I can do this with pencil and paper, but not Access 2000

[kojak43]

I am trying to create a db for job bidding specifications. These are printed paper specifications.
There is a Job Table listing the Job#, Description, size, number of colors to be printed and the final bindry requirements.
There is a Quantity Table listing 5 different quantities. There is a StockTable listing 3 different types of papers that could be used.
There is a Price Table and there is a Vendor table. This is were it gets hairy. If I have three vendors, bidding 3 different quantity levels on two different paper stocks I will have a total of 18 prices (Each vendor furnishes 6 prices)

What I am trying to achieve is to type specifications once. That will yield a report that gets sent to the three vendors.

When the vendors are finished with their quoting, they return the prices to me and I enter them into the database and create a report showing all vendors, and the prices by all quantity levels and all paper stock levels. The final report uses the initial specifications as well as the newly entered price levels by vendor.

I can get all of this written out ok until I get to the point of trying to enter prices. The price table is too complex for me to work out how to create it. Some jobs have fewer line items, some have more line items, so I have to create a table that will allow me to enter just what I need (in this example 3 vendors + 2 paper stocks + 3 quantity levels.

Any suggestions?

 

[thewesties]

Kojak,
here is what I would recommend. I will list the table(field-type,field-type)

tblQuantity (QtyID-Autonumber, Qty-Integer)
tblStock (StockID-Autonumber, StockType-String)
tblVendor (VendorID-Autonmuber, VendorName-String)
tblPricing (PriceID-Autonumber, QtyID-Long Integer,StockID-Long Integer, VendorID-Long Integer)

The autonumber fields will naturally be the Primary keys.
Create relationships between all the tables linking the PKs to the tblPricing

Then create a query (frm_qry_Pricing) that is created based on the above

Create a form based on that query.

This will allow you to manipulate the data for each vendor and unique situation.

Once these are completed, you should be able to generate reports from a query that grabs the infor from the separate tables.

Hopefully, this helps. Something like this is easier done than explained. I hope this helps.

 

[kojak43]

Thanks, I'll try this tonight.
I have such a wonderful life. I get to stay home on date night and make a db!
k

 

[thewesties]

Shouldn't be too bad. As long as you don't start to look longingly at the Microsoft Help Assistant!!!

 

[kojak43]

thewesties:
I did as instructed.
Thanks for the guidance.
In the tblQuantity I added 5 additional fields:QuanA, QuanB etc.
In the tblStock I added 5 additional fields:StockA, StockB etc
In the tblPrices, I added 25 combination fields. PriceStockAQuanA, PriceStockAQuanB all the way to PriceStockEQuanE

When I made the form, I can add all the job information in the main form and the Stock and Quantity is a subform.

When I try to enter stock I get a "Can not update field" with a yellow yield sign with a Bang (!). When I say OK, it allows me to enter the stock information.

When I try to make a report from the form, nothing appears.

I may have been luckier at last call in the pub, than staying home trying to make this work.

Any guidance?
k

 

[kojak43]

I can not seem to get this to work.

 

[bat17]

I think that you are thinking flat file rather than relational!

tblPricing (PriceID-Autonumber, QtyID-Long Integer,StockID-Long Integer, VendorID-Long Integer)

You don't need to add all of the possible combinations to the table, though I think it probably need the Job number adding to it.

What you will end up with in this table is a separate row for each quote. So in your example this table will have 18 rows.

By using the other tables as look up tables you can add as many varieties of paper/quantities as you want with out having to rewrite everything to make it fit!

You can use this data to build your form. You will probably want to use a subform to display the data so that you can filter it to each vendor and job number.

When you build your reports you will be able to use the fields for grouping to help lay out the report.

For your own pricing you will probably need to create a similar set up or you may be able to just add yourself as a Vendor and put your pricing in there.

HTH

Peter