Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 631
  • Last Modified:

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;
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     

Open in new window


sgtbl_UpdateStatus
------------------------
HistoryRef, TaxYearStart
   100/1  ,      2012   
   100/2  ,      2012   
   100/3  ,      2012   
   100/4  ,      2012   
   100/5  ,      2012   
   100/6  ,      2012   

Open in new window


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)

Open in new window


Which gets me;
Reference, NICat, SumOfNICEarnsToLEL
   100   ,   A  ,      312          
   100   ,   C  ,      150          
   100   ,   D  ,      14           

Open in new window


But what I want to see is;
Reference, NICat, SumOfNICEarnsToLEL
   100   ,   A  ,      78           
   100   ,   C  ,      150          
   100   ,   D  ,      14           
   100   ,   A  ,      234          

Open in new window


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
0
ITPOL
Asked:
ITPOL
  • 10
  • 5
  • 5
2 Solutions
 
ITPOLAuthor Commented:
Forgot to add;  there are a lot more columns in the source tables, but I simplified them for the example.  If it helps there are dates associated with each HistoryRef entry, but I couldnt see how these would help as my groupby wouldnt work anymore.
0
 
LIONKINGCommented:
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
0
 
ITPOLAuthor Commented:
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.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LIONKINGCommented:
Let's modify a bit your first table to simplify:

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     

Open in new window


How do you know which ones go together and which ones are a "separate" group?
0
 
ralmadaCommented:
They represent an employee’s NIcategory history

Open in new window


So 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           

Open in new window


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)

Open in new window

0
 
LIONKINGCommented:
From what I understand, the dates can change for the same NICategory.
Let's see if the author clarifies that for us.
0
 
ITPOLAuthor Commented:
@LIONKING;

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     

Open in new window


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.
0
 
ITPOLAuthor Commented:
Thanks for both of your help, by the way...
0
 
LIONKINGCommented:
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???
0
 
ITPOLAuthor Commented:
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?
0
 
ralmadaCommented:
question, other than HistoryRef, do you have an primary key ID column (autonumber)
0
 
ITPOLAuthor Commented:
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...
0
 
LIONKINGCommented:
I don't think you can do that in Access with one single query... You'd have to develop some logic to be able to correctly identify which set of rows belongs to one group.

The problem is that you can't group by the available columns because you want to create new groups on values that could be grouped together. So you'd have to differentiate which group of rows go together.
0
 
ralmadaCommented:
I think you will have to create a stage table with adding an autonumber column + the columns of your existing tables, then insert the result of this query

INSERT INTO StageTable
SELECT     sgtbl_PayValuesHistory.Reference, sgtbl_PayValuesHistory.NICat, sgtbl_PayValuesHistory.NICEarnsToLEL
FROM         (sgtbl_PayValuesHistory INNER JOIN
                      sgtbl_UpdateStatus ON sgtbl_PayValuesHistory.HistoryRef = sgtbl_UpdateStatus.HistoryRef)
WHERE     (sgtbl_UpdateStatus.TaxYearStart = 2012)
ORDeR BY Reference, HistoryRef

Open in new window


Then you could use VBA to create a function that will group the NiCat as you require. Let me know if this is something you are interested in pursuing, so that I can provide guidance on the function
0
 
ITPOLAuthor Commented:
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...
0
 
ralmadaCommented:
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.
0
 
ITPOLAuthor Commented:
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.
0
 
ITPOLAuthor Commented:
0
 
ralmadaCommented:
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.
0
 
ITPOLAuthor Commented:
As suggested looks like it can't be done. Oh well, will have to be done in code instead.

Thanks for your help.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 10
  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now