Partition and Query

[termsig]

Hi,

I'm a new Access user and i'm having some small problems with pulling out the information from Microsoft Access database.

I need a breakdown of unique visits by specific time range.
Below function is working fine as it give me the breakdown by time unfortunately the duplicated visits are counted together.

One visit should be counted only one not twice as it's happening in below code.

I will be very appreciate for any clue or example how to achieve it.

SELECT Partition(Hour([time]),0,23,1) AS Range_Time, Count(orders.Visit) AS Visits, Sum(orders.qty) AS Total_Qty
FROM orders
GROUP BY Partition(Hour([time]),0,23,1);

 

[Andrzejek]

I would be very tempted to try:

SELECT [blue]DISTINCT[/blue] Partition(Hour([time]),0,23,1) AS Range_Time, 
Count(orders.Visit) AS Visits, Sum(orders.qty) AS Total_Qty
FROM orders
GROUP BY Partition(Hour([time]),0,23,1);

Have fun.

---- Andy

 

[termsig]

It's still not working as you have duplicated visits,

It's the information received from the query and as you can see we have two situation when this same visit is counted twice.


Range_Time Visits Total_Qty
6: 6 1 4
7: 7 1 9
11:11 2 4
13:13 2 8
16:16 1 1

In attached file you can see query 2 and 3 that get all information needed the problem is that i can't join them together.. every time i'm getting wrong results..

Many thanks,

 

[dhookom]

You might want to provide some additional information such as the records that appear twice. Some of us can't or won't open the file.

Duane
Hook'D on Access
MS Access MVP

 

[termsig]

The orders table is as below,

ID Visit Product Time Date_Visit Qty
1 A123 123 13:34:13 18/05/2011 3
2 A124 143 16:05:13 03/04/2011 1
3 A124 193 07:23:19 03/04/2011 9
4 A126 137 06:59:01 13/07/2011 4
5 A127 139 11:06:12 11/04/2011 3
6 A127 129 11:06:12 11/04/2011 1
7 A129 121 13:59:01 09/08/2011 5

The function from my first email is counting Visit A127 and A124 twice where it should be counting as 1 (one unique visit)
I can't change the table format as it's over 3 millions of records ...

I hope that this time it's more clear...

 

[dhookom]

I would first create a group by query like:

SELECT Visit, [Time],Date_Visit, Sum(Qty) As QtySum
FROM Orders
GROUP BY Visit, [Time],Date_Visit

Then base your partition query on the new group by query rather than the Orders table.


Duane
Hook'D on Access
MS Access MVP

 

[termsig]

Ok, but it will count "Visit" A124 as two separate visits where it's this same only with different times.

Sorry for the question but i'm pretty fresh with Access and i can't migrate to any proper SQL system.

 

[dhookom]

Access is a proper SQL system.

Which [time] do you want to count for visit A124? If it's one visit, I assume there should be one time. You can use MIN([Time]) and remove it from the GROUP BY clause.

SELECT Visit, Min([Time]) as TimeMin,Date_Visit, Sum(Qty) As QtySum
FROM Orders
GROUP BY Visit, Date_Visit

Duane
Hook'D on Access
MS Access MVP

 

[termsig]

I agree that Access is a SQL system but it's limited with some functions comparing to other software's.

I dont wan't time max or min what i need it's a breakdown of visits(count) and qty (sum). Unfortunately the PARTITION function can't remove duplicated visits before the count...

The main problem was that function is counting every visit by hour where it should exclude the duplicated ones.

 

[dhookom]

Please tell us based on the data records posted on 31 Aug 11 13:21, what exactly you would expect to see in the output.

Duane
Hook'D on Access
MS Access MVP