Multiple counts per date 1

[Daps89]

I need to create a report that displays multiple counts so the end result looks something like:

Date Type1 Type 2 Type3 A B ........
17/3 1 3 2 2 4
18/3 2 1 5 5 6

The data comes from tblVesselType and Type1,Type2,Type3,Type4are in one drop down and A, B, C are in another.

So basically I want a report to show for each day, how many times the above options were selected.

At the moment, using the following quiery I only get a count of Type1, 2 and 3.
However I want a count of each type and a, b or c for each day:

SELECT tblVesselType.TypeOfVessel, Count(tblVessel.VesselId) AS CountOfVesselId
FROM tblVesselType INNER JOIN tblVessel ON tblVesselType.VesselTypeId = tblVessel.VesselType
GROUP BY tblVesselType.TypeOfVessel
HAVING (((tblVesselType.TypeOfVessel)="Type1" Or (tblVesselType.TypeOfVessel)="Type2" Or (tblVesselType.TypeOfVessel)="Type3"));

Does anyone know the best way to do multiple counts per date?

 

[PWise]

look @ Pivot table queires

 

[Daps89]

I hadn't thought of that, but after a quick it seems I can only do counts as if the two different criteria are linked.

Rather than each criteria being a sub-criteria, I want them all along the top of the report as separate items.

Does that make sense and can that be done using the pivot table?

 

[dhookom]

You can create a union query first:

SELECT YourDateField, YourTypeField as ColHead
FROM [YourSourceTables]
WHERE [Your Where Clause]
UNION ALL
SELECT YourDateField, YourABCField
FROM [YourSourceTables]
WHERE [Your Where Clause];

Then create a crosstab query with YourDateField as the Row Heading, ColHead as the Column Heading, and Count of ColHead as the Value.

Duane
Hook'D on Access
MS Access MVP

 

[Daps89]

The below code gives the error "is not a valid name name

SELECT TodayDate, VesselType as ColHead
FROM [tblVessel]
WHERE [tblVesselType.TypeOfVessel="Type1" Or tblVesselType.TypeOfVessel="Type2" Or tblVesselType.TypeOfVessel="Type3"]
UNION ALL
SELECT TodayDate, Logged
FROM [tblVessel]
WHERE [tblIncidentIniciator.LoggedBy="A" Or tblIncidentIniciator.LoggedBy="B" Or tblIncidentIniciator.LoggedBy="C"];

Am I on the right lines?

 

[dhookom]

Remove some of the []s and simplify your where clauses:

SELECT TodayDate, VesselType as ColHead
FROM [tblVessel]
WHERE tblVesselType.TypeOfVessel IN ("Type1","Type2","Type3")
UNION ALL
SELECT TodayDate, Logged
FROM [tblVessel]
WHERE  tblIncidentIniciator.LoggedBy IN ("A","B","C");

Duane
Hook'D on Access
MS Access MVP

 

[Daps89]

I am not having much luck with the suggested union Query and when using the code I am prompted to enter parameter values for vesseltype and logged by.

I have no idea why that is happening but any suggestions would be much appreciated

 

[dhookom]

I just noticed you don't have tblIncidentIniciator in the FROM clause. You need to describe your tables and fields so we can help.

Duane
Hook'D on Access
MS Access MVP

 

[Daps89]

tblVessel contains the the details about the vessels, however tblVesselType and tblIncidentIniciator are tables that link to the table and are accessed through combo boxes.

tblIncidentIniciator contains An ID and the field LoggedBy, which contains the records "a", "b" or "c"

tblVesseltype contains an ID and the field TypeOfVessel, which contains the records "Type1","Type2","Type3"

tblVessel contains a date field and for each date I want a count of how many records were recorded, how many were "a", how many were "b"...How many were "type1"

 

[dhookom]

Please provide the SQL view of a query that joins these tables together outputting the necessary columns.


Duane
Hook'D on Access
MS Access MVP

 

[Daps89]

The joins on the tables may not have been correct, however I have put all of the data into the same table, in hope that the issue can be resolved quicker and to simplify the query.

Now all the data is stored in tblVesselID, which contains the fields VesselID, TodayDate, Logged, VesselType....

Logged has the Values "a","b","c" manually typed into the combo box
VesselType has the values "Type1", "Type2", "Type3" typed in the same way.

Is it possible to return a query so the output looks like:

Date Type1 Type 2 Type3 A B C
17/3 1 3 2 2 4 1
18/3 2 1 5 5 6 2

Where the column headings relate to a count of the number of instances the value has occurred on that date?

 

[dhookom]

Create your [quniLoggedType] union query first as I described earlier:

SELECT TodayDate, Logged as ColHead
FROM tblVesselID
UNION ALL
SELECT TodayDate, VesselType
FROM tblVesselID;

Then create a crosstab query with TodayDate as the Row Heading, ColHead as the Column Heading, and Count of ColHead as the value.

Duane
Hook'D on Access
MS Access MVP

 

[Daps89]

That almost gives me the perfect result, thank you for all your help.

One problem that it creates, is that the values of the counts are displayed on different lines.

For example if Type A is selected in 2 separate records, rather than the query displaying:
Type A
17/03/2011 2

It displays:

Type A
17/03/2011 1
17/03/2011 1

How can I make it appear as a total number per date rather than on a seperate line?

 

[dhookom]

You probably have a group by on a column that is not necessary. Can you please reply with your SQL view so someone can help you?

The other issue might be if you are storing the time with the date field. If so, you will need to use DateValue(TodayDate) to remove the time portion.

Duane
Hook'D on Access
MS Access MVP

 

[Daps89]

The missing "DateValue" appeared to be the problem. I now have enough information to work on the report.

Thank you for the help

 

[Daps89]

The report works well, when I want to display all records, however when I want to select a date range problems occur.

In the Crosstab query I input the following into the criteria in the date field

>=[Enter Start Date? DD/MM/YYYY] And <=[Enter End Date DD/MM/YYYY]

I also defined the parameters for that query.

The query runs ok when running directly from the query, however this doesn't work when I try to create a report.

I read that this often occurs when using cross tab queries and that the solution may be to define the column headings.

If that is the correct solution, how would I go about that?

Thanks

 

[dhookom]

If you need to define the column headings then open the properties dialog for the query and enter the column headings in the column headings property.

Duane
Hook'D on Access
MS Access MVP