Get data from multiple tables 2

[mdctsg]

I have a database that has info in multiple tables. I am trying to get certain columns from each table into a grid, or some way of viewing these fields. Is this possible.

Thanks
Bob
Example: Field 1, Field 2 from Table 1
Field 4, Field 6 from Table 2
Field 1, Field 7 from Table 3

 

[petermeachem]

It is if you have common field(s) in the tables. Look up JOIN and WHERE. The sort of thing you want is

SELECT table1.field1,table2.field1 FROM table1,table2 WHERE
table1.field3 = table2.field4

There are a lot of variations so you need to check that out. Oh, you may not be able to edit the resulting recordset, just view it. Peter Meachem
peter @ accuflight.com

 

[petermeachem]

It is if you have common field(s) in the tables. Look up JOIN and WHERE. The sort of thing you want is

SELECT table1.field1,table2.field1 FROM table1,table2 WHERE
table1.field3 = table2.field4

There are a lot of variations so you need to check that out. Oh, you may not be able to edit the resulting recordset, just view it. Peter Meachem
peter @ accuflight.com

 

[mdctsg]

The ado doesn't seem to let me connect to more than one table. I will keep trying. Thanks

Bob

 

[petermeachem]

Are you using the data control? If so look at the Recordsource tab on the property pages. If you aren't, you can do this sort of thing.

csql = "SELECT Addresses.*"
csql = csql & " FROM Addresses INNER JOIN PromotionMembers ON Addresses.UniqueKey = PromotionMembers.UniqueKey"
csql = csql & " WHERE PromotionMembers.nlProNumber = " & iPromSelected

rs.Open csql, cnn, adOpenStatic, adLockOptimistic Peter Meachem
peter @ accuflight.com

 

[SemperFiDownUnda]

ADO will let you join tables IF the database you are connecting to supports it.

Use the SQL SELECT statement

SELECT Table1.Field1, Table2.Field1,...
FROM Table1
INNER JOIN Table2
ON Table1.Fieldx = Table2.Fieldy
...
WHERE ...


now you can join any number of tables via the FROM Clause of the select statement

ie
FROM Table1
INNER JOIN Table2
ON Table1.Fieldx = Table2.Fieldy
INNER JOIN Table3
ON Tablen.Fieldx = Table3.Fieldz
...


Tablen is just which of the first 2 table you want to join to.

INNER JOIN requires the ON clause to return true before the record is added
there is also OUTER JOIN which lets you pick a base table and join a 2nd table but will always show you all of the base tables records and the 2nd tables data will be NULL if a matching record, via the ON clause, is not found. This is needed in a situation where you may have a parent table but no children but still want to see the parents without children in the result set.

 

[irlbinky]

Another way if there are common fields between some of the tables

Example
table 1 field 1:ID No
field 2:Name
field 3:Address Line 1
field 4:Address Line 2 ..etc

table 2 field 1:ID No
field 2:Family
field 3:Job

table 3 field 1:job
field 2escription

"SELECT table1.field2, table1.field3, table1.field4, table2.field2, table3.field2 FROM table1, table2, table3
WHERE table1.field1 = table2.field1 AND table3.field1 = table2.field3"

Should return a table like
Name
Address Line 1
Address Line 2
Family
Job
Descriptio

 

[mdctsg]

Ok I have tried it different ways. I know I am doing something wrong. I am such a dork! It seems to bring in the headers with no info in the grid. Here is what I am doing.

Option Explicit
Dim mblnLoaded As Boolean
Dim mblnMouse As Boolean
Dim WithEvents cn As ADODB.Connection
Dim WithEvents rs As ADODB.Recordset


Private Sub Form_Load()


Dim strConnect As String

strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
App.Path & "\Master.mdb"

Set cn = New ADODB.Connection
cn.CursorLocation = adUseClient
cn.Open strConnect

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.CursorType = adOpenForwardOnly
rs.LockType = adLockPessimistic
rs.Source = "SELECT Agent.customerId, Order.OrderN
FROM Agent, Oder WHERE Agent.customerId =
Order.OrderNo"
rs.ActiveConnection = cn
rs.Open


mblnLoaded = False
Call LoadFG
mblnLoaded = True

End Sub

Private Sub LoadFG()
fg.AllowUserResizing = flexResizeBoth
fg.Cols = rs.Fields.Count + 1
Dim i As Integer
fg.Row = 0
For i = 0 To rs.Fields.Count - 1
fg.Col = i + 1
fg.ColAlignment(i) = flexAlignLeftCenter
fg.Text = rs.Fields(i).Name
Next
Do While Not rs.EOF
fg.Rows = fg.Rows + 1
fg.Row = fg.Rows - 1
For i = 0 To rs.Fields.Count - 1
fg.Col = i + 1
fg.Text = rs(i).Value & ""
Next
rs.MoveNext
Loop

End Sub

 

[petermeachem]

Are you quite sure that this

WHERE Agent.customerId =
Order.OrderNo

is correct. It looks like each customer can only make one order?

I would have expected something more like

WHERE Agent.customerId =
Order.customerId

This is the sort of thing that seems quite unintuitive and one day it will click, so no you aren't a dork. Well you may be, but not because you can't get your head round this at present. Peter Meachem
peter @ accuflight.com

 

[mdctsg]

I may not be explaining it right. My fault

My tables
Agent: with customerId as one of the fields
Orders: with OrderNo as one of it's fields

What I am trying to do is connect the customerId (from Agent) with the OrderNo (From Orders) into the grid and perhaps later add fields from the rest of the db. Such as address, city, state zip, name from the Agent table and connect with the OrderNo, and Reference Fields from the Orders table.

I figure it would be safer to try two fields first to see if I can get it right and do the others later.

I appreciate what you are doing to help me.
Thanks

Bob

 

[bjd4jc]

petermeachem said it right,,, if you are comparing the customerId to the Order ID then you will probably not get any matches,,, and if you do get matches it is more than likely not correct.

In your orders table, do you have a field that stores the Customer who placed that order? If so, you need to compare the Agent.CustomerId to that field. If not, then you will need to create that field so there can be some sort of relationship.

 

[johnwm]

tblOrders may contain:
OrderID
Item
Quantity
AgentID
CustomerID

tblCustomers may contain:
CustomerID
CustomerName
CustomerAddress

tblAgent may contain:
AgentID
AgentName

You can then look up the details of orders from a particular customer with the code like:
"Select * from tblOrders where Customer ID = " & myID

Before going much further, try looking up Access Help/Getting started. It's got a good section on setting up relational databases from square one. Let me know if this helps
________________________________________________________________
If you are worried about how to post, please check out FAQ222-2244 first

'There are 10 kinds of people in the world: those who understand binary, and those who don't.'

 

[mdctsg]

Thanks to all of you for your help. This db has no link to the other table that I can find. This is a situation where I cannot alter the db in anyway it belongs to the company I work for. I know these fields can be linked, they are done thru csv export often. If someone wants to write me privately I can send them a copy and they can see if it is possible. It is 600k in size. Atleast the one I am using to work with.
rtaylor327@comcast.net
I can tell you the fields and tables in better detail
Again thank you all for you time and effort. You have been very paitent and kind

Bob

 

[mdctsg]

Ok I got it

I wasn't looking at the primary keys.

In another table called Deliveries there is a
column called foreignKey which links to the 'primary
key in the Agent table. In the Orders table the primary
key links to the primary key in the Deliveries table. What
I mean by links is having the same number. So I used:

SELECT Agent.customerId, Orders.OrderNo FROM Agent, Orders, Deliveries
WHERE Agent.primaryKey = Deliveries.foreignKey AND Orders.primaryKey =
Deliveries.primaryKey

Thanks to all of you for the help and kindness. It's nice to be able to come here as a newbie and get good help without being flamed.

Bob