Using Count If 1

[henp]

Hi,

I have the following tables:
Table Name = Table1
ClientID CustID Date Field
644/1234 123456 12/02/2003 1500
644/1234 123567 12/02/2003 1500
644/1234 123456 12/02/2003 1500
644/1234 123567 12/02/2003 1600

Table Name = Table2
ClientID CustID Date Field
644/1234 123456 12/02/2003 1600
644/1234 123567 12/02/2003 1600
644/1234 123456 12/02/2003 1600
644/1234 123567 12/02/2003 1500

I have grouped my report by ClientID and date and would like to add an expression that would give me the following results.

Table1
1500 = 3
1600 = 1

Table2
1500 = 1
1600 = 3

Please help,

Thanks,

Kirsten.

 

[henp]

Same thing it gave me a data type mismatch in criteria expression error. I just copied and pasted the above quirie.

Is there something else I can try?

Kirsten.

 

[dhookom]

I RecordTime ever Null?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[dhookom]

Should have been "Is RecordTime ever Null?"

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[henp]

Yes.


RecordTime does sometimes have no date information in the cell. Would this make a difference?

Thanks,

Kirsten.

 

[dhookom]

You (or I) could test to find out if it makes a difference. Why don't you test it and report back.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[henp]

Not sure how to test this (duh!!). Tried to delete records and the file is read only. Tried to change read only- cannot. About a third of the records do not contain RecordTime data. Can you help me?

Thanks for your assistance

Kirsten.

 

[dhookom]

Set a criteria on the query:
WHERE RecordTime is Not Null

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[henp]

Duane it works better although I'm still not getting the results I need. Below is the querie and below that some of the results from it. You'll see that balance bracelet works as I would like it to, but body dome is still not grouping correctly giving me duplicate 1310's and so on.

SELECT [Last Called State - History Table & HistCampaign Table].CampaignID, [Last Called State - History Table & HistCampaign Table].LastCalledState AS CountOfLastCalledState1, Count([Last Called State - History Table & HistCampaign Table].LastCalledState) AS CountOfLastCalledState, DateValue([Last Called State - History Table & HistCampaign Table].RecordTime) AS Expr1
FROM [Last Called State - History Table & HistCampaign Table]
WHERE ((([Last Called State - History Table & HistCampaign Table].RecordTime) Is Not Null))
GROUP BY [Last Called State - History Table & HistCampaign Table].CampaignID, [Last Called State - History Table & HistCampaign Table].LastCalledState, DateValue([Last Called State - History Table & HistCampaign Table].RecordTime), [Last Called State - History Table & HistCampaign Table].RecordTime
HAVING ((([Last Called State - History Table & HistCampaign Table].RecordTime) Between [Type the beginning date:] And [Type the ending date:]));

CampaignID CountOfLastCalledState1 CountOfLastCalledState Expr1003
Balance Bracelet 1010 2 12/8/2003
Balance Bracelet 1310 3 12/8/2003
Balance Bracelet 1320 2 12/8/2003
Balance Bracelet 1340 6 12/8/2003
Balance Bracelet 1500 10 12/8/2003
Balance Bracelet 1710 1 12/8/2003
Balance Bracelet 1800 1 12/8/2003
Balance Bracelet 1810 2 12/8/2003
Body Dome 1010 1 12/8/2003
Body Dome 1210 2 12/8/2003
Body Dome 1310 1 12/8/2003
Body Dome 1310 3 12/8/2003
Body Dome 1320 1 12/8/2003
Body Dome 1320 4 12/8/2003
Body Dome 1340 1 12/8/2003
Body Dome 1500 6 12/8/2003
Body Dome 1710 2 12/8/2003
Body Dome 1810 3 12/8/2003


Thanks Duane

Kirsten.

 

[dhookom]

Check your current Group By and my suggested Group By from Jan 9th.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[henp]

Brilliant Duane,

Thanks soooooo much for your help.

Thank you,

Kirsten.