Consolidating Tables In A Query 3

[RAxel]

I have four tables that keep track of payroll information for each respective quarter (1st through 4th). What I want to do, is be able to let the user select which quarters to consolidate.

For example, the user wants to see the payroll information for the 4th through 2nd quarter. I want them to be able to select the quarters in a form, and then click a button and a table is made that shows the 4th through 2nd quarter payroll information.

What I have right now is a form, with 4 checkboxes (each indicating a quarter) and a command button (named btnConsolidateQuarters). I've figured out how to get the button to open and run a query but I don't know how to get the query to select the correct quarters.

My question is, is it possible for the user to choose which quarters to consolidate (via checkboxes) and the query can be adapted to the user's selections, or do I have to manually write queries for every possible scenario? Thanks.

 

[rubbernilly]

I'm not sure why you would have 4 tables for the quarters, rather than a single table with a field that indicated the quarter, but that being the case...

Try a form-level variable:

Private vQuarters(4)

Then you have a function that fills the array:

Public Function FillQuarter(iQuarter as Integer, tbl as String)
vQuarter(iquarter) = tbl
End Function

Each Checkbox has the following code in the click event, modified by the number and the table:

Select Case Value
   Case 0 'unchecked
      FillQuarter 1,""
   Case -1 'checked
      FillQuarter 1,"Quarter1TableName"
End Select

Then your button would check the vQuarters array for the table to use as a source.

dim a as integer
dim strSQL as string

For a = 1 to 4 'the range of quarters
   if vQuarters(a) <> "" then
      strsql = "SELECT * INTO YourNewTableName " & _
               "FROM " & vquarters(a)
      docmd.RunSQL strsql
      exit for
   end if
Next a

For a = a to 4
   if vQuarters(a) <> "" then
      strsql = "INSERT INTO YourNewTableName " & _
               "SELECT * FROM " & vquarters(a)
      docmd.RunSQL strsql
   end if
Next a

Your first loop makes the table. Your second loop inserts into that table if it already exists. You might want to delete the table programatically before you run the first SQL statement, since that will prompt you that the table is about to be overwritten otherwise.

Alternately, you could run a "DELETE" SQL statement to delete all the records from the destination/consolidation table, and then only worry with a loop of INSERT SQL statements from your array. You would not be constantly deleting and recreating the table this way.

HTH

 

[MajP]

There are many ways to do this. If it makes sense for what you are doing, you could make a tabular form with the query data.

I assume you are using the following to open a query:
DoCmd.OpenQuery queryname[, view][, datamode]

The reason to make a form is that the form has a where condition argument:

DoCmd.OpenForm formname[, view][, filtername][, wherecondition][, datamode][, windowmode][, openargs]

The where condition is a sql where without the word "where"
ex
"intFiscalQuarter = " & me.txtBoxFiscalQquarter

You are going to have to build a function that determines the string. (I did not check this) Example:

If me.chkOne then
strWhere = "intFiscalQuarter = 1 OR"
end if
if me.chkTwo then
strWhere = strWhere & "intFiscalQuarter = 2 OR"
end if
if me.chkThree then
strWhere = strWhere & "intFiscalQuarter = 3 OR"
end if
'remove the last OR
strWhere = left(strWhere, len(strWhere)-3)

DoCmd.OpenForm formName, strWhere

you could use this same approach to open a query datasheet, but it is a little more involved

 

[MajP]

Oops. Disregard what I said, I missed the whole non normalized "Four Table" thing. I thought you just wanted to get the information from one table.

 

[RAxel]

Thanks for the quick responses everyone.

I've decided to move all the quarters into one table with a field entitled 'Quarter' to signify the quarter of the payroll.

This should be easier but there seems to be some problems. With all the data in one table, is it possible to get all the information from one person to fit on one line? For example, if the user wanted to consolidate all the quarters, I want them to be able to see all the quarters on one line, per individual, in case they wanted to export it to excel to make the information look better.

Also, when I do create a new table/insert values into a table from rubbernilly's code, can I disable the messageboxes asking if I really want to create this new table?

Thanks again.

 

[RAxel]

Excuse the question about the messageboxes, I just found the code docmd.setwarnings false and it works.

 

[PHV]

get all the information from one person to fit on one line?
SELECT person, Sum(IIf(Quarter=1,amount,0)) As Q1, Sum(IIf(Quarter=2,amount,0)) As Q2, Sum(IIf(Quarter=3,amount,0)) As Q3, Sum(IIf(Quarter=4,amount,0)) As Q4
FROM yourTable
GROUP BY person

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RAxel]

There seems to be a little bit of a problem with that PHV. It totals up all of the payroll information in the grosspayroll (which I used in place of amount) for all the quarters and sticks that in the Q1, Q2, etc... fields; instead of just putting in the Q1 payroll in the Q1 field.

 

[PHV]

You may group by more fields ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RAxel]

I'm sorry for the last reply, I was in a hurry. This is what I meant.

I have this table:

EmployeeID GrossPayroll OT Rate Quarter
1 $111.00 $111.00 $111.00 1
1 $222.00 $222.00 $222.00 2
1 $333.00 $333.00 $333.00 3
1 $444.00 $444.00 $444.00 4


Since I've moved all four quarters into one table, this is what I've ended up with. This is the code I have:

strSQL = "INSERT INTO [Consolidated Quarters] " & _
         "SELECT EmployeeID, Sum(IIf(chkFirst.Value=True,GrossPayroll,0)) As Q1GrossPayroll, " & _
         "Sum(IIf(chkSecond.Value=True,GrossPayroll,0)) As Q2GrossPayroll, Sum(IIf(chkThird.Value=True,GrossPayroll,0)) " & _
         "As Q3GrossPayroll, Sum(IIf(chkFourth.Value=True,GrossPayroll,0)) As Q4GrossPayroll" & _
         " FROM " & tbl & _
         " GROUP BY EmployeeID"
DoCmd.RunSQL strSQL

I want to put all the consolidated data into [Consolidated Quarters]. The problem I get is, say, the user wants to consolidate the first and second quarter, I get this in [Consolidated Quarters]:

EmployeeID Q1GrossPayroll Q2GrossPayroll etc...
1 $1110.00 $1110.00

It just sums up all the quarters information. Instead, I want:

EmployeeID Q1GrossPayroll Q2GrossPayroll etc...
1 $111.00 $222.00

 

[RAxel]

Sorry if I didn't include this before but chkFirst, chkSecond.... are checkboxes I have on a form.

 

[PHV]

You missread my suggestion, I'm afraid.
Replace this:
Sum(IIf(chkFirst.Value=True,GrossPayroll,0)) As Q1GrossPayroll
with this:
Sum(IIf(Quarter=1 AND chkFirst.Value=True,GrossPayroll,0)) As Q1GrossPayroll

And accordingly for Q2,Q3 and Q4

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RAxel]

Thanks again PHV, I did indeed missread your suggestions.