More Query Woes.

[PK2000]

I've been tasked with another query. This one seems quite complicated.
I have to find only entries that are standby with no callins.

My database layout, Date|Day of Week|Employee|Classification|Hourly Rate|Type|Hours|Hours Paid|Shift|Value|Time|Section|Notes|Mileage|Meals|

An example:
29-Mar-04| Fri | Lane, P | BTL | $18.00 |standby |
29-Mar-04| Fri | Lane, P | BTL | $18.00 |Callin |
03-Apr-04| Wed |Reid, J | ATA | $22.00 | Standby|
11-Apr-04| Wed |Jones, J | BTL | $18.00 |standby |
23-May-04| Sun | Smith, K | BTL | $18.00 |Standby |
23-May-04| Sun | Smith, K | BTL | $18.00 |Callin |
03-Jun-04| Tue |Green, M | ATA | $22.00 | Standby|

I've asked afew different people how this may be done and I keep getting told that its going to probably have to be done using SQL or some really complex querying. I need to eliminate/filter all the callins and any standby entries that would correspond with a callin entry. So that I'm only left with Standby entries that have no callins. A slight catch is that some of these people have multiple callins for a single standby. They could be on standby for a friday, and be called in 6 times during that day. All 6 of those callins along with the one standby would need to be eliminated.
Examples P.Lane and K.Smith. So that I'm left with J.Reid, J.Jones, and M.Green.

Where should I start? As you've probably noticed my knowledge of access it right up there with my ability to build wax wings and fly(nill).
Hope someone can help me out with this. To me it looks quite complicated, maybe its not though.
Thanks

 

[M8KWR]

seems quite straight forward.

you have the table that has all this information...

Create a query that display all the standby information, then create a query thats display all the callin information.

Use the unmatched Query Wizard, select the standby query first, then the callin query, match the name for now, then go to the design window.

In the design window, where you have a link between the 2 queries, you need to create some more, so this narrows down you list.

Create a new link between the date... Making sure you arrow on the link is the same as the other one that was created for you.

Then run the query... it should display a lot of information, so then you would have to build a parameter into the query so you only show the day or date range that you require.

Hope this helps....

Any issues let me know. And if you need any sql for this then please say.

 

[PK2000]

Well I have only one thing to say,

Thank you!
This worked perfectly it seems so obvious once its done and I sit and look at it.
Once again, Thank you very much!