Simple Select Query Question...... 1

[Cordury]

I have two tables- tblStore has all store information. tblAddress contains store addresses (pretty clever, I know).

I want to create one master table so I can remove the Address Subform.

The select query I use now links the table by StoreNumber but does not return all the stores in the tblStore table. Could this be because some of those stores are not on tblAddress?

Thanks,
Cord

 

[lespaul]

Yes, it doesn't return stores without an address due to the relationship between the two tables. If you open the relationship window, right click on the line representing the relationship between stores and address, you can change the relationship to include all the records from the store table and only those records that match in the address table. If you don't want to change the relationship (since it will effect everything) you can re-write the query:

SELECT * FROM tblSTORES LEFT JOIN tblADDRESS ON tblSTORES.STOREID = tblADRESS.STOREID

which should return all the stores and those with addresses will be included those without will have blank cells.

HTH

(and yes, it was VERY clever of you!)

 

[Cordury]

Thank you for the quick response HTH.

Changing the relationship property did not seem to work, the query still returned the same amount of rows.

Ther current SQL is:
SELECT Construction071503.StoreNum, Construction071503.Division, Construction071503.[Project Mgr], Construction071503.[Project Type], Construction071503.[Poss Date], Construction071503.[Const Date], Construction071503.[Const End Date], Construction071503.Comments, Construction071503.[Account Status], Construction071503.[Electric Co], Construction071503.EDeposit, Construction071503.[EAccount #], Construction071503.[EMeter#], Construction071503.[First Electric], Construction071503.[Final Electric], Construction071503.[Gas Co], Construction071503.[Gas Deposit], Construction071503.[GAccount #], Construction071503.[GMeter#], Construction071503.[First Gas], Construction071503.[Final Gas], Construction071503.[Water Co], Construction071503.WDeposit, Construction071503.[WAccount #], Construction071503.[WMeter#], Construction071503.[First Water], Construction071503.[Final Water], Construction071503.[Sewer Co], Construction071503.[SAccount #], Construction071503.[First Sewer], Construction071503.[Final Sewer], Construction071503.[Old Perm], Construction071503.Temp, Construction071503.[New Perm], tblAddress.[Business Unit], tblAddress.District, tblAddress.[Mall Name], tblAddress.[Address 2], tblAddress.[Address 3], tblAddress.[Address 4], tblAddress.[Mall Phone], tblAddress.City, tblAddress.State, tblAddress.[Postal/Zip], tblAddress.[Store Phone], tblAddress.[Mall PhoneII]
FROM Construction071503 INNER JOIN tblAddress ON Construction071503.StoreNum = tblAddress.StoreNum;


I can replace all of it with your suggested code?

note: Construction071503 is the table with all of the store info (obviously).

Thanks again!
Cord

 

[lespaul]

First of all, I would use an alias:

SELECT C.StoreNum, C.Division, C.[Project Mgr], C.[Project Type], C.[Poss Date], C.[Const Date], C.[Const End Date], C.Comments, C.[Account Status], C.[Electric Co], C.EDeposit, C.[EAccount #], C.[EMeter#], C.[First Electric], C.[Final Electric], C.[Gas Co], C.[Gas Deposit], C.[GAccount #], C.[GMeter#], C.[First Gas], C.[Final Gas], C.[Water Co], C.WDeposit, C.[WAccount #], C.[WMeter#], C.[First Water], C.[Final Water], C.[Sewer Co], C.[SAccount #], C.[First Sewer], C.[Final Sewer], C.[Old Perm], C.Temp, C.[New Perm], A.[Business Unit], A.District, A.[Mall Name], A.[Address 2], A.[Address 3], A.[Address 4], A.[Mall Phone], A.City, A.State, A.[Postal/Zip], A.[Store Phone], A.[Mall PhoneII]
FROM Construction071503 C LEFT JOIN tblAddress A ON C.StoreNum = A.StoreNum;

This should return the record set you're looking for, it will have all the records from Construction071503 and if there are matching rows in address it will have them. (I hope, technically it SHOULD work!)

HTH

Leslie

 

[Cordury]

Good Morning Leslie,

Thanks again for the suggestion. I had to leave work early yesterday so after I catch up on some things, I will try your recommendation. HAve a good day.

Cord

 

[Cordury]

I guess I did not realize how easy that would be. I replaced the SQL and everything lookds GREAT so far. I need to do some more research to undserstand WHY it works though. I am not familiar with "alias". Thanks so much!

 

[lespaul]

Here's a great link for joins:

http://www.devshed.com/Server_Side/MySQL/SQLJoins/page1.html

The alias is just a way to refer to the table name by placing an "alias" after it the first time you refer to it. So in the FROM clause in you statement, where you first reference Construction071503, I put a C that indicates that wherever in the statement there's a C it refers to that table. Then in the JOIN I reference tblAddress as A, and again wherever there's an A, it refers to that table. It really cuts down on the amount of typing when you're doing long complex queries and can mean the differene between a query exceeding the maximum number of characters.

Leslie

 

[Cordury]

Leslie,

Thanks for your help last week! Is there a simple way to save/convert the above query results as a table?

Again, I want to remove the address sub form and table and try and reduce the size of my db. It is close to 10 megs and it barely has any data in it or any complex queries.

Thanks,
Max

 

[lespaul]

I'm pretty sure that you can convert it to a Make Table query, but I'm not really sure how to go about it. I don't really USE access that much.

Sorry, you may want to search this forum for 'Make Table' and see if you can figure out how it's done!

Leslie

 

[Cordury]

Leslie,
It was as simple as you said, just change the query type and rename it.

I guess I was trying to make it more difficult than necessary.

Thanks again

 

[lespaul]

Glad I got it right! Sometimes I'm afraid to answer cuz I'm just not sure!!

Leslie