nerfgunz
asked on
Access Relationships in VB
Hello, I have a MS Access Database containing three related tables.
- Table 1 is related to Table 2 by the field ClientID
- ID is my primary key.
- Table 2 is related to Table 3 by Customer ID
I have a form with tons of textboxes bounded to data from the Clients table. I want the information from Table 3 related to Table 1 to be bounded to other textboxes.
Is there a way this can be done? Because I am finding it hard.
- Table 1 is related to Table 2 by the field ClientID
- ID is my primary key.
- Table 2 is related to Table 3 by Customer ID
I have a form with tons of textboxes bounded to data from the Clients table. I want the information from Table 3 related to Table 1 to be bounded to other textboxes.
Is there a way this can be done? Because I am finding it hard.
Hi nerfgunz, if there is some relationship between those tables and using Foreign Keys (normalize tables), you need to use JOIN (INNER JOIN / OUTER JOIN) in SQL.
ASKER
Well I created the tables in Access and set ClientID as the primary key. I set the relationships as mentioned above, but did not formally set Foreign Keys because I think Access 2000 does it automatically for me. I may be wrong about this.
You need to Link those tables manually in "relationship".
ASKER
Yes linked them.
Hi nerfgunz, so you get the result you wanted?
ASKER
No =* (
I tried binding a ADO datacontrol recordsource to an SQL statement similiar to the one suggested by valli_an as follows:
SELECT CUSID FROM invoices WHERE lead.ID = invoices.ID;
however, I get the following error:
"No value give for one or more required parameters"
I tried binding a ADO datacontrol recordsource to an SQL statement similiar to the one suggested by valli_an as follows:
SELECT CUSID FROM invoices WHERE lead.ID = invoices.ID;
however, I get the following error:
"No value give for one or more required parameters"
Hi nerfgunz,
I think you need to add JOIN in your SQL.
Anyway, Can you show all the fields for table1, table2 and table3 that want to display? and the relationship between each of them so that i can help as i can, thanks.
I think you need to add JOIN in your SQL.
Anyway, Can you show all the fields for table1, table2 and table3 that want to display? and the relationship between each of them so that i can help as i can, thanks.
ASKER
Ok thank you,
I have 3 tables: lead, invoices, and customers.
lead one has my primary key which is the column, ID.
lead is related to the table invoices which also has a column ID. They are linked one-to-many.
invoices is related to customers by the column CUSID. They are NOT linked one-to-many.
So basically, ID from lead points to ID in invoices. Invoices contains CUSID which points to CUSID in the table CUSTOMERS.
I would like to extract all the data out of CUSTOMERS which is related to CUSID which is related to the ID selected in table one.
Confused?
I have 3 tables: lead, invoices, and customers.
lead one has my primary key which is the column, ID.
lead is related to the table invoices which also has a column ID. They are linked one-to-many.
invoices is related to customers by the column CUSID. They are NOT linked one-to-many.
So basically, ID from lead points to ID in invoices. Invoices contains CUSID which points to CUSID in the table CUSTOMERS.
I would like to extract all the data out of CUSTOMERS which is related to CUSID which is related to the ID selected in table one.
Confused?
Let me try and get back later. :)
Hi nerfgunz,
firstly, i think you need to establish a relationship between Invoice.CUSID and the Customer.CUSID, as the linked result, here is a query that might help:
SELECT Invoice.InvoiceID, Customer.field1, Customer.field2
FROM Lead INNER JOIN (Customer INNER JOIN Invoice ON Customer.CUSID = Invoice.CUSID) ON Lead.ID = Invoice.ID
WHERE Lead.ID=1;
If this is not what you want, tell me again. Thanks.
firstly, i think you need to establish a relationship between Invoice.CUSID and the Customer.CUSID, as the linked result, here is a query that might help:
SELECT Invoice.InvoiceID, Customer.field1, Customer.field2
FROM Lead INNER JOIN (Customer INNER JOIN Invoice ON Customer.CUSID = Invoice.CUSID) ON Lead.ID = Invoice.ID
WHERE Lead.ID=1;
If this is not what you want, tell me again. Thanks.
ASKER
ryancys,
I cannot actually link the two CUSID's because this was a very poorly designed database. There are values that exist in Customer.CUSID that do not exist in Invoices.CUSID.
So, this is what I am attempting to do. Extract all the CUSID's from invoices were Invoices.ID = lead.ID.
Then use these CUSID's to extract the necessary information from Customer table.
However, my SQL statement,
SELECT invoices.CUSTID FROM invoices where txtID.Text = invoices.ID;
does not work because it doesn't know which ID to take info from.
I will give you comment a try and get back to you. Thanks.
I cannot actually link the two CUSID's because this was a very poorly designed database. There are values that exist in Customer.CUSID that do not exist in Invoices.CUSID.
So, this is what I am attempting to do. Extract all the CUSID's from invoices were Invoices.ID = lead.ID.
Then use these CUSID's to extract the necessary information from Customer table.
However, my SQL statement,
SELECT invoices.CUSTID FROM invoices where txtID.Text = invoices.ID;
does not work because it doesn't know which ID to take info from.
I will give you comment a try and get back to you. Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi nerfgunz,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will suggest to:
Accept ryancys's comment(s) as an answer.
nerfgunz, if you think your question was not answered at all or if you need help, you can simply post a new comment here. Community Support moderators will follow up.
EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will suggest to:
Accept ryancys's comment(s) as an answer.
nerfgunz, if you think your question was not answered at all or if you need help, you can simply post a new comment here. Community Support moderators will follow up.
EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer
>> I would like to extract all the data out of CUSTOMERS which is related to CUSID which is related to the ID selected in table one.
I think nerfgunz, wants all records in CUSTOMERS table, so I hope, it should be the first table to FROM criteria in SELECT statement, as one in my last comment. You can correct me if I am wrong.
I think nerfgunz, wants all records in CUSTOMERS table, so I hope, it should be the first table to FROM criteria in SELECT statement, as one in my last comment. You can correct me if I am wrong.
hi valli_an, I'll admit got lost on this one -- no db schema is given and the desired result seems ambiguous. If you think your answer is better, I'll change my recommendation. -- Dan
Recommended disposition:
Accept valli_an's comment(s) as an answer.
DanRollins -- EE database cleanup volunteer
Accept valli_an's comment(s) as an answer.
DanRollins -- EE database cleanup volunteer
Comment from expert accepted as answer
Computer101
E-E Moderator
Computer101
E-E Moderator
SELECT Table3.* FROM Table3,Table2 WHERE Table3.CustomerID = Table2.CustomerID AND Table2.ClientID = '" & txtClientID & "'"
hope it helps.
Cheers