[database] Help me sort this out in my brain!
 

Ok, i need to make an access (ewww) database for my dad who is going to start being a taxi driver. He was trying to do something in excel, but i think it needs access really, and my access is weak.

Basically for each day there can be any number of entries, which are of type Fares (money made, tip, mileage), Empty Miles (mileage), or Fuel (litres, cost)

Now it seems fairly easy for me to link those to a day (have a unique id for each day, and they all have it too),

but each day has to link to a month (and not all months are the same or even fixed lengths, and have to be unique to the year). Can i do that the same way or is there something easier i'm missing ?

Again, those months need to link to a year, so that funky things like viewing last 6 months totals, etc can be done.

Is there a really easy way of doing all this lot, and i just can't think of it ?

edit : this is my proposed table structure so far.

Table dayitems
--------------
DayID (link to the day it belongs to)
Fuel Cost
Fuel Litres
Empty Miles
Fare Miles
Fare
Tip

Table days
----------
DayID (used to link items to each day)
MonthID (so we know which month we belong to)
Daynum (Purely for user display)
Monthnum (Purely for user display)
Yearnum (Purely for user display)

Table months
------------
YearID (to which year we belong)
MonthID (so days can link to us)
Monthnum (Purely for user display)
Yearnum (Purely for user display)

Table years
-----------
YearID (so months link to us)
Yearnum (Purely for user display)

Suggestions/comments appreciated :)

Re: [database] Help me sort this out in my brain!
 

Use a date instead of DayID:

Table dayitems
--------------
ItemID (Primary key)
fldDate
FuelCost
FuelLitres
EmptyMiles
FareMiles
Fare
Tip

You have a month and year in one field.
You can now makes queries to show all item sof a day/month/year or sum the Fuel Cost, FuelLitres, etc. for a day/month/year

Oh, and using spaces in column names is bad

Re: [database] Help me sort this out in my brain!
 

store the date in a long integer like yyyymmdd (ie: 20050414) and do some wickid SQL to get a particular month like ((ldate - (ldate MOD 100) MOD 10000) / 100) AS myMonth WHERE myMonth = 11.
This is how the database at my work works BTW. it's hell. :p

BTW, is days.monthnum different from month.monthnum ?
And why can't you just have day/month/year fields in the dayitems table and make sure those three make an unique combo.
I don't see why you need to store days/months/years in separate tables.

Re: [database] Help me sort this out in my brain!
 

You can also use functions like Day(fldDate), Month(fldDate), Year(fldDate)

Re: [database] Help me sort this out in my brain!
 

I'm not much of an access buff but couldn't you put it ALL in one table to save all the cross-linking?

Each entry could have a year,month and day id along with all the other info..

Re: [database] Help me sort this out in my brain!
 

Why on *earth* would you want to use 3 useless tables, instead of ... (err) ... a date field to store a date?

Re: [database] Help me sort this out in my brain!
 

because i think in terms of SQL not access ? :/

also i know very little about access and date functions :\

thanks for the help everyone :)

Re: [database] Help me sort this out in my brain!
 

Is that really bad? Would you mind pointing out to me why quickly?
Because there are other, string based date-formats? Or because of binary timestamps that are better to use?

Re: [database] Help me sort this out in my brain!
 

Partly, I imagine, because the SQL date/time standard is "yyyy-mm-dd hh:mm:ss". It's also a bitch to do arithmetic with, for that you want a Unix timestamp.

Re: [database] Help me sort this out in my brain!
 

Because I need to do arithmatics with the date and time (ie: calculate the difference in time between the records) which is a terrible programming problem (instead of an SQL problem) with conversion and so. There are about 20 functions that convert time and date to a timestamp, long yyyymmdd/hhmmss, or seconds display.

Re: [database] Help me sort this out in my brain!
 

Can I ask why you think your dad needs to use access instead of excel? Does he need to produce reports or manipluate the data in anyway or is it simply a case of book keeping?

If it's the latter then I think access is not the answer (it seems like a lot of work to me).

Surely excel will do the job by having a seperate workbook for each month and a new spreadsheet for each new year. Each days details would be entered as a new row and the data you require in columns.