Need help on MS access

Question

Hello ,

I have water usage daily csv file & want to put it into Access for me to review monthly or yearly

I want to create a Microsoft Access DB to record the daily water usage in order can review the usage

Is anyone can show me steps how to create this DB on ACCESS?

sample file https://1drv.ms/u/s!AlCHhVjJ-adjgg1qbISC1zQgOngb?e=EUMkRV

The CSV file format below. filename with date & the water usage in hourly base

Water Use For Aug 01, 2021.csv
Hour of Day,Water Use (m³)
0 a.m.,0.360
1 a.m.,0.160
2 a.m.,0.170
3 a.m.,0.170
4 a.m.,0.170
5 a.m.,0.150
6 a.m.,0.160
7 a.m.,0.170
8 a.m.,0.170
9 a.m.,0.160
10 a.m.,0.170
11 a.m.,0.170
12 a.m.,0.150
1 p.m.,0.160
2 p.m.,0.160
3 p.m.,0.150
4 p.m.,0.180
5 p.m.,0.120
6 p.m.,0.140
7 p.m.,0.130
8 p.m.,0.120
9 p.m.,0.120
10 p.m.,0.120
11 p.m.,0.140

Many Thanks & help someone know how to create the Access DB

Answer 1

Hi. Perhaps it would be easier to show you if you could share a sample CSV file for testing.

Answer 2

Okay, I think this will need a few steps, which might be hard to demonstrate here. Essentially, to really make your database useful, we would need a table with a date/time column. That would allow you to query the data more intelligently.

Since your CSV files don't have that information, you would have to pre-stage each file and then add the date/time information before storing the data into a table.

So, I would suggest creating a table with a proper time column to match the ones from your CSV files. Then, import the files, one at a time, into a temporary table. Then, insert each record from the temporary table into the final table while adding the date/time info. You should end up with something like this:

08/01/2021 00:00 AM, 0.360
08/01/2021 01:00 AM, 0.160
...
08/01/2021 10:00 PM, 0.120
08/01/2021 11:00 PM, 0.140
08/02/2021 00:00 AM, 0.150
and so on...

Hope that helps...

Answer 3

If you import the data from your text file into a column of date/time data type the date element would default to 30 December 1899, which is 'day zero' in Access's implementation of the date/time data type. As you are importing the data for exactly one day at a time, then you could set the database up so that the relevant date is first entered into a text box in an unbound dialogue form, and after each import session execute an update query to add the date to each value where the date element is 30 December 1899, e.g.

PARAMETERS Forms!frmDateDlg!txtDate DATETIME;
UPDATE WaterLog
SET HourOfDay = HourOfDay + Forms!frmDateDlg!txtDate
WHERE DATEVALUE(HourOfDay) = #1899-12-30#;