Criteria Help - WHERE, IF, AND, or OR? 1

[RonnieDobbs]

Hello,

I have imported an excel spreadsheet into Access and I am looking to create reports based on this one sheet, (one table in Access)

Data is results from a survey. So basically people are asked who much they know about something, 1 is expert, 2 average, 3 need help. I want to get results based on what Branch they are reporting from, and what instances they marked a 3 for.

Example row:

BSONUM Name Med Supp MMA PDP LTC

1111 Steve 1 3 2 3



I have started with a parameter query of:

SELECT [BSM GS].BSONUM, [BSM GS].Name, [BSM GS].MedSup, [BSM GS].MMA, [BSM GS].PDP, [BSM GS].LTC
FROM [BSM GS]
WHERE ((([BSM GS].BSONUM)=[enter bso #]))...

How do i query to only get # 3 respnses?

thanks,

Ron

 

[PHV]

First, normalize your data.
Create a query named, say, qryBSMGS:
SELECT BSONUM, Name, 'MedSup' AS Instance, MedSup AS Response FROM [BSM GS]
UNION ALL SELECT BSONUM, Name, 'MMA', MMA FROM [BSM GS]
UNION ALL SELECT BSONUM, Name, 'PDP', PDP FROM [BSM GS]
UNION ALL SELECT BSONUM, Name, 'LTC', LTC FROM [BSM GS]

And now your parametized query:
SELECT *
FROM qryBSMGS
WHERE Response=3 AND BSONUM=[enter bso #]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RonnieDobbs]

Thanks PHV, that makes sense and works great!

Ron