Data from multiple tables on one report

[Darhl Thomason]

I have a 2003 db with multiple tables that all refer to each other. My
tblStoreData has fields like StoreNumber, StorePhone, StoreName, OwnerID,
RegionID, DistrictID that contain numerical values that correspond to data
in other tables like tblOwners which has OwnerID, OwnerName; tblRegion which
has RegionID, RegionName; tblDistrict which has DistrictID, DistrictName.

What I'm trying to do is print a report that has all of this data on it. I
have my report designed and semi working, I have an inner join pulling the
OwnerID's together so it shows the Owner's Name on my report. I think I
need to keep doing inner joins to add the RegionName and DistrictName, but I
just can't seem to make it work.

Here's the code I have that pulls the owner's data into the report:

strSQL = "SELECT * FROM tblStoreData INNER JOIN tblOwners on
tblStoreData.OwnerID = tblOwners.OwnerID"
Me.RecordSource = strSQL

I want my report to show this data:
Number Name Owner
Phone Region District
123 Alpha Store Joe Blow Owner
123-456-7890 Central Mountain

Thanks!

Darhl

 

[Marshall Barton]

I have a 2003 db with multiple tables that all refer to each other. My
tblStoreData has fields like StoreNumber, StorePhone, StoreName, OwnerID,
RegionID, DistrictID that contain numerical values that correspond to data
in other tables like tblOwners which has OwnerID, OwnerName; tblRegion which
has RegionID, RegionName; tblDistrict which has DistrictID, DistrictName.

What I'm trying to do is print a report that has all of this data on it. I
have my report designed and semi working, I have an inner join pulling the
OwnerID's together so it shows the Owner's Name on my report. I think I
need to keep doing inner joins to add the RegionName and DistrictName, but I
just can't seem to make it work.

Here's the code I have that pulls the owner's data into the report:

strSQL = "SELECT * FROM tblStoreData INNER JOIN tblOwners on
tblStoreData.OwnerID = tblOwners.OwnerID"
Me.RecordSource = strSQL

I want my report to show this data:
Number Name Owner
Phone Region District
123 Alpha Store Joe Blow Owner
123-456-7890 Central Mountain

You are correct that you need to join the other tables.

If your question is How to do that, just add the other
tables to the query's design window and make sure the
connecting lines are between the related fields. Rhen drag
the desired fields down to the query's field list.

 

[Darhl Thomason]

Marshall,

Thanks for replying. My db is pretty much written in VBA, can I still use
the query builder to put it all together? I have a previous post titled
"Inner Join Problem" dtd 10/24. It has the code I tried to write to
accomplish this.

Thanks,

Darhl

 

[Marshall Barton]

To get all those parenthesis in the right place I would use
the query design window to create and test the query. Once
that is working, then Copy/Paste it into your code and add
the quotes and line continuations.

I am not going to set this up for testing, but I think it
will probably end up looking more like:

strSQL = "SELECT * FROM ((tblStoreData " _
& "INNER JOIN tblOwners " _
& "On tblStoreData.DistrictID=tblDistrict.DistrictID) " _
& "INNER JOIN tblRegion " & _
& "On tblStoreData.RegionID = tblRegion.RegionID) " _
& "INNER JOIN tblDistrict " _
& "On tblStoreData.OwnerID = tblOwners.OwnerID"

 

[Darhl Thomason]

Thanks Marsh!

That was the ticket. I didn't know I could use the query tool then turn it
into SQL. That worked great. I have already used that for some of my other
stuff as well.

Thanks again for the great tip!

Darhl

To get all those parenthesis in the right place I would use
the query design window to create and test the query. Once
that is working, then Copy/Paste it into your code and add
the quotes and line continuations.

I am not going to set this up for testing, but I think it
will probably end up looking more like:

strSQL = "SELECT * FROM ((tblStoreData " _
& "INNER JOIN tblOwners " _
& "On tblStoreData.DistrictID=tblDistrict.DistrictID) " _
& "INNER JOIN tblRegion " & _
& "On tblStoreData.RegionID = tblRegion.RegionID) " _
& "INNER JOIN tblDistrict " _
& "On tblStoreData.OwnerID = tblOwners.OwnerID"
--
Marsh
MVP [MS Access]

Thanks for replying. My db is pretty much written in VBA, can I still use
the query builder to put it all together? I have a previous post titled
"Inner Join Problem" dtd 10/24. It has the code I tried to write to
accomplish this.