I’m posting for help with a query in Access 2010 that doesn’t return the results I expect.
I have three tables:
- FIND (one FIND can have many RPs)
- RP (one RP can have many PSUs)
- PSU
For testing, I have the following records in the tables:
FIND Table
FIND 1
FIND 2
FIND 3
RP Table
RP 10 (Pending) – FIND 1
RP 11 (Closed) – FIND 2
RP 12 (Open) – FIND 3
PSU Table
PSU 01: FIND 1 – RP 10 (Pending) – PSU April
PSU 02: FIND 1 – RP 10 (Pending) – PSU May
PSU 03: FIND 1 – RP 10 (Pending) – PSU June
PSU 04: FIND 2 – RP 11 (Closed) – PSU May
PSU 05: FIND 2 – RP 11 (Closed) – PSU June
PSU 06: FIND 2 – RP 11 (Closed) – PSU July
PSU 07: FIND 2 – RP 11 (Closed) – PSU August
PSU 08: FIND 2 – RP 11 (Closed) – PSU September
PSU 09: FIND 3 – RP 12 (Open) – PSU August
PSU 10: FIND 3 – RP 12 (Open) – PSU September
PSU 11: FIND 3 – RP 12 (Open) – PSU October
What I want to accomplish in the query is to select the records that meet the following conditions and the output will contain fields from all three tables:
IF RP status = “Open” then select the PSU October record
IF RP status = “Closed” then select any PSU record
IF RP status = “Pending” then select any PSU record
The results I want to see from the query are the following three records:
FIND 1 – RP 10 (Pending) – PSU June (randomly chosen PSU is ok)
FIND 2 – RP 11 (Closed) – PSU August (randomly chosen PSU is ok)
FIND 3 – RP 12 (Open) – PSU October
The results I see from my query are the following two records:
FIND 2 – RP 11 (Closed) – PSU August (randomly chosen PSU is ok)
FIND 3 – RP 12 (Open) – PSU October
What is missing is: FIND 1 – RP 10 (Pending) – PSU June (randomly chosen PSU is ok)
I don’t know why my query doesn’t select that record
My query is set up with the following criteria in the RP status and PSU Date fields:
RP status = “Open” and PSU Date = [Enter Status Period:]
(This works and correctly selects the October record when I enter a status period of “October”)
RP status = “Closed” and PSU Date = (Select First([t_Progress Status].[Status Period]) from [t_Progress Status])
(This works and selects one PSU record from the set.)
RP status = “Pending” and PSU Date = (Select First([t_Progress Status].[Status Period]) from [t_Progress Status])
(This does NOT work and doesn’t return any PSU records from the set.)