normalization and relationships - don't know how to place data

[Nevy]

Hi, I want to create an access app that will allow a user to see the number of pages printed by various printers.

These are the data that I have:

• Model Type (various are available)
• Printer's name (unique to one printer)
• IP addr (unique to printer)
• Quantity printed (such as Single Legal, Double Legal, Single Letter,etc)

This is what I have (2 tabels with 1-many relationship):
Printers
PrinterID
Model
Name
IP

ModelTypes
ModelID
Model

Now, where do I place the quanties of paper printed? Do I just leave it in Printers?

Thanks.

 

[Pat Hartman]

The tables should be:
Printers
PrinterID
ModelID
Name
IP

ModelTypes
ModelID
Model

ModelID is the linking field, NOT Model. The Quantity field should go with the printer since that is the lowest level of detail.

 

[Nevy]

I thought quantities would go else where since it has many types of paper been printed.

But since it's simpler, I'll stick with that.

Thanks.

 

[DaveJHPT]

You need another table tblPaperSize, with
PaperSizeID
Name
Length
Width
...

and a further table tblPagesPrinted, with
PaperSizeID
PrinterID
Quantity
...

Dave

 

[Nevy]

But all printers print all the types of paper. So on the main table (Printers), I would end up with 4 references (SingleLegal, SingleLetter, DoubleLegal, DoubleLetter) for each printers.

And if I get this right, that's a many-to-many reference. Isn't it ?

 

[RV]

And if I get this right, that's a many-to-many reference. Isn't it ?

That's correct, you would need a junction table.

RV

 

[Pat Hartman]

How's this?

tblPrinters
PrinterID (primary key)
ModelID (foreign key to tblModel)
PrinterName
IP

tblPaperSize
PaperSizeID (primary key)
PaperSizeDesc

tblPrintTransaction
PrintTransactionID (primary key)
PrintDate
PrinterID (foreign key to tblPrinters)
PaperSizeID (foreign key to tblPaperSize)
PrintedBy (foreign key to tblUser?)
Quantity

tblModelTypes
ModelID (primary key)
Model

 

[Nevy]

So what would be the relationship between Printers and PrintTransactions?

Is it many-to-one? "Printers have unique transactions"

 

[Pat Hartman]

1-many. Each transaction is for only a single printer.
printerA 25 pages legal
printerA 10 pages letter
printerB 98 pages letter
printerB 20 pages letter
printerA 14 pages legal

 

[Nevy]

cool, thanks a lot.