User Name
Password

Go Back   Planetarion Forums > Non Planetarion Discussions > Programming and Discussion

Reply
Thread Tools Display Modes
Unread 14 Apr 2005, 15:03   #1
NEWSBOT3
NEWSBOT
 
Join Date: Dec 2000
Location: The enby cave!
Posts: 4,872
NEWSBOT3 needs a job and a girlfriendNEWSBOT3 needs a job and a girlfriendNEWSBOT3 needs a job and a girlfriendNEWSBOT3 needs a job and a girlfriendNEWSBOT3 needs a job and a girlfriendNEWSBOT3 needs a job and a girlfriendNEWSBOT3 needs a job and a girlfriendNEWSBOT3 needs a job and a girlfriendNEWSBOT3 needs a job and a girlfriendNEWSBOT3 needs a job and a girlfriendNEWSBOT3 needs a job and a girlfriend
[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
__________________
[20:27:47] <nodrog-aawy> **** i think my housemate just caught me masturbating
[11:25:32] <idimmu> you are a little piggy arent you
[13:17:00] <KaneED> i'm so closet i'm like narnia
__________________
Pretty parks and funky scrap metal things here
NEWSBOT3 is offline   Reply With Quote
Unread 14 Apr 2005, 17:39   #2
Caesar2
Commander
 
Caesar2's Avatar
 
Join Date: Sep 2001
Location: Netherlands
Posts: 146
Caesar2 is just really niceCaesar2 is just really niceCaesar2 is just really niceCaesar2 is just really nice
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
__________________
Quote:
Originally posted by Cochese
Cathaar are not overpowered.

You were just "bashed", live with it.
Caesar2 is offline   Reply With Quote
Unread 14 Apr 2005, 18:20   #3
Structural Integrity
Rawr rawr
 
Structural Integrity's Avatar
 
Join Date: Dec 2000
Location: Upside down
Posts: 5,301
Structural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriend
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.

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.
__________________
"Yay"
Structural Integrity is offline   Reply With Quote
Unread 14 Apr 2005, 20:08   #4
Caesar2
Commander
 
Caesar2's Avatar
 
Join Date: Sep 2001
Location: Netherlands
Posts: 146
Caesar2 is just really niceCaesar2 is just really niceCaesar2 is just really niceCaesar2 is just really nice
Re: [database] Help me sort this out in my brain!

You can also use functions like Day(fldDate), Month(fldDate), Year(fldDate)
__________________
Quote:
Originally posted by Cochese
Cathaar are not overpowered.

You were just "bashed", live with it.
Caesar2 is offline   Reply With Quote
Unread 15 Apr 2005, 03:15   #5
ph8
Banned
 
ph8's Avatar
 
Join Date: Jun 2004
Posts: 91
ph8 is on a distinguished road
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..
ph8 is offline   Reply With Quote
Unread 15 Apr 2005, 05:28   #6
Raging.Retard
Street Tramp
 
Raging.Retard's Avatar
 
Join Date: Apr 2000
Location: Street Gutter
Posts: 341
Raging.Retard has a brilliant futureRaging.Retard has a brilliant futureRaging.Retard has a brilliant futureRaging.Retard has a brilliant futureRaging.Retard has a brilliant futureRaging.Retard has a brilliant futureRaging.Retard has a brilliant futureRaging.Retard has a brilliant futureRaging.Retard has a brilliant futureRaging.Retard has a brilliant futureRaging.Retard has a brilliant future
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?
__________________
Chimney Pots.
Raging.Retard is offline   Reply With Quote
Unread 15 Apr 2005, 05:38   #7
NEWSBOT3
NEWSBOT
 
Join Date: Dec 2000
Location: The enby cave!
Posts: 4,872
NEWSBOT3 needs a job and a girlfriendNEWSBOT3 needs a job and a girlfriendNEWSBOT3 needs a job and a girlfriendNEWSBOT3 needs a job and a girlfriendNEWSBOT3 needs a job and a girlfriendNEWSBOT3 needs a job and a girlfriendNEWSBOT3 needs a job and a girlfriendNEWSBOT3 needs a job and a girlfriendNEWSBOT3 needs a job and a girlfriendNEWSBOT3 needs a job and a girlfriendNEWSBOT3 needs a job and a girlfriend
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
__________________
[20:27:47] <nodrog-aawy> **** i think my housemate just caught me masturbating
[11:25:32] <idimmu> you are a little piggy arent you
[13:17:00] <KaneED> i'm so closet i'm like narnia
__________________
Pretty parks and funky scrap metal things here
NEWSBOT3 is offline   Reply With Quote
Unread 15 Apr 2005, 12:49   #8
JetLinus
Friendly geek of GD :-/
 
JetLinus's Avatar
 
Join Date: Nov 2000
Location: On my metal roid
Posts: 926
JetLinus has much to be proud ofJetLinus has much to be proud ofJetLinus has much to be proud ofJetLinus has much to be proud ofJetLinus has much to be proud ofJetLinus has much to be proud ofJetLinus has much to be proud ofJetLinus has much to be proud ofJetLinus has much to be proud ofJetLinus has much to be proud of
Arrow Re: [database] Help me sort this out in my brain!

Quote:
Originally Posted by Structural Integrity
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.
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?
__________________
[] Entropy increases! :-/
JetLinus is offline   Reply With Quote
Unread 15 Apr 2005, 17:03   #9
Gayle29uk
Bitch
 
Join Date: Jun 2002
Location: North Yorkshire
Posts: 3,848
Gayle29uk is just really niceGayle29uk is just really niceGayle29uk is just really niceGayle29uk is just really nice
Re: [database] Help me sort this out in my brain!

Quote:
Originally Posted by JetLinus
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?
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.
__________________
ACHTUNG!!!
Das machine is nicht fur gefingerpoken und mittengrabben. Ist easy
schnappen der springenwerk, blowenfusen und corkenpoppen mit
spitzensparken. Ist nicht fur gewerken by das dummkopfen. Das
rubbernecken sightseeren keepen hands in das pockets. Relaxen und vatch
das blinkenlights!!!
Gayle29uk is offline   Reply With Quote
Unread 17 Apr 2005, 22:12   #10
Structural Integrity
Rawr rawr
 
Structural Integrity's Avatar
 
Join Date: Dec 2000
Location: Upside down
Posts: 5,301
Structural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriend
Re: [database] Help me sort this out in my brain!

Quote:
Originally Posted by JetLinus
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?
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.
__________________
"Yay"
Structural Integrity is offline   Reply With Quote
Unread 18 Apr 2005, 00:10   #11
CrashTester
I am an idiot
 
CrashTester's Avatar
 
Join Date: Feb 2003
Posts: 2,137
CrashTester has a brilliant futureCrashTester has a brilliant futureCrashTester has a brilliant futureCrashTester has a brilliant futureCrashTester has a brilliant futureCrashTester has a brilliant futureCrashTester has a brilliant futureCrashTester has a brilliant futureCrashTester has a brilliant futureCrashTester has a brilliant futureCrashTester has a brilliant future
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.
CrashTester is offline   Reply With Quote
Reply


Thread Tools
Display Modes

Forum Jump


All times are GMT +1. The time now is 09:07.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2002 - 2018