Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Access Relationships in VB

Posted on 2001-06-11
18
Medium Priority
?
186 Views
Last Modified: 2010-05-02
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.


0
Comment
Question by:nerfgunz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 3
  • +2
18 Comments
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6179809
You could try the query like, to retrieve the table3 data:

SELECT Table3.* FROM Table3,Table2 WHERE Table3.CustomerID = Table2.CustomerID AND Table2.ClientID = '" & txtClientID & "'"

hope it helps.
Cheers
0
 
LVL 53

Expert Comment

by:Ryan Chong
ID: 6179819
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.
0
 

Author Comment

by:nerfgunz
ID: 6181383
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.

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 53

Expert Comment

by:Ryan Chong
ID: 6181426
You need to Link those tables manually in "relationship".
0
 

Author Comment

by:nerfgunz
ID: 6181476
Yes linked them.
0
 
LVL 53

Expert Comment

by:Ryan Chong
ID: 6184112
Hi nerfgunz, so you get the result you wanted?
0
 

Author Comment

by:nerfgunz
ID: 6184162
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"
0
 
LVL 53

Expert Comment

by:Ryan Chong
ID: 6184180
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.
0
 

Author Comment

by:nerfgunz
ID: 6184227
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?
0
 
LVL 53

Expert Comment

by:Ryan Chong
ID: 6184242
Let me try and get back later. :)
0
 
LVL 53

Expert Comment

by:Ryan Chong
ID: 6184294
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.
0
 

Author Comment

by:nerfgunz
ID: 6186753
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.
0
 
LVL 9

Accepted Solution

by:
Valliappan AN earned 200 total points
ID: 6187098
hi,
>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.
There is nothing wrong in a Customer ID exist, for which there is no Invoice existing. Nothing to do for this to design the database.

Try this:

"SELECT customer.cusid
FROM (customer INNER JOIN invoice ON customer.cusid = invoice.custid) INNER JOIN lead ON invoice.id = lead.id GROUP BY customer.custid"

If you wish to filter for txtID, do this:

"SELECT customer.cusid
FROM (customer INNER JOIN invoice ON customer.cusid = invoice.custid) INNER JOIN lead ON invoice.id = lead.id WHERE invoice.id = " & txtID & " GROUP BY customer.custid"

hope it helps.

0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7135911
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
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 7136843
>> 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.
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7136990
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
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7151114
Recommended disposition:

  Accept valli_an's comment(s) as an answer.

DanRollins -- EE database cleanup volunteer
0
 
LVL 1

Expert Comment

by:Computer101
ID: 7168338
Comment from expert accepted as answer

Computer101
E-E Moderator
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question