SQL Union Query

[paulS30berks]

I have created a report that provides me with employee expenses for temps per week. The types of expenses have been defined as Ad_hoc amounts.

An SQL union query I have used to combine fields

Adhoc_Code_1 - 3
Adhoc_description_1 - 3
Adhoc_Pay_Amount_1 - 3

(details of full sql query below)

I have tried to run for a particular week which should have 3 expense entries but only 2 have been picked up.

I think this is because both Adhoc_ Pay_Amount_2 and 3 have a value of 6 and the UNION operation will not return duplicate records. I have amended to UNION ALL but all entries are duplicated. Can anyone help?

Thanks

SELECT dbo_Valid_Timesheets.Employer_Ref, dbo_Valid_Timesheets.Personnel_Ref, dbo_Valid_Timesheets.Department, dbo_Valid_Timesheets.Tax_Yr_Proc_By_Payroll, dbo_Valid_Timesheets.Period_Proc_By_Payroll, dbo_Valid_Timesheets.Session_Proc_By_Payroll, dbo_Valid_Timesheets.Adhoc_Code_1, dbo_Valid_Timesheets.Adhoc_Description_1, dbo_Valid_Timesheets.Adhoc_Pay_Amount_1, dbo_Valid_Timesheets.Timesheet_Number
FROM dbo_Valid_Timesheets
WHERE (((dbo_Valid_Timesheets.Adhoc_Pay_Amount_1)<>0));
UNION ALL
SELECT dbo_Valid_Timesheets.Employer_Ref, dbo_Valid_Timesheets.Personnel_Ref, dbo_Valid_Timesheets.Department, dbo_Valid_Timesheets.Tax_Yr_Proc_By_Payroll, dbo_Valid_Timesheets.Period_Proc_By_Payroll, dbo_Valid_Timesheets.Session_Proc_By_Payroll, dbo_Valid_Timesheets.Adhoc_Code_2, dbo_Valid_Timesheets.Adhoc_Description_2, dbo_Valid_Timesheets.Adhoc_Pay_Amount_2, dbo_Valid_Timesheets.Timesheet_Number
FROM dbo_Valid_Timesheets
WHERE (((dbo_Valid_Timesheets.Adhoc_Pay_Amount_2)<>0));
UNION ALL
SELECT dbo_Valid_Timesheets.Employer_Ref, dbo_Valid_Timesheets.Personnel_Ref, dbo_Valid_Timesheets.Department, dbo_Valid_Timesheets.Tax_Yr_Proc_By_Payroll, dbo_Valid_Timesheets.Period_Proc_By_Payroll, dbo_Valid_Timesheets.Session_Proc_By_Payroll, dbo_Valid_Timesheets.Adhoc_Code_3, dbo_Valid_Timesheets.Adhoc_Description_3, dbo_Valid_Timesheets.Adhoc_Pay_Amount_3, dbo_Valid_Timesheets.Timesheet_Number
FROM dbo_Valid_Timesheets
WHERE (((dbo_Valid_Timesheets.Adhoc_Pay_Amount_3)<>0));

UNION ALL SELECT dbo_EE_Payment_History.Employer_Ref, dbo_EE_Payment_History.Personnel_Ref, dbo_Payslip_Static_Data.Department, dbo_EE_Payment_History.Tax_Year, dbo_EE_Payment_History.Tax_Period, dbo_EE_Payment_History.Tax_Session, dbo_EE_Payment_History.Payment_Ref, dbo_EE_Payment_History.Type, Val([Payment_Value]) AS [Value], "" AS Timesheet
FROM dbo_EE_Payment_History INNER JOIN dbo_Payslip_Static_Data ON (dbo_EE_Payment_History.Tax_Session = dbo_Payslip_Static_Data.Session_Number) AND (dbo_EE_Payment_History.Tax_Period = dbo_Payslip_Static_Data.Period_Number) AND (dbo_EE_Payment_History.Tax_Year = dbo_Payslip_Static_Data.Tax_Year) AND (dbo_EE_Payment_History.Personnel_Ref = dbo_Payslip_Static_Data.Personnel_Ref) AND (dbo_EE_Payment_History.Employer_Ref = dbo_Payslip_Static_Data.Employer_Ref)
WHERE (((dbo_EE_Payment_History.Tax_Year)=[Forms]![Misc]![year]) AND ((dbo_EE_Payment_History.Tax_Period)=[Forms]![Misc]![period]) AND ((dbo_EE_Payment_History.Tax_Session)=[Forms]![Misc]![session]) AND ((dbo_EE_Payment_History.Payment_Ref)=777));

UNION ALL SELECT dbo_EE_Payments.Employer_Ref, dbo_EE_Payments.Personnel_Ref, dbo_Payslip_Static_Data.Department, [Forms]![Misc]![year] AS Tax_Year, [Forms]![Misc]![period] AS Tax_Period, [Forms]![Misc]![session] AS Tax_Session, dbo_EE_Payments.Payment_Ref, dbo_EE_Payments.X_Type, Val([Calculated_Value]) AS [Value], "" AS Timesheet
FROM dbo_EE_Payments INNER JOIN dbo_Payslip_Static_Data ON (dbo_EE_Payments.Personnel_Ref = dbo_Payslip_Static_Data.Personnel_Ref) AND (dbo_EE_Payments.Employer_Ref = dbo_Payslip_Static_Data.Employer_Ref)
WHERE (((dbo_EE_Payments.Payment_Ref)=777));

 

[Len Boorman]

I do not think that records have been duplicated actually. What happens with a UNION query is that the first select statement determoines the field names so that in your case AdHoc_PayAmount_1 will also hold the values returned in the selecy select Adhoc_PayAmount_2.

So First select statement determines Field names, All subsequent select statements will drop their values into these columns in the sequence determined in the first select statement.

This is why second and subsequent select statements in a UNION query must be of same data type and in the same sequence as the first select statement.

This is why I suspect that your problem is not one of duplication but the way it which the data is presented.

As a check pick out the individual select statements and run those as individual queries so that you can check the data. If data is okay then it is the presentation that is causing the confusion

Len

 

[paulS30berks]

Many thanks for your reply.

I have individually tested each statement and they all work ok. So presentation it seems does need some altering.

Do you have any suggestions?

Thanks

 

[Len Boorman]

How about a slightly different approach.

It sounds as if you are looking to get some form of summary as an end result.

Have you considered a table to hold the data as an interim stage.

Basically you then run Append queries to put the relevant bits of data into a table that contains only the fields you are really interested in. This way you could have the 3 payment fields you are looking for,

This would give you a reduced data set where you could now run summation queries,reports etc.

Not sure what your end goal is but I have used this method before and whilst maybe a bit longer does allow you to work with dataset that is all in one place so the queries does not have any joins or the like,

Tweaking the UNION query I feel is going to need the generation of additional fields to cover the three payment types in the first select, maybe via alias's and then you will need to amend the remaining selects to be sure that the field sequence was equal in terms of number of fields but also sequenced correctly. Could probably be done but personally prefer the simpler, maybe longer approach.

Do not forget to delete all records in the interim table before running the appends.

Best I can think of at the moment

L

 

[paulS30berks]

Thanks for your reply.

The report I am running reports on additional expenses incurred by Temps on a weekly basis.

There are some underlying queries - A base level union query that pulls together these 3 seperate fields from a table and combines them into one column so for example:

Table format:

Adhoc_Code_1 Adhoc_Description_1 Adhoc_Pay_Amount_1 Adhoc_Code_2 Adhoc_Description_2 Adhoc_Pay_Amount_2 etc etc all on one row..

Union Query:

Adhoc_Pay_Amount_1 Adhoc_Code_1 Adhoc_Description_1

This query is then used within a seperate query to produce a summary but combining 3 seperate fields within a row into one field within a column. If this makes sense?

 

[Len Boorman]

Still confused a bit but that could be age.

So what you are after is really a summary of additional expenses maybe based on a week number.

So if you UNION ALL AdHoc_Description, Adhoc_Pay_Amount records maybe with week number say would it matter whether or not they were adhoc_1 or 2 or 3, It it did matter then use the AdHoc_Code in the UNION as well

Really I think the problem stems from the table. This really should be a table say Ad_Hoc_Payment with field for Payment Code and amount plus additional to identify person and say week number

You would then have a long narrow table rather than short fat so to speak but the fundamental difference is that Payment is all in one column and is qualified for reason etc in additional columns. Makes analysis,summation, selection much easier

L

 

[paulS30berks]

Unfortunately I do not have full control over the table. I am using linked tables to a main server held elsewhere..

Abit confused about what you mean - that could be down to Friday afternoon.

thanks

 

[paulS30berks]

The summary of expenses if by week number and by employee.

i am trying to summarize these additional expense details into one column, however using the SQl code I have written the query misses some of the data.

I have used UNION ALL however via the full I receive several entries per employee including duplicates.

Thanks

 

[neileg]

I think confusion reigns. Len was on the way to solving the problem but didn't persist.

Union queries produce a continuous set of data from two or more tables or queries. This requires that the data from each source is compatible in terms of both datatypes and fieldnames. You don't seem to have your field names sorted.

Suppose I have two tables (assume field datatypes are OK):

tblInvoices
InvID
TransDate
InvValue
InvDesc

tblCredits
CreditID
TransDate
CredValue
CredDesc

To union these you would use:

SELECT tblInvoices.TransDate, tblInvoices.InvValue AS TransValue, tblInvoices.InvDesc AS Desc;

UNION SELECT tblCredits.TransDate, tblCredits.CredValue AS TransValue, tblCredits.CredDesc AS Desc;

This means that each individual select statement produces a set of fields with matching names in the same order.

 

[paulS30berks]

Thanks for your reply

I have found a solution. If I type in:

1 AS Line_No
2 AS Line_No

etc etc

at the end of the select + union statements, all lines will report.

Thanks

 

[Len Boorman]

Thanks for stepping in Neil

Unfortunately (perhjaps) I do not work Friday pm and was otherwise tied up this afternoon, Only got back to have a look this evening

L