Count Per Day 1

[EnergyTed]

Hi

I am very frustrated and need some help/advice!

I have a ownership table with the following fields -

Company Name
Ownership Start Date
Item

When a company creates a new item a row is created in the table.
When a company transfers an item a row is created in the table with a new Ownership Start Date.

I am trying to calculate a Count Per Day as per the below example -

Date, Company1, Company2, Etc
01/01/2011, 10, 9, 3


A transfer takes place from Company1 to Company2

Date, Company1, Company2, Etc
02/01/2011, 9, 10, 3

Please let me know if you require any further information?

 

[dhookom]

What does your actual data in your table look like? Where does "10, 9, 3" and "9. 10. 3" come from?

What is the table name?

Duane
Hook'D on Access
MS Access MVP

 

[EnergyTed]

Hi Duane

The 10, 9, 3 and examples of the running count on that particular date that I am trying to achieve.

An example of the actual data would be

Company Name, Ownership Start Date, Item
CompA, 01/01/2011, Item1
CompA, 01/01/2011, Item2
CompA, 01/01/2011, Item3
CompA, 01/01/2011, Item4
CompA, 01/01/2011, Item5
CompA, 01/01/2011, Item6
CompA, 01/01/2011, Item7
CompA, 01/01/2011, Item8
CompA, 01/01/2011, Item9
CompA, 01/01/2011, Item10
CompB, 01/01/2011, Item11
CompB, 01/01/2011, Item12
CompB, 01/01/2011, Item13
CompB, 01/01/2011, Item14
CompB, 01/01/2011, Item15
CompB, 01/01/2011, Item16
CompB, 01/01/2011, Item17
CompB, 01/01/2011, Item18
CompC, 01/01/2011, Item19
CompC, 01/01/2011, Item20
CompC, 01/01/2011, Item21
CompB, 02/01/2011, Item1

The table is called Ownership.

Cheers
Ted

 

[dhookom]

Apparently you want a count of records by date and company. This looks like a basic crosstab query with the [Ownership Start Date] as a Row Heading, [Company Name] as the Column Heading, and Count(item) as the Value.

Duane
Hook'D on Access
MS Access MVP

 

[EnergyTed]

No, because of the way the table is stuctured, the results from a crosstab query only show ever increasing numbers, whereas in reality one Company has increased and another has decreased their Item count? When CompB takes ownership of Item1, the running count for that date should reflect that, i.e. CompA 9, CompB10.

Ted

 

[dhookom]

Based on your data, you can create the following queries:

qselOwnershipStartEnd

SELECT Ownership.[Company Name], Ownership.[Ownership Start Date], Ownership.Item, Nz(Ownership_1.[Ownership Start Date] - 1, date()) AS OwnEndDate
FROM Ownership LEFT JOIN Ownership AS Ownership_1 ON (Ownership.Item = Ownership_1.Item) AND (Ownership.[Ownership Start Date] < Ownership_1.[Ownership Start Date]) AND (Ownership.[Company Name] <> Ownership_1.[Company Name]);

qgrpOwnershipDates

SELECT Ownership.[Ownership Start Date] AS OwnDate
FROM Ownership
GROUP BY Ownership.[Ownership Start Date];

qxtbOwnershipDates

TRANSFORM Count(qselOwnershipStartEnd.Item) AS CountOfItem
SELECT qgrpOwnershipDates.OwnDate
FROM qselOwnershipStartEnd, qgrpOwnershipDates
WHERE (((qgrpOwnershipDates.OwnDate) Between [Ownership Start Date] And [OwnEndDate]))
GROUP BY qgrpOwnershipDates.OwnDate
PIVOT qselOwnershipStartEnd.[Company Name];

[tt]
OwnDate CompA CompB CompC
1/1/2011 10 8 3
1/2/2011 9 9 3
[/tt]

I'm not sure how this will work with multiple ownership changes. The first query may need to be changed to use DMin().

Duane
Hook'D on Access
MS Access MVP

 

[EnergyTed]

Hi Duane

I haven't had time to test the queries but on first look they seem to do exactly what I am looking for.

I will report back when I have had time to test them, especially the possible change to the Dmin() as I know ownership has changed multiple times and this will need to be tested.

Thanks for your time and patience.


Ted.

 

[EnergyTed]

Hi Duane

I finally have some time to experiment and test the queries above.

I am not sure where the Dmin() function should be exchanged too allow for multiple ownerships changes?

Is it

Nz(Ownership_1.[Ownership Start Date] - 1, Dmin()) AS OwnEndDate
Or
Nz(Dmin(Ownership_1.[Ownership Start Date], date())) AS OwnEndDate
Or
Dmin(Nz(Ownership_1.[Ownership Start Date] - 1, date())) AS OwnEndDate

Or am I completely wrong?

Yours in hope

Ted

 

[dhookom]

Please provide raw data that better illustrates "multiple ownerships changes" and then how these records would appear in your final query.

Duane
Hook'D on Access
MS Access MVP

 

[EnergyTed]

Hi Duane

An example of actual data would be -

Company Name, Ownership Start Date, Item
CompA, 01/01/2011, Item1
CompA, 01/01/2011, Item2
CompA, 01/01/2011, Item3
CompA, 01/01/2011, Item4
CompA, 01/01/2011, Item5
CompA, 01/01/2011, Item6
CompA, 01/01/2011, Item7
CompA, 01/01/2011, Item8
CompA, 01/01/2011, Item9
CompA, 01/01/2011, Item10
CompB, 01/01/2011, Item11
CompB, 01/01/2011, Item12
CompB, 01/01/2011, Item13
CompB, 01/01/2011, Item14
CompB, 01/01/2011, Item15
CompB, 01/01/2011, Item16
CompB, 01/01/2011, Item17
CompB, 01/01/2011, Item18
CompC, 01/01/2011, Item19
CompC, 01/01/2011, Item20
CompC, 01/01/2011, Item21
CompB, 02/01/2011, Item1
CompA, 03/01/2011, Item1
CompB, 04/01/2011, Item1

And the result would be -

OwnDate CompA CompB CompC
01/01/2011 10 8 3
02/01/2011 9 9 3
03/01/2011 10 8 3
04/01/2011 9 9 3

Kind Regards

Ted

 

[dhookom]

Did you try the suggested queries?
They seem to match your desired result.
[tt]
OwnDate CompA CompB CompC
1/1/2011 11 8 3
1/2/2011 10 9 3
1/3/2011 11 8 3
1/4/2011 9 9 3
[/tt]

Duane
Hook'D on Access
MS Access MVP