Confused n stuck

  • Thread starter Thread starter Ling
  • Start date Start date
L

Ling

I try to explain as simply as I could.

All my tables have ID and Yr set as their primary keys. I
have set the numbering to start from 1 each yr and would
then add by 1 each new record is added to the table.

E.g: I have a quotation form n 1 of the combo box shows a
list of enquiry nos(from the tblCosting).The combo box
shows all the enquiry nos stored in the tblCosting.

Now, I discovered a problem. What if a person creates a
quotation no of say 20,and maybe choose an enquiry no of 1
but of yr 2004? I only stored the selected enquiry no
inside my quotation table.
Thus, when I run a query,the Quotation no 20 would show
two returned records.1 of Yr 2003 and 1 of Yr 2004.

I was thinking of adding the chosen enquiry no as well as
its corresponding yr in the quotation table.
Currently, the combo box in the quotation form shows the
enquiry no,its yr etc.N I only bound the enquiry no to the
quotation table.

Is there a way to bound 2 columns in a combo box.Sounds
impossible.

Any ideas?
I am really stuck in this situation. Thanx for any help.

Ling
 
I try to explain as simply as I could.

All my tables have ID and Yr set as their primary keys. I
have set the numbering to start from 1 each yr and would
then add by 1 each new record is added to the table.
Click to expand...

This is a bit unsettling right there. Every table should have a
Primary Key appropriate for that table; if two tables have the same
primary key, they are ipso facto in a one-to-one relationships - which
is quite uncommon. Or are these different ID's - a customer ID,
distinct from a quotation ID and so on? How are the tables related to
one another?
E.g: I have a quotation form n 1 of the combo box shows a
list of enquiry nos(from the tblCosting).The combo box
shows all the enquiry nos stored in the tblCosting.

Now, I discovered a problem. What if a person creates a
quotation no of say 20,and maybe choose an enquiry no of 1
but of yr 2004? I only stored the selected enquiry no
inside my quotation table.
Click to expand...

Well, you need to store a Foreign Key which contains as many fields,
of the same types, as the Primary Key of the table to which you are
relating.
Thus, when I run a query,the Quotation no 20 would show
two returned records.1 of Yr 2003 and 1 of Yr 2004.
Click to expand...

Obviously. You don't have a valid foreign key.
I was thinking of adding the chosen enquiry no as well as
its corresponding yr in the quotation table.
Currently, the combo box in the quotation form shows the
enquiry no,its yr etc.N I only bound the enquiry no to the
quotation table.

Is there a way to bound 2 columns in a combo box.Sounds
impossible.
Click to expand...

No, but you can use the Combo Box's AfterUpdate event to "push" the
year:

Me!txtYear = Me!comboboxname.Column(n)

where (n) is the zero based subscript of the desired column in the
combo's row source.
 
Hi..I give an example of 2 of my tables...

tblCosting tblQuotation
---------- --------------
EnquiryNo QuotationNumber
EnquiryYear QuotationYear
Item EnquiryNo
Qty etc
etc

tblCosting: EnquiryNo, EnquiryYear r the primary keys
tblQuotation:QuotationNumber,QuotationYear r the primary
keys

I was thinking to add a field "EnquiryYear" in
tblQuotation so that even if the user adds a Quotation no
of 1, at least the Year could help differentiate the
correct enquiry No.

U think this is correct?

Ling
 
tblCosting tblQuotation
---------- --------------
EnquiryNo QuotationNumber
EnquiryYear QuotationYear
Item EnquiryNo
Qty etc
etc

tblCosting: EnquiryNo, EnquiryYear r the primary keys
tblQuotation:QuotationNumber,QuotationYear r the primary
keys

I was thinking to add a field "EnquiryYear" in
tblQuotation so that even if the user adds a Quotation no
of 1, at least the Year could help differentiate the
correct enquiry No.

U think this is correct?
Click to expand...

If the Primary Key of tblCosting is the two fields EnquiryYear and
EnquiryNo, and if you want to relate each record in tblCosting to
corresponding records in tblQuotation, then yes: you must have both
fields.

If EnquiryYear is always the same as QuotationYear you could just join
EnquiryNo to EnquiryNo and EnquiryYear to QuotationYear, but I'd guess
that you could have Costing record created in one year and a
Quotation in a different year, which would pull the WRONG record!
 
Back
Top