Multiple variables

[Kojak43]

I work at an Advertising Agency in the print production dept. Production get prices and prints brochures, postcards, catalogues yahda, yahda, yahda.

A job is opened describing the task. The job number is unique. The specifications for the job is used to provide a price.

Example job 00025 is opened for post cards. The client wants to learn the cost difference if it is 17" x 6" with one fold to 8 1/2" x 6" or a no fold card that measures 11" x 6". They want a five quantity break of 100,000; 250,000; 500,000; 750,000 and 1,000,000 units. I need to send out a bid sheet to three printing vendors.

I figured out out to create a query and a report that shows the two sizes and the 5 quantity breaks.

Where I am stumbling is making a query that can capture 30 prices. (3 vendors x 5 quantities x 2 sizes.)

I made tables for all variables. Each variable has at least 5 containers. i.e. QuantityA, QuantityB etc, + SizeA, SizeB, ect + PaperstockA, PaperStockB ect. There can be lots of variables.

When I join (oh, and everything joins with the job number) tblprices, tblquantities, tbljobs and tblvendors in a query it does not yield any information other than the column headings. It is as if there is no data in any of the fields. Actually, there is no data in the price fields, but that should not preclude showing data in fields that it does exist does it?

So, how can I input 30 prices?

 

[danvlas]

I can't see how everything joins by job number...
Can you post the table structures and explain the relationships?
So far looks like bad design...but I may be wrong...




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[Kojak43]

Oh, geeze, sorry, but you are dealing with a rank amateur.
I doubt that you are wrong. Bad design usually runs in all my databases.
Sorry, but I am not certain what you mean by table structures, or for that matter how to post them.

 

[danvlas]

Table structure means, in this particular case, list of fields and primary key. Also indicate the foreign key (the field by which the table is related to another one of a 'higher' level.)

Example:
Table tblCustomer
CustomerID (Numeric, Primary Key)
CustomerName (Text)


Table tblOrders
OrderID (Numeric, Primary Key)
Customer (numeric, Foreign Key to CustomerID in tblCustomers)

and so on





Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[Kojak43]

OK, thanks.
tblJob
JobNum (text,primary)
Description (text)
QuantityID (numeric, Foreign to QuantityID in tblQuantity)
SizeID (numeric, Foreign to SizeID in tblSize)
PagesPanelsID (numeric, Foreign to PagesPanelID in tblPagesPanel)
FoldsID (numeric, Foreign to FoldsID in tblFolds)
NumOfColorsID (numeric, Foreign to NumOfColorsID in tblNumOfColors)
PaperStockID (numeric, Foreign to PaperStockID in tblPaperStock)
BindryID (numeric, Foreign to BindryID in tblBindry)
PriceID (numeric, Foreign to PriceID in tblPrice)
BidVendorID (numeric, Foreign to BidVendorID in tblBidVendor)

tblQuantity
QuantityID (AutoNumber, Primary)
QuantityA (Numeric)
QuantityB (Numeric)
Quantity C-D-E (Numeric)(These are line listed just like A&B)
JobNum (Numeric, Foreign key to tblJob)

tblSize
SizeID(AutoNumber, Primary)
SizeA (Text)
SizeB (Text)
SizeC-D-E (Text) (These are line listed just like A&B)
JobNum (Numeric, Foreign key to tblJob)

tblPagesPanels
PagesPanelsID(AutoNumber, Primary)
PagesPanelsA (Text)
PagesPanelsB (Text)
PagesPanelsC-D-E (Text) (These are line listed just like A&B)
JobNum (Numeric, Foreign key to tblJob)

tblFolds
FoldsID(AutoNumber, Primary)
FoldsA (Text)
FoldsB (Text)
FoldsC-D-E (Text) (These are line listed just like A&B)
JobNum (Numeric, Foreign key to tblJob)

tblNumOfColors
NumOfColorsID(AutoNumber, Primary)
NumOfColorsA (Text)
NumOfColorsB (Text)
NumOfColorsC-D-E (Text) (These are line listed just like A&B)
JobNum (Numeric, Foreign key to tblJob)

tblPaperStock
PaperStockID(AutoNumber, Primary)
PaperStockA (Text)
PaperStockB (Text)
PaperStockC-D-E (Text) (These are line listed just like A&B)
JobNum (Numeric, Foreign key to tblJob)

tblPrice
PriceID(AutoNumber, Primary)
PriceA (Numeric)
PriceB (Numeric)
PriceC-D-E (Numeric) (These are line listed just like A&B)
JobNum (Numeric, Foreign key to tblJob)

tblBidVendor
BidVendorID(AutoNumber, Primary)
BidVendorA (Text)
BidVendorB (Text)
BidVendorC-D-E (Text)(These are line listed just like A&B)
JobNum (Numeric, Foreign key to tblJob)

tblBindry
BindryID(AutoNumber, Primary)
BindryA (Text)
BindryB (Text)
BindryC-D-E (Text)(These are line listed just like A&B)
JobNum (Numeric, Foreign key to tblJob)

The relationship is One Job to Many Paper,Prices,Vendors Etc
All of the JobNum's in each of the other tables is a lookup to the tblJobs

BidVendor uses a lookup to a tblVendor not outlined here. I can lookup the name of a vendor for BidVendorA-B-C-D-E from the Vendor table.

It is not pretty. But I don't know how else to get all the variables in.