Access Query

This is hard one to explain, so I attached a sample Access file.  Run qryFruit.  I don't want the second result to show up with "apple" in both columns.

I want the query result to look like the attached excel file.qryResult.xlsxDatabase5.accdb

Thanks,
Joel
Genius123Asked:
Who is Participating?
 
Saurabh Singh TeotiaCommented:
Looking at your query results then try this query...

select type as type1a ,'' as type1b
from tbla
union
select '' as type1a,type as type1b
from tblb
order by type1b
;

Open in new window


Saurabh...
0
 
Saurabh Singh TeotiaCommented:
Joel,

Need a bit of more understanding from you on this as you are saying you want to show blank against apple since id doesn't match...

Now if you see id doesn't match for orange in tablea and tableb as well since orange is 2 and then orange 6 which is for stawberry..dont you want to show blank their??

It will be really helpful if you help understand what you are trying to do here...

Saurabh...
0
 
PatHartmanCommented:
Your example still isn't clear.  I changed the query to group
SELECT tblA.Type, tblB.Type
FROM tblA LEFT JOIN tblB ON tblA.Type = tblB.Type
GROUP BY tblA.Type, tblB.Type;

This means that apple-apple will only show up once.

If what you are asking is for the first "apple" to be hidden when "apple" appears multiple times then the answer is  - You CANNOT do that in a query.  Use a report since this is a formatting issue and reports can be formatted.  It doesn't make logical sense to suppress "duplicate" values when running a query.  You can use the group by as I showed but that's it.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Genius123Author Commented:
Thanks for the reply.

Ignore the ID field.
May I should phrase this is different way.  If tblA has 6 records, and tblB has 5 records, I want 11qryResult.xlsx records to show in my query like the attached excel file.
0
 
Genius123Author Commented:
Thanks, that worked!
0
 
Saurabh Singh TeotiaCommented:
Also the above query will show apple one time..However if you want to show it twice then use this...

select type as type1a ,'' as type1b
from tbla
union all
select '' as type1a,type as type1b
from tblb
order by type1b
;

Open in new window


Saurabh...
0
 
PatHartmanCommented:
A join and a union are quite different queries.  The selected answer does not produce the results shown in the spreadsheet.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.