Design Problem

[Rob Bergstrom]

Hello,
I have a user table that contains stuff like first name, last name, job
title, copy codes, etc.
The company has 5 copiers that employees use their codes for printing,
faxing, etc.
I need to add a table(s) to the database so that every month I can enter
individual copier usage according to users copy code.
I am confused on how to do this.

I made a "copies" table and put a lookup field to the copy code in the users
table, then added a field for each copier.
That works for a single record for each copier but I need to track each
month and compile this for history also.
Do I need a join field or something so I can do a separate record each month
for each copier?

Thanks in advance,

Rob

 

[George Nicholson]

Users:
UserID
Fname
Lname
JobTitle
CopyCode

Copiers:
CopierID

CopierUsage:
UsageDate (or some other period designation like YearMonth)
CopierID
CopyCode
Usage (# of Copies)

Each period/month new records are added to the CopierUsage table.
Queries/Reports are then run from it (with joins to get User Names, etc., as
necessary) for a specific UsagePeriod or range of periods (i.e., year to
date).

 

[Rob Bergstrom]

Not quite getting it.
with the copierusage table, I also need a primary key right?
So when I do that, I guess I don't understand the whole joins thing because
I can't get it to work.

Rob

 

[George Nicholson]

CopierUsage can have a PrimaryKey, but it won't come into play in any joins.

Copiers joins to CopierUsage (one-to-many) on CopierID
Users joins to CopierUsage (one-to-many) on CopyCode. This does assume that
CopyCode is unique & no duplicates within Users. If it isn't then we have a
slightly different ballgame.

 

[Rob Bergstrom]

Thanks I got it working now and giving me accurate data.
Thanks very much,

Rob