Related tables help

[Guest]

Hi,

i am slowly working my way through Access. i seem to be ok at relating
certain tables to others to retrive or link certain information like my
suppliers and their products, my clients and their specific projects. i
cannot think how to start my next phase though.

i have a table named enquiry table where i insert new enquiries. this gives
me a number which follows the enquiry through to the end for example " 4556"
( these numbers are sequential so another client will have 4557 and so on )

i need to create a table to to quote with. the quote needs to be broken into
about 10 mini quotes to create the 1 large quote as there are multiple parts
to one quote as i manufacture units not supply components. each mini quote is
named A,B,C,D E,F,G,H,I,J.

i need the quote table to be able to link with the mini quote tables so when
i am viewing this on a form, the one enquiry number will show all mini quotes
related to the enquiry number. the quote is built up on products from my
suppliers using the Productname field.

i hope this is understandable and i apologise if it is a little vague but i
dont have any table info to provide as i can't seem to think what i need to
do.

i am guessing that all tables need to have the enquiryID to relate to the
enquiry number and there must be a relation to the mini quote area to create
what i need. hopefully, someone could help me.

Kind Regards and thanks in advance,

Nigel

 

[Guest]

You need two tables in a one-to-many relationship: Quotes and Miniquotes. The
primary key of the Quotes table would be your Enquiry Number - in fact the
Quotes table could be the Enquiry table itself, or (if most enquiries never
lead to a quote) a separate table in a one to one relationship.

The Miniquote table would have the Enquiry number as a foreign key, and a
separate field (I'd use an integer rather than a letter so you don't have the
On Beyond Zebra problem someday!) for the sequential miniquote identifier;
these two fields could be a joint two-field Primary Key.

 

[Guest]

Hi,

thanks for replying to my query. if you are saying that i need only 2
tables, will the tables hold all of the information needed to build the main
quote out of the mini quotes? to view the main quote, the mini quotes will
need to show the cost structure as in materials, time estimated per mini
quote. can 2 tables do this?

do you have an example to show me where i can try it out?


thanks in advance,


Nigel

 

[Guest]

Oh,

i forgot to metion that i am ahppy to sue alphabetic codes instead of
numeric as i have never had a project with more than 10 items in. also the
codes are transfered through drawing stages and onto my shop floor for time
logging per code ( item ). numbers would confuse everyone!!!

i look forward to your response.


regs,


Nigel