Multiple INNER JOIN statements

D

Darhl Thomason

I have an Access 2003 db with 4 tables.
tblFC has FCID and FCName
tblOwners has CompanyID and Owner
tblType has TypeID and TypeName
tblStoreData has multiple fields, the important ones here are FC, Type,
Owner

My current INNER JOIN statement is only joining tblOwners and tblStoreData
like this:
& "FROM tblOwners INNER JOIN tblStoreData ON (tblOwners.CompanyID =
tblStoreData.Owner) "

This works perfectly (thanks Nikos!) and my resulting form shows my data
with my owner's name. I'd like it to also show the type name properly
instead of the type ID and the FC name instead of the FC ID.

If you look at the tblStoreData in datasheet mode, it does show owner name,
fc name and type properly (with dropdowns to select/change the field).

I've been playing with the INNER JOIN quite a bit and can't quite get it
figured out. Can anyone help?

Thanks!

Darhl
 
D

Darhl Thomason

I thought it may be important to have my entire SQL statement, so here it
is:

strSQL = "SELECT tblOwners.OwnerID, tblOwners.OwnerName, tblStoreData.FC, "
_
& "tblStoreData.Store, tblStoreData.Number, " _
& "tblStoreData.Type, tblStoreData.StoreOpen, tblStoreData.Phone, " _
& "tblStoreData.Processor, tblStoreData.[Merch#],
tblStoreData.EFSNetLogin, " _
& "tblStoreData.EFSNetPW, tblStoreData.EFSNetID, tblStoreData.EFSNetKey,
" _
& "tblStoreData.PCChargeSerial, tblStoreData.PCChargeSecurityCode,
tblStoreData.PCChargeSystemCode, " _
& "tblStoreData.Visa, tblStoreData.MC, tblStoreData.Amex,
tblStoreData.Disc, " _
& "tblStoreData.Debit, tblStoreData.EBT, tblStoreData.Parallel_Printers,
" _
& "tblStoreData.CC_Setup, tblStoreData.Oneac, tblStoreData.DSL, " _
& "tblStoreData.Static_IP, tblStoreData.PinPads, tblStoreData.Cable, " _
& "tblStoreData.iMobile, tblStoreData.POC, tblStoreData.Comments " _
& "FROM tblOwners INNER JOIN tblStoreData ON (tblOwners.OwnerID =
tblStoreData.Owner) "
 
D

Darhl Thomason

Wow, I impressed myself! I actually figured this out, too! Here's what I
changed my SQL statement to:

strSQL = "SELECT tblOwners.OwnerID, tblOwners.OwnerName, tblFC.FCID,
tblFC.FCName, " _
& "tblType.TypeID, tblType.TypeName, tblStoreData.Store,
tblStoreData.Number, " _
& "tblStoreData.StoreOpen, tblStoreData.Phone, tblStoreData.Processor,
tblStoreData.[Merch#], " _
& "tblStoreData.EFSNetLogin, tblStoreData.EFSNetPW,
tblStoreData.EFSNetID, tblStoreData.EFSNetKey, " _
& "tblStoreData.PCChargeSerial, tblStoreData.PCChargeSecurityCode,
tblStoreData.PCChargeSystemCode, " _
& "tblStoreData.Visa, tblStoreData.MC, tblStoreData.Amex,
tblStoreData.Disc, " _
& "tblStoreData.Debit, tblStoreData.EBT, tblStoreData.Parallel_Printers,
" _
& "tblStoreData.CC_Setup, tblStoreData.Oneac, tblStoreData.DSL, " _
& "tblStoreData.Static_IP, tblStoreData.PinPads, tblStoreData.Cable, " _
& "tblStoreData.iMobile, tblStoreData.POC, tblStoreData.Comments " _
& "FROM ((tblOwners INNER JOIN tblStoreData ON tblOwners.OwnerID =
tblStoreData.Owner) " _
& "INNER JOIN tblType ON tblType.TypeID = tblStoreData.Type) " _
& "INNER JOIN tblFC ON tblFC.FCID = tblStoreData.FC "


Darhl Thomason said:
I thought it may be important to have my entire SQL statement, so here it
is:

strSQL = "SELECT tblOwners.OwnerID, tblOwners.OwnerName, tblStoreData.FC,
" _
& "tblStoreData.Store, tblStoreData.Number, " _
& "tblStoreData.Type, tblStoreData.StoreOpen, tblStoreData.Phone, " _
& "tblStoreData.Processor, tblStoreData.[Merch#],
tblStoreData.EFSNetLogin, " _
& "tblStoreData.EFSNetPW, tblStoreData.EFSNetID,
tblStoreData.EFSNetKey, " _
& "tblStoreData.PCChargeSerial, tblStoreData.PCChargeSecurityCode,
tblStoreData.PCChargeSystemCode, " _
& "tblStoreData.Visa, tblStoreData.MC, tblStoreData.Amex,
tblStoreData.Disc, " _
& "tblStoreData.Debit, tblStoreData.EBT,
tblStoreData.Parallel_Printers, " _
& "tblStoreData.CC_Setup, tblStoreData.Oneac, tblStoreData.DSL, " _
& "tblStoreData.Static_IP, tblStoreData.PinPads, tblStoreData.Cable, "
_
& "tblStoreData.iMobile, tblStoreData.POC, tblStoreData.Comments " _
& "FROM tblOwners INNER JOIN tblStoreData ON (tblOwners.OwnerID =
tblStoreData.Owner) "


Darhl Thomason said:
I have an Access 2003 db with 4 tables.
tblFC has FCID and FCName
tblOwners has CompanyID and Owner
tblType has TypeID and TypeName
tblStoreData has multiple fields, the important ones here are FC, Type,
Owner

My current INNER JOIN statement is only joining tblOwners and
tblStoreData like this:
& "FROM tblOwners INNER JOIN tblStoreData ON (tblOwners.CompanyID =
tblStoreData.Owner) "

This works perfectly (thanks Nikos!) and my resulting form shows my data
with my owner's name. I'd like it to also show the type name properly
instead of the type ID and the FC name instead of the FC ID.

If you look at the tblStoreData in datasheet mode, it does show owner
name, fc name and type properly (with dropdowns to select/change the
field).

I've been playing with the INNER JOIN quite a bit and can't quite get it
figured out. Can anyone help?

Thanks!

Darhl
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

INNER JOIN questions 5
Inner Join problem 0
perform INNER JOIN on query results 2
Combo box based on another combo box 1
Data from multiple tables on one report 4
String Manipulation in an Inner Join 1
Need help creating temporary table from filter 11
Wildcard on an "INNER JOIN" 5

Top