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.

 

[dhookom]

Try create a union query with SQL like:

Select Field, Count(Field) as NumOf
FROM Table1
GROUP BY Field
UNION
Select Field, Count(Field)
FROM Table2
GROUP BY Field;

BTW: I hope you don't have field names like Date (a function) and Field (an object type).

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

 

[henp]

I'm not sure how to use the above querie is there a simple expression such =countif([field]="1500&quot i know I would have to put a different expression for each # but I only have 5 or six different ones.

Please help and thanks for the above.

I will use the above when I have more time to figure out how to use it.

Thanks again

Kirsten

 

[dhookom]

To use my suggestion, open a new query and view the sql. Paste my suggested syntax into the query window and change the field/table names to match your objects.

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

 

[henp]

I tried the above but it gave me a syntax error in FROM clause.

Below is what I did - can you help!!

Select LastCalledState, Count(LastCalledState) as NumOf
FROM Last Called State - History Table & HistCampaign Table
GROUP BY LastCalledState
UNION Select LastCalledState, Count(LastCalledState)
FROM Last Called State-Hist & Camp Table Rec in the Camp Table only
GROUP BY LastCalledState;

Thanks

Kirsten.

 

[dhookom]

That's the penalty for not using a naming convention that excludes spaces in object names. Try:
Select LastCalledState, Count(LastCalledState) as NumOf
FROM [Last Called State - History Table & HistCampaign Table]
GROUP BY LastCalledState
UNION Select LastCalledState, Count(LastCalledState)
FROM [Last Called State-Hist & Camp Table Rec in the Camp Table only]
GROUP BY LastCalledState;

My table names might be something like:
tblLastCallStateHistAndCmpgn
no spaces or punctuation and always prefixed with "tbl" for tables. Tony Toews has a good reference on naming conventions at

http://www.granite.ab.ca/access/tablefieldnaming.htm.

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

 

[henp]

dhookom,

Thanks for the above it worked perfectly. Is there any way to add additional grouping level. I have a field that contains a date & time called RecordTime & would like to add this as an additional grouping level. The other thing is I would like to group by just the date not the date & time - so the whole thing would go like this :

Date 12012003 LastCalledState 1500 Amount 2
1600 3
Date 12022003 1500 1

and so on.

Please help

Thanks,

Kirsten.

 

[dhookom]

Try:
Select LastCalledState, DateValue(RecordTime) as CallDate, Count(LastCalledState) as NumOf
FROM [Last Called State - History Table & HistCampaign Table]
GROUP BY LastCalledState, DateValue(RecordTime)
UNION ...etc

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

 

[henp]

dhookom,

Please forgive my ignorance - the last sql you gave me sent back the following error Data Type Mismatch in Criteria Expression. Is there a reason for this or am I not explaining myself correctly. Also, can you give me any tips on how to add even more grouping levels.

Thanks for your assistance and patience,

Kirsten.

 

[dhookom]

When a query gives you an error, please paste the entire sql view into a reply so we don't have to make a WAG as to your issue.
To troubleshoot, press [Ctrl]+G and enter
? DateValue(Now())
and press enter. Do you get an error message?


Regarding grouping levels, start a new query and "play" with the grouping to see what happens.

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

 

[henp]

Duane,

I tried to troubleshoot the problem using Ctrl G and there was no an error message.

Sorry about having to bat this back and forth.

Sql

SELECT LastCalledState, DateValue(RecordTime) AS CallDate, Count(LastCalledState) AS NumOf
FROM [Last Called State - History Table & HistCampaign Table]
GROUP BY LastCalledState, DateValue(RecordTime)
UNION Select LastCalledState, DateValue(RecordTime) AS CallDate, Count(LastCalledState)
FROM [Last Call State-Hist & Camp Table Rec in the Camp Table only]
GROUP BY LastCalledState, DateValue(RecordTime);

Field Names
Campaign ID
CustomerID
RecordTime - 12/01/2002 12:01:15 PM
LastCalledState

I would like to group by

CampaignID
RecordTime - Date Only

I would like the quirie created to show eg

CampaignID/RecordTime/LastCalledState/# of
597/12/01/2003/1500/3
597/12/01/2003/1600/3
597/12/02/2003/1500/2
683/12/03/2003/1600/4

and so on......

Sory I did not make myself clear.

Thanks for your help it is greatly appreciated.

Your the best!!!

Kirsten.

 

[dhookom]

Is your source a table or query? What is its name? How can you show LastCalledState since it is not in the fields you want to group by?

All you normally have to do is create a query with the source table/query and click the View Totals icon. Then set the Totals to either Group By or Sum or Max or whatever.

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

 

[henp]

That works great!! although I still have the problem with the RecordTime Date value. I would like it to give me the results based on just the date not the date & time that the RecordTime column contains. One last time can you help. Here's what I have:

SELECT [Last Called State - History Table & HistCampaign Table].CampaignID, [Last Called State - History Table & HistCampaign Table].LastCalledState, Count([Last Called State - History Table & HistCampaign Table].LastCalledState) AS CountOfLastCalledState, [Last Called State - History Table & HistCampaign Table].RecordTime
FROM [Last Called State - History Table & HistCampaign Table]
GROUP BY [Last Called State - History Table & HistCampaign Table].CampaignID, [Last Called State - History Table & HistCampaign Table].LastCalledState, [Last Called State - History Table & HistCampaign Table].RecordTime
ORDER BY [Last Called State - History Table & HistCampaign Table].CampaignID, [Last Called State - History Table & HistCampaign Table].RecordTime;

Thanks,
Kirsten.

 

[dhookom]

Use the DateValue() function around your datetime field.
RecordDate: DateValue([Last Called State - History Table & HistCampaign Table].RecordTime)


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

 

[henp]

Duane,

I did the above but it still seems to be giving me results on Date & Time. Can you help?

SELECT [Last Called State - History Table & HistCampaign Table].CampaignID, Count([Last Called State - History Table & HistCampaign Table].LastCalledState) AS CountOfLastCalledState1, DateValue([Last Called State - History Table & HistCampaign Table].RecordTime) AS Expr1, [Last Called State - History Table & HistCampaign Table].LastCalledState
FROM [Last Called State - History Table & HistCampaign Table]
GROUP BY [Last Called State - History Table & HistCampaign Table].CampaignID, [Last Called State - History Table & HistCampaign Table].LastCalledState, [Last Called State - History Table & HistCampaign Table].RecordTime
ORDER BY [Last Called State - History Table & HistCampaign Table].CampaignID, [Last Called State - History Table & HistCampaign Table].RecordTime;

Thanks,

Kirsten.

 

[dhookom]

This is your current Group By

GROUP BY [Last Called State - History Table & HistCampaign Table].CampaignID, [Last Called State - History Table & HistCampaign Table].LastCalledState, [Last Called State - History Table & HistCampaign Table].RecordTime
It should be:
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)

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

 

[henp]

Duane,

Sorry can't seem to get this to work. It's giving me the error message "you tried to execute a query that not include a specified expression '[Last Called State - History and HistCampaign Table].RecordTime' as part of an aggregate function.

SELECT [Last Called State - History Table & HistCampaign Table].CampaignID, Count([Last Called State - History Table & HistCampaign Table].LastCalledState) AS CountOfLastCalledState1, DateValue([Last Called State - History Table & HistCampaign Table].RecordTime) AS Expr1, [Last Called State - History Table & HistCampaign Table].LastCalledState
FROM [Last Called State - History Table & HistCampaign Table]
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)
ORDER BY [Last Called State - History Table & HistCampaign Table].CampaignID, [Last Called State - History Table & HistCampaign Table].RecordTime;

I've played with it until I can't see the wood for the trees. Please help.

Thanks,

Kirsten.

 

[dhookom]

Try the following where each reference to the datetime field is included in the DateValue() function.

SELECT [Last Called State - History Table & HistCampaign Table].CampaignID, Count([Last Called State - History Table & HistCampaign Table].LastCalledState) AS CountOfLastCalledState1, DateValue([Last Called State - History Table & HistCampaign Table].RecordTime) AS Expr1, [Last Called State - History Table & HistCampaign Table].LastCalledState
FROM [Last Called State - History Table & HistCampaign Table]
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)
ORDER BY [Last Called State - History Table & HistCampaign Table].CampaignID, DateValue([Last Called State - History Table & HistCampaign Table].RecordTime);

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

 

[henp]

Duane,

That gave me a data type mismatch in criteria expression error.


Thanks


Kirsten.

 

[dhookom]

Get rid of all the Sorting in the query.
SELECT [Last Called State - History Table & HistCampaign Table].CampaignID, Count([Last Called State - History Table & HistCampaign Table].LastCalledState) AS CountOfLastCalledState1, DateValue([Last Called State - History Table & HistCampaign Table].RecordTime) AS Expr1, [Last Called State - History Table & HistCampaign Table].LastCalledState
FROM [Last Called State - History Table & HistCampaign Table]
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);


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