How to start setting this DB up... (Dates)

[gangel]

Hi pplz,
Ive made about 3 databases for different things. Still developing them and learning more and more as i go.
My newest project that im working on is regarding people picking up weekly items from my shop.

I have 100 customers and every MONDAY they pick an item up from my shop.

At the moment i have an excel spreadsheet with customers down left column and up top row i have 11/5, 18/5, 25/5 (eg the next few mondays in May (Aussie dating, not USA :P)


Now i print this off and manually tick the boxes,

So if John Smith picks up on 11/5, i tick the box in that column under his name and correct date. (hopefully you understand the layout now).

Im going to change this to an Access DB, so i put the fields of their names in... But do i have another table with Dates as fields and a tick box for each date...
This is a different kind of DB than i have made before and im totally confusing myself with it.

I also want the db to just autofill all the mondays of the year along the top, instead of writing them in manually.

Hopefully im making myself clear,

Please give any advice or ask questions, i really want to learn more about this but i dont know the questions/terminology to even google to teach myself!

Gangel

[ItsMe]

I would avoid the "Date" field, as you describe it. I would look for a way to store a "TimeStamp". What you are describing is an activity. Someone comes into your shop and picks something up. This activity may warrant its own table. You can log events in this table by adding a Customer ID PK and the associated Purchase Order PK, along with a timestamp.

[gangel]

so ive looked more into it and what is confusing me is... im used to one to one or one to many relationships BUT in this:
- ONE customer has many pickup dates...
- ONE pickup date can also have many customers...

Or am i totally looking at this the wrong way!?

[ItsMe]

...ONE pickup date can also have many customers...

Don't worry so much about this. Take a look at post #2. If you recognize something as an activity, determine where to store/log this activity. Often times, it will require a new table.

[gangel]

k so i do get that a customer "john" can come in and pickup the item, and i can select john and say "pickedup" button that will timestamp his ID on a table to Date()

However what i need is to phyically recreate the same idea of dates (Along the top row) and names down the left, with tick boxes for that specific date.

Is either of the above sentences me being on the right path?

[gangel]

This is the general idea of what im getting at

[gangel]

this is a basic recreation of my excel doc,

[gangel]

k how about this idea...

Customer table (just customer names + PK)
Date table (All the dates of all mondays of the year) + PK

"Pickup" table: which has CustomerID, DateID AND a tickbox for if it has been picked up....

Now the trick to create the top table would be that for each Customer, there must be a date ID allocated to them ....

This makes more sense but maybe im totally doing this incorreclty and not understanding your directions

[ItsMe]

this is a basic recreation of my excel doc,

I understand. Hence, my suggestion. What you are describing is a common issue when migrating from Excel to an RDBMS. I would have a table for customers, Sales Orders or Invoices, and a table to log events where customers pick up their order. You may not need a separate table. It may be possible to manage with extra fields in your Sales Order table. I would opt for a Sales Order table. In your Sales Order table you can store the Customer ID and their PO number, along with a Timestamp when the request was generated.

Afterwards, if you so desire, you can create a crosstab query to simulate your spreadsheet. I suspect there will not be a need to display your data in that format. It is likely you will find another, suitable, option.