Selective Group By SQL Query on Access Jet DB
Hi,
I am trying to determine the query to group by a column, but to only group in batches until that column has changed. Easier to explain;
I have two tables;
I'm currently using this query;
Which gets me;
But what I want to see is;
The Database is Access/Jet4.0. Im using .Net, so it's oledb access etc. Unfortunately I don't have any control over the design of the database.
Can anyone help me get the query right?
Thanks
I am trying to determine the query to group by a column, but to only group in batches until that column has changed. Easier to explain;
I have two tables;
sgtbl_PayValuesHistory
-------------------------------------------
Reference, HistoryRef, NICat, NICEarnsToLEL
100 , 100/1 , A , 50
100 , 100/2 , A , 28
100 , 100/3 , C , 150
100 , 100/4 , D , 14
100 , 100/5 , A , 99
100 , 100/6 , A , 135 sgtbl_UpdateStatus
------------------------
HistoryRef, TaxYearStart
100/1 , 2012
100/2 , 2012
100/3 , 2012
100/4 , 2012
100/5 , 2012
100/6 , 2012 I'm currently using this query;
SELECT sgtbl_PayValuesHistory.Reference, sgtbl_PayValuesHistory.NICat, SUM(sgtbl_PayValuesHistory.NICEarnsToLEL) AS SumOfNICEarnsToLEL
FROM (sgtbl_PayValuesHistory INNER JOIN
sgtbl_UpdateStatus ON sgtbl_PayValuesHistory.HistoryRef = sgtbl_UpdateStatus.HistoryRef)
WHERE (sgtbl_UpdateStatus.TaxYearStart = 2012)
GROUP BY sgtbl_PayValuesHistory.Reference, sgtbl_PayValuesHistory.NICat
HAVING (sgtbl_PayValuesHistory.Reference = 100)Which gets me;
Reference, NICat, SumOfNICEarnsToLEL
100 , A , 312
100 , C , 150
100 , D , 14 But what I want to see is;
Reference, NICat, SumOfNICEarnsToLEL
100 , A , 78
100 , C , 150
100 , D , 14
100 , A , 234 The Database is Access/Jet4.0. Im using .Net, so it's oledb access etc. Unfortunately I don't have any control over the design of the database.
Can anyone help me get the query right?
Thanks
Why should the values for Reference 100 and NICCat A "not" be added?
Reference, NICat, SumOfNICEarnsToLEL
100 , A , 78 <<-- This value
100 , C , 150
100 , D , 14
100 , A , 234 <<-- This value
Reference, NICat, SumOfNICEarnsToLEL
100 , A , 78 <<-- This value
100 , C , 150
100 , D , 14
100 , A , 234 <<-- This value
ASKER
I don’t like it, but unfortunately that’s what I need to retrieve.
They represent an employee’s NIcategory history. Effectively I need a total for each Nicategory they have been in, in the order they were in them. Unfortunately it is possible to move from one category to another and then back again.
They represent an employee’s NIcategory history. Effectively I need a total for each Nicategory they have been in, in the order they were in them. Unfortunately it is possible to move from one category to another and then back again.
Let's modify a bit your first table to simplify:
How do you know which ones go together and which ones are a "separate" group?
sgtbl_PayValuesHistory
-------------------------------------------
Reference, HistoryRef, NICat, NICEarnsToLEL
100 , 100/1 , A , 50
100 , 100/2 , A , 28
100 , 100/5 , A , 99
100 , 100/6 , A , 135 How do you know which ones go together and which ones are a "separate" group?
They represent an employee’s NIcategory historySo shouldn't each NICategory be associated with a date? So basically shouldn't your resultset look like this?
Datecolumn Reference, NICat, SumOfNICEarnsToLEL
01/01/2012 100 , A , 78
01/01/2012 100 , C , 150
01/01/2012 100 , D , 14
04/05/2012 100 , A , 234 If so, adding the date column to the group by should do the trick.
SELECT sgtbl_PayValuesHistory.Reference, sgtbl_PayValuesHistory.NICat, SUM(sgtbl_PayValuesHistory.NICEarnsToLEL) AS SumOfNICEarnsToLEL
FROM (sgtbl_PayValuesHistory INNER JOIN
sgtbl_UpdateStatus ON sgtbl_PayValuesHistory.HistoryRef = sgtbl_UpdateStatus.HistoryRef)
WHERE (sgtbl_UpdateStatus.TaxYearStart = 2012)
GROUP BY yourtable.yourdatecolumn, sgtbl_PayValuesHistory.Reference, sgtbl_PayValuesHistory.NICat
HAVING (sgtbl_PayValuesHistory.Reference = 100)
From what I understand, the dates can change for the same NICategory.
Let's see if the author clarifies that for us.
Let's see if the author clarifies that for us.
ASKER
@LIONKING;
These would all be in one group (one record returned) as the indicator to create a new group is that fact the NICat has changed.
@Ralmada;
The dates I have align to the HistoryRef i.e. there is a different date for each entry in the sgtbl_PayValuesHistory table. So if i understand correctly, addingh a group by date is the same as adding a group by historyId, which in effect just returns all the items.
How do you know which ones go together and which ones are a "separate" group?
sgtbl_PayValuesHistory
-------------------------------------------
Reference, HistoryRef, NICat, NICEarnsToLEL
100 , 100/1 , A , 50
100 , 100/2 , A , 28
100 , 100/5 , A , 99
100 , 100/6 , A , 135 These would all be in one group (one record returned) as the indicator to create a new group is that fact the NICat has changed.
@Ralmada;
So shouldn't each NICategory be associated with a date? So basically your resultset shouldn't look like this?
The dates I have align to the HistoryRef i.e. there is a different date for each entry in the sgtbl_PayValuesHistory table. So if i understand correctly, addingh a group by date is the same as adding a group by historyId, which in effect just returns all the items.
ASKER
Thanks for both of your help, by the way...
These would all be in one group (one record returned) as the indicator to create a new group is that fact the NICat has changed.
If this is true, then your initial query is correct.
You're getting one grouped record for those 4 individual records...
Unless... All the records have to be ordered by Reference, then historyRef and whenever the NICat changes a new "group" should be created... Is this correct???
ASKER
Unless... All the records have to be ordered by Reference, then historyRef and whenever the NICat changes a new "group" should be created... Is this correct???
BINGO!... :)
Yeah thats the case, any thoughts on how I'd do that?
question, other than HistoryRef, do you have an primary key ID column (autonumber)
ASKER
The PrimaryKeys for the UpdateStatus table are; Reference (the employeees No.), PeriodStart, PeriodEnd. The last two are dates, but none are set to auto as far as I can see.
The PrimaryKey for the PayValuesHistory table is HistoryRef.
So No, i dont think so...
The PrimaryKey for the PayValuesHistory table is HistoryRef.
So No, i dont think so...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
When you create a stagetable like this, is it actually creating the table in the DB? ie modifiying the schema, or is it just in memory?
Also, although its an access/jet DB its MS Access is not the front end, so VBA won't work. Its .NET though so I could manipulate the results afterwards. The only issue with this is that it breaks the design of my classes, thats why I was hoping to find an SQL query capable of returning these results...
Also, although its an access/jet DB its MS Access is not the front end, so VBA won't work. Its .NET though so I could manipulate the results afterwards. The only issue with this is that it breaks the design of my classes, thats why I was hoping to find an SQL query capable of returning these results...
creating the table in the DB? ie modifiying the schema, or is it just in memory?
you have to create the table in Access first. The above only copies the data over. The reason being is, with the existing columns is not easy to identity the next record to see if there's a change in the category
MS Access is not the front end, so VBA won't work.
VBA will work, because the idea is to create a function that you could use in your query so at the back end, not at the front end.
ASKER
ah right, sorry, what I was trying to say is that although it's an Access DB, I dont use Access with the DB.
I am unable to change the Schema etc of the DB, only its values.
I am unable to change the Schema etc of the DB, only its values.
ASKER
Found some similar requirements, but not sure how to apply these to my scenario or whether they are really appropriate;
http://www.sql-server-perf
Http://social.msdn.microso
http://www.sql-server-perf
Http://social.msdn.microso
I don't think those are exactly the same scenario. Also those are SQL Server examples, not Access.
The way I see this is, unless you move this data to a temporary database where you can add some VBA and change the table definitions, there's not much you can do with a simple SQL statement.
The way I see this is, unless you move this data to a temporary database where you can add some VBA and change the table definitions, there's not much you can do with a simple SQL statement.
ASKER
As suggested looks like it can't be done. Oh well, will have to be done in code instead.
Thanks for your help.
Thanks for your help.
ASKER