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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.