asked on 7/21/2008

Query not returning all results

Hey Experts!!
I have a query which one expert already helped me with and everything works great so long as they have records in one of the tables. However, I know realize that I need this query to show EVERYTHING from ALL the tables I specify. As of right now it is ONLY returning records that have records in AED_master.

Tables:
Client - ACT_No is Key, linked to ACT_Master.ACT_no and AED_Master.ACT_no
AED_Master - AED_No is key however ACT_no is linked to Client.ACT_no
ACT_Master - ACT_no is key not linked any more.

So let's say I call Client.ACT_No = 1, there are no records in the AED_Master table, but there are contents in the Client and ACT_Master tables, still no results.

Can anyone help!? Also, let me know if you need the database and I will compact and send it.

Thanks in advance!

SELECT DISTINCT (AED_Master.ACT_no) AS Expr1, Client.ACT_no, *
FROM ((ACT_Master RIGHT JOIN Client ON ACT_Master.ACT_No = Client.ACT_no) 
INNER JOIN AED_master ON Client.ACT_no = AED_master.ACT_No) 
INNER JOIN Models ON AED_master.Model_ID = Models.Model_ID
WHERE (((Client.ACT_no)=MMColParam));

Open in new window

Microsoft Access ASP SQL

Last Comment

LZ1

7/21/2008

Guy Hengel [angelIII / a3]

7/21/2008

when a linked table has no results, but you want still some rows, use LEFT OUTER JOIN instead of INNER JOIN...

Giuseppe Pizzuto

7/21/2008

If you are using MS-SqlServer, use the FULL JOIN operator instead of the INNER JOIN.
If not, it could be longer: you should use subqueries.
Hope this helps.

LZ1

7/21/2008

ASKER

Angellll: I used LEFT OUTER JOIN, however it said it wasn't supported. So I used RIGHT OUTER JOIN and it still ended up returning no results.
gpizzuto: I am using ASP with an Access database. Can you give me an example of FULL JOIN syntax? (still relatively new to queries)

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

7/21/2008

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

LZ1

7/21/2008

ASKER

It's giving me a syntax error, but I cannot find it.

SOLUTION

Rick_Rickards

7/21/2008

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Rick_Rickards

7/21/2008

One note about my previous post. The WHERE clause...

WHERE (((Client.ACT_No)=MMColParam Or (Client.ACT_No) Is Null));

suggests that MMColParam is a variable or function but the syntax isn't quite right for either to be the case. If it is a field name it would be good to know what table it's from as that could impact the way the query is written.

LZ1

7/21/2008

ASKER

angelIII: I got it to work. The parenthesis were off. Thanks a million.
Rick Richards: The first query worked like a charm after throwing the asterisk into the Select statement. I didn't even realize that I could have thrown in a Null statement in there. Hindsite is always 20/20 though.
Thanks Experts!

LZ1

7/21/2008

ASKER

I did not wish to close this question. I hit "Accept multiple solutions" and it gave me this. There are 2 experts here who deserve points!

LZ1

7/21/2008

ASKER

Sorry about that original mess up guys with closing the question.
I appreciate all and any help from you guys and this site!!!!