Form and Table issue - help needed.

[Mobbbx]

I am trying to create a price database. One vendor can offer many items at
different prices over time. i need access to capture all the various vendors,
the items they sell and at their respective prices. So far, i have 3 tables.

Vendor Table
VendorID
Name
Address

Item Table
Item#
Description

Price Table
PriceID
Item#
VendorID
Price
Date Entered

i want to create a continuous subform showing all the past prices and linked
to the main form by vendor and item. So the end-user can select the vendor
and item, then go to the subform to enter new prices, at the same time, look
at historical prices.

Does the current table design allow me to do that? If so how? Can i create a
subform based on the above price table and create the main form based also on
the same price table? or do i need to create another table? i tried to create
another table and edit the Price table:

Price_Details Table
PriceID (Key)
Line_ID (Key)
Price
Date Entered

Price Table
Price ID (Key)
Item#
VendorID

But with my new table structure, it allows the same vendor with the same
item# to appear more than once in the price table. There should be only 1
record for 1 vendor for the same item in the price table, while the
price_details table referencing the price table allows users to enter prices
over time.

Any help will be greatly appreciated. Many thanks.

 

[Golfinray]

What you are going to need to do is establish relationships between your
tables so you can query the data and then use forms. You could relate vendor
id to vendor id in the vendor and price tables. Then item # and item # in the
item and price tables.
Got to tools/relationships and establish those relationships. I think I
would make vendor ID and Item ID my primary and foreign keys. Pull all three
tables up in a query and relate them the same way. See if that gives you one
vendor per one price. If that works I would create a mainform where you can
select an item and vendor then a subform for price.