Selecting all records for TWO tables via an SQL command

[peskywinnets]

To avoid a ridiculously long SQL Select statement, how do I select all record from two tables?

So in the example below, I've two tables [Customers] & [Invoice] which are joined by CustomerID

SELECT Customers.BillingName, Invoice.InvoiceNumber, Customers.BillingAddress1, Invoice.OrderDateTime
FROM Customers INNER JOIN Invoice ON Customers.CustomerID = Invoice.CustomerID;

...but rather than nominate each record individually, I want all records from both tables in the select statement.

Couldn't locate the syntax via google!

 

[Galaxiom]

SELECT Customers.*, Invoice.*
FROM Customers 
INNER JOIN Invoice 
ON Customers.CustomerID = Invoice.CustomerID;

or

SELECT *
FROM Customers 
INNER JOIN Invoice 
ON Customers.CustomerID = Invoice.CustomerID;

 

[peskywinnets]

Many thanks

 

[CJ_London]

or just

SELECT *
FROM Customers 
INNER JOIN Invoice 
ON Customers.CustomerID = Invoice.CustomerID

;

 

[peskywinnets]

I'm having issues!

There are actually three tables involved

This shows the joins...

SELECT ShippingService.ShippingService, Customers.Email, Invoice.InvoiceNumber
FROM (Customers INNER JOIN Invoice ON Customers.CustomerID = Invoice.CustomerID) INNER JOIN ShippingService ON Invoice.ShippingServiceID = ShippingService.ShippingServiceID;

but when I used a wildcard * like this...

SELECT ShippingService.*, Customers.*, Invoice.*
FROM (Customers INNER JOIN Invoice ON Customers.CustomerID = Invoice.CustomerID) INNER JOIN ShippingService ON Invoice.ShippingServiceID = ShippingService.ShippingServiceID;

When I run my VBA code it complains that some fields I'm referencing "aren't found in the collection"......so clearly my select syntax is wrong :-(

 

[Dystonia]

Try changing line one to .......

SELECT *

.....as per CJ's post and see if you get an error

 

[peskywinnets]

This is odd, so I tried the * i.e....

SELECT * FROM (Customers INNER JOIN Invoice ON Customers.CustomerID = Invoice.CustomerID) INNER JOIN ShippingService ON Invoice.ShippingServiceID = ShippingService.ShippingServiceID;

My vba code is happy (finds all the records referenced), except one particular record in the ShippingService table (ShippingService.ShippingServiceID ....which should have been captured by the same wildcard).

Also, if I place the above command in query window, the query runs fine...so this is something to do with the SQL syntax used within VBA?

 

[arnelgp]

maybe you should use Left Join.

 

[CJ_London]

the wildcard in this scenario does not mean all records, it means all fields, so arnelgp's suggestion is probably correct if you are missing a record. Or perhaps the record is missing because it does not have a matching record in the invoice table

 

[peskywinnets]

the wildcard in this scenario does not mean all records, it means all fields, so arnelgp's suggestion is probably correct if you are missing a record. Or perhaps the record is missing because it does not have a matching record in the invoice table

But when I use the * in a query ...it works - the field definitely exists in the table

maybe you should use Left Join.

I must confess, I've never full got to grips with joins (no matter how many times I look at the diagrammatic representations!)...I simply construct the SQL via an Access query, then copy/paste the SQL produced into my VBA 'Select' statement. There are a couple of joins...which one are you saying should use left join?

 

[arnelgp]

go back to design view of your query.
right-click on the relationship line (one at a time).
choose Edit Relationship...
click Join Type button.
Select No.2 option.

 

[peskywinnets]

go back to design view of your query.
right-click on the relationship line (one at a time).
choose Edit Relationship...
click Join Type button.
Select No.2 option.

Thanks for taking the time - I did as you suggested...

SELECT * FROM (Customers LEFT JOIN Invoice ON Customers.CustomerID = Invoice.CustomerID) LEFT JOIN ShippingService ON Invoice.ShippingServiceID = ShippingService.ShippingServiceID;

but when my code runs...

strSQL = "SELECT * FROM (Customers LEFT JOIN Invoice ON Customers.CustomerID = Invoice.CustomerID) LEFT JOIN ShippingService ON Invoice.ShippingServiceID = ShippingService.ShippingServiceID;"

Set rst = CurrentDb.OpenRecordset(strSQL)

If (rst!ShippingServiceID = 7 Or rst!ShippingServiceID = 10 Or rst!ShippingServiceID = 39) Then

I get this...

[/code]

which when I click on Debug, it takes me to...



...so the field ShippingService.ShippingServiceID is not in the collection (but the field definitely exists in the ShippingService Table )

As I say, when I select the explicit field needed (vs. using wildcards) it all works (but selecting individual field will result in a massive 'select' command - which I'll do if necessary, I just figured there ought to be a way to use a wildcard!) .....so this must be down to syntax in the SQL command within VBA.

 

[jdraw]

Pesky,

Please step back and tell us what you are trying to do in plain English.
You mentioned all records in 2 tables; then told us it was really 3 tables...??

I have a feeling there is a business reason/rationale behind your post--- please tell us what it is.

Tell or show us the tables involved and what output you seek.

 

[Dystonia]

The problem is your criteria

You have field names with the same name in more than one table in the same recordset

try rst![TableName.ShippingServiceID] =7 or.....

 

[arnelgp]

you can make a test.
on your code, after creating rst recordset, add this line:


Dim fld As DAO.Field
Stop
For Each fld In rst.Fields
Debug.Print fld.Name
Next


if you see that there are dot in field name, dont use ! when accessing this field.
instead enclosed it in parenthesis, eg:


if rs("shippingService.ShippingServiceID") = 7 ....

 

[CJ_London]

and here was me thinking we are talking about a query, not sql code used in vba

 

[peskywinnets]

Pesky,

Please step back and tell us what you are trying to do in plain English.
You mentioned all records in 2 tables; then told us it was really 3 tables...??

I have a feeling there is a business reason/rationale behind your post--- please tell us what it is.

Tell or show us the tables involved and what output you seek.

Basically this is my setup...




...I've written some VBA to automatically email customer their sales receipt. I have it working *when* all the fields required are specifically selected in the SQL select command...the problem is it's a very long 'select' statement, so I figured I could press wild cards into play.

The problem is your criteria

You have field names with the same name in more than one table in the same recordset

try rst![TableName.ShippingServiceID] =7 or.....

Bingo - that was it (have a cigar!)

Yes, I realised there was a field with the same name in two tables (this is done so that I know how/where to join the two tables), but the error message I was getting wasn't one along the lines of "I don't know which record you mean", but more of "I can find any record like that in the collection!"....so I'm blaming Microsoft! Hah.

Many thanks for all your input & sorry if I wasn't clear in my wording