Received Dates Query

[dancarter]

Hi,
I want to run a query that returns only records where we haven't received 2 items for them from a possible 4. I can't seem to get my head around it!
At the moment I have:-
Recd Date 1 Recd Date 2 Recd Date 3 Recd Date 4
Is Null Is Null
Is Null Is Null
Is Null Is Null
Is Null Is Null
Is Null Is Null
Is Null Is Null


But obviously this isn't correct as it would return all records! I only want to show records, which haven't got 2 received dates.
I thought about counting the number of received dates in a record but wasn't sure how to code this up?

Any ideas gratefully received!
Thanks,
Dan

 

[cuttsy]

So you have a table with a field called Recd Date 1, a field called Recd Date 2, a field called Recd Date 3 and a field called Recd Date 4?

 

[ColinEssex]

If you have 4 received date fields in a table then you need to change it to having one received date. Then have each record on a new line. You setup is (at the moment) like a spreadsheet, it needs to be like a database.

Col

 

[cuttsy]

If you wan't to make this query easier and your database more like a database than a spreadsheet then do a search for normalization.

If you want to struggle on this way then you will need to write a monster of a WHERE clause with more brackets than a TV store.

You will need to take all of the possible pairs of dates:
Recd Date 1, Recd Date 2
Recd Date 1, Recd Date 3
Recd Date 1, Recd Date 4
Recd Date 2, Recd Date 1
Recd Date 2, Recd Date 3
Recd Date 2, Recd Date 4 .... etc

Then do

Where (your first pair are blank) or (your second pair are blank) or (.... and on and on and on.

I would highly recommend not doing this.

 

[dancarter]

I think my problem is that all of the data is held in spreadsheets at the moment and I've tried to mirror that to some extent.
The received dates are for references for employees but even if I had each reference in it's own table I still don't see how I could perform the same query any easier really?

I tried something like this expression but it had a syntax error:-
Satis: IIf(Or(AND(Ref1Date Received<>Null,Ref2Date Received<>Null),AND(Ref1Date Received<>Null,Ref3Date Received<>Null),AND(Ref1Date Received<>Null,Ref4Date Received<>Null),AND(Ref2Date Received<>Null,Ref3Date Received<>Null),AND(Ref2Date Received<>Null,Ref4Date Received<>Null),AND(Ref3Date Received<>Null,Ref4Date Received<>Null)), "Satisfactory", "Unsatisfactory")

Then I could query based on that expression?
Please excuse my hardened spreadsheet ways!

 

[cuttsy]

Forgive me but I am not even going to begin and pick my way through that syntax

If you had a tables set up like this: (I don't know what you are storing so I'll use stupid generic names)

YourTable([U]YourID[/U], YourFields)
YourRecieved([U][I]YourID[/I][/U], [U]RecievedDate[/U])

Then you can query

SELECT YourTable.*
FROM YourTable
WHERE YourTable.YourID IN
    (SELECT YourRecieved.YourID
     FROM YourRecieved
     GROUP BY YourRecieved.YourID
     HAVING Count(YourRecieved.YourID)<3);

That will give you all of your records with 2 or less recieved dates.

 

[ColinEssex]

all of the data is held in spreadsheets at the moment and I've tried to mirror that

Thats the problem. Read up about "normalisation", there's loads of it on these forums or in Access help.

Col

 

[EMP]

I thought about counting the number of received dates in a record but wasn't sure how to code this up?

You can add up the number of IsDates and change its sign from negative to positive:-

Num: -( IsDate([Recd Date 1])+IsDate([Recd Date 2])+IsDate([Recd Date 3])+IsDate([Recd Date 4]) )

^