Need Help with designing a Quotation Database

Hi all,

I hope im not being too lazy here but i have been asked to develop a new quotation system with ms access (eventually being implemented on mysql but thats a different story) i have the following information already inserted into two table

Quotations table
Quote number (key and auto number)
who created the quote
who the quote is for
the address the quote is going to (possibly 3 lines)
the data the quote is created
the date the quote is meant to be completed by
the type of quote (technical, documentational, special or other)

now i can do this just fine, the problem i come across is when i am linking to the ITEMS table,

in the items table i have the following
Item Number (part number)
Item Description
quantity
Item unit cost
and Total cost

i want to have a field that will take the number from quotations.quote number field

i have created a one to many relationship from quotations to items, I have already created a form with a sub form and it doesnt fill out well and when i create a field to take a number from the quotation.quote number field it just ends up taking info from the other fields and not the one i want.

any help is appreciated, also if you would like me to clarify please say so.

thanks

MattyG

It sounds as though you need a QuoteItem table. This will contain the Quote Number and Item Number. You will need to move the Quantity, Unit Cost and Total Cost fields to this table.

The sub form would then be based on this table and linked by the Quote Number. You can use a lookupfield to get the Item Number and Unit Cost from the Item table.