Dale
Super User
Joined: 21 Feb 2005
Posts: 1440
Location: Australia
|
 Posted: Wed Nov 23, 2005 7:00 pm Post subject: Re: Database and query structure |
 |
|
Hi Dropout,
First off let me say I know nothing about Base. My responses here are what I woud do in general and I am hoping that they will be eonough to get you moving. | Dropout wrote: | I am trying to set up a data base for waste collection. We have customers that we pick up from every week or two weeks and some that call when we need to.
I have a table that includes with all the customer information.
What I need to add is:
The ability to flag a customer for pickup so I can print a pickup report when needed. Should this be a separate table or add to the customer table? |
I would add another two or three fields to your customer table:SCHED_PICKUP (boolean), PICKUP_INTERVAL (probably integer). If all your scheduled pickups are weekly PICKUP_INTERVAL is redundant and unnecessary. You also want a LAST_PICKUP (date) somewhere. (Either in the customer table or a history table - depending on how detailed you want to be)
| Quote: | | How can I get the customers who need weekly pickups to get flagged automatically? |
This would be a calculated field/control (whatever it's called in BASE) on a form or report. You would also calculate it in a query to produce a printed report of who needs pickups today.
| Quote: | | What can I do to clear those flags when the pickups are done? Is there a special type of query that will do this? |
Probably the most sensible way is to update the LAST_PICKUP field manually (on a customer history form). It would be part of the data entry task for the day's run.
| Quote: | | If I want to keep historical information does that change how I set this up? |
Yes, it does change things a bit. I would use a customer history table. It links to the customer table via the CUST_ID field. It would have a date field (PICKUP_DATE field maybe), a notes field, a service field, whatever else... If all you are doing is recording pickups, then you only need the one date field, and no service field. If you are recording other items as well (phone calls, complaints whatever) you need to be able to differentiate between these items.
You get the date of your last pickup using a query to look for max date when service is pickup.
| Quote: | | Are there any good online info sources for Base? |
Check out the links in the How to get started thread. Also search this forum for other references.
As I said, this is all "in principle" stuff and does not address BASE specifically. I hope it helps.
_________________
Dale
To err is human, but to destroy your slippers in the process takes a real son of a bitch: Me!
OOo documentation from the source
http://documentation.openoffice.org
Guides, FAQ, How Tos |
|
