Access Database Union 3 tables

  • Thread starter Thread starter Alaa
  • Start date Start date
A

Alaa

I have query which collects the total expenses and income per month from two
tables which works fine, now I want to add a third table to collect the total
payroll per month: here is my query and when I try to run it it gives me the
error:"Data type mismatch in the criteria expression"

Query:
SELECT DatePart('m', Date2) AS MDate, SUM(IncAmount) AS TInc,
SUM(ExpAmount) AS TExp, SUM(PayAmount) AS TPay
FROM [SELECT I.IncDate AS Date2,I.Amount AS IncAmount,null AS
ExpAmount,null AS PayAmount FROM Income I WHERE I.IncDate BETWEEN
#1/Jul/2007# AND #30/Jun/2008#
UNION ALL SELECT E.ExpDate AS Date2,null AS IncAmount,E.Amount AS
ExpAmount,null AS PayAmount FROM Expenses E WHERE E.ExpDate BETWEEN
#1/Jul/2007# AND #30/Jun/2008#
UNION ALL SELECT P.PayDate AS Date2,null AS IncAmount,null AS
ExpAmount,P.Net AS PayAmount FROM Payroll P WHERE P.PayDate BETWEEN
#1/Jul/2007# AND #30/Jun/2008#
]. AS U
GROUP BY DatePart('m', Date2)
ORDER BY DatePart('m', Date2)

============
As I said, if you include only 2 tables , the query will run with no
problem, but as soon as I add the third table, it shows the above error.

Much appreciated for any help to solve my problem.
 
]. AS U

What's that period for?

You're not using the alias U so why have it?

Maybe simplify the query for testing by just running the core UNION ALL part
to see if it works. Then add the rest from there.
 
Hi Jerry, thanks for the reply.
My original working query is below:
Note: the U represent the new table combination from both Expenses and
Income tables.

Working Query:
SELECT DatePart('m', U.Date2) AS MDate, SUM(U.IncAmount) AS TInc,
SUM(U.ExpAmount) AS TExp
FROM [SELECT I.IncDate AS Date2,I.Amount AS IncAmount,null AS
ExpAmount FROM Income I
UNION ALL SELECT E.ExpDate AS Date2,null AS IncAmount,E.Amount AS ExpAmount
FROM Expenses E]. AS U
WHERE U.Date2 BETWEEN #1/Jul/2007# AND #30/Jun/2008#
GROUP BY DatePart('m', U.Date2)
ORDER BY DatePart('m', U.Date2)
================
I can also replace "]." with normal bracket ")" and I could also remove the
U and the query will still work as it should be "I am defining a name to my
new table" as I some times join the combination query to another queries
which then I need an alias for my new table.

Also you might notice that in my working query "I union both tables" then
set my date condition which is the right way. But for my new query with the
third table, I set a condition per query or table as the payroll date
condition will be different than the Expenses and Income date conditions.

Hope this will help you or someone to let me know what is wrong with my
three tables union query as far as I see it should work as all fields have
the same data field type:
PayDate,ExpDate,IncDate: Short Date
Amount,Net: Currency

Much appreciated



Jerry Whittle said:
]. AS U
Click to expand...

What's that period for?

You're not using the alias U so why have it?

Maybe simplify the query for testing by just running the core UNION ALL part
to see if it works. Then add the rest from there.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Alaa said:
I have query which collects the total expenses and income per month from two
tables which works fine, now I want to add a third table to collect the total
payroll per month: here is my query and when I try to run it it gives me the
error:"Data type mismatch in the criteria expression"

Query:
SELECT DatePart('m', Date2) AS MDate, SUM(IncAmount) AS TInc,
SUM(ExpAmount) AS TExp, SUM(PayAmount) AS TPay
FROM [SELECT I.IncDate AS Date2,I.Amount AS IncAmount,null AS
ExpAmount,null AS PayAmount FROM Income I WHERE I.IncDate BETWEEN
#1/Jul/2007# AND #30/Jun/2008#
UNION ALL SELECT E.ExpDate AS Date2,null AS IncAmount,E.Amount AS
ExpAmount,null AS PayAmount FROM Expenses E WHERE E.ExpDate BETWEEN
#1/Jul/2007# AND #30/Jun/2008#
UNION ALL SELECT P.PayDate AS Date2,null AS IncAmount,null AS
ExpAmount,P.Net AS PayAmount FROM Payroll P WHERE P.PayDate BETWEEN
#1/Jul/2007# AND #30/Jun/2008#
]. AS U
GROUP BY DatePart('m', Date2)
ORDER BY DatePart('m', Date2)

============
As I said, if you include only 2 tables , the query will run with no
problem, but as soon as I add the third table, it shows the above error.

Much appreciated for any help to solve my problem.
Click to expand...
Click to expand...
 
I suggest creating three queries with the different date criteria. Then
joining these three queries in a Union query.

To manage all the different dates, you could create an unbound form with 6
date fields and a button to run the Union query. In the criteria for the
queries puts something like below:

Between Forms![FormName]![TextBoxName1] And Forms![FormName]![TextBoxName2]
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Alaa said:
Hi Jerry, thanks for the reply.
My original working query is below:
Note: the U represent the new table combination from both Expenses and
Income tables.

Working Query:
SELECT DatePart('m', U.Date2) AS MDate, SUM(U.IncAmount) AS TInc,
SUM(U.ExpAmount) AS TExp
FROM [SELECT I.IncDate AS Date2,I.Amount AS IncAmount,null AS
ExpAmount FROM Income I
UNION ALL SELECT E.ExpDate AS Date2,null AS IncAmount,E.Amount AS ExpAmount
FROM Expenses E]. AS U
WHERE U.Date2 BETWEEN #1/Jul/2007# AND #30/Jun/2008#
GROUP BY DatePart('m', U.Date2)
ORDER BY DatePart('m', U.Date2)
================
I can also replace "]." with normal bracket ")" and I could also remove the
U and the query will still work as it should be "I am defining a name to my
new table" as I some times join the combination query to another queries
which then I need an alias for my new table.

Also you might notice that in my working query "I union both tables" then
set my date condition which is the right way. But for my new query with the
third table, I set a condition per query or table as the payroll date
condition will be different than the Expenses and Income date conditions.

Hope this will help you or someone to let me know what is wrong with my
three tables union query as far as I see it should work as all fields have
the same data field type:
PayDate,ExpDate,IncDate: Short Date
Amount,Net: Currency

Much appreciated



Jerry Whittle said:
]. AS U
Click to expand...

What's that period for?

You're not using the alias U so why have it?

Maybe simplify the query for testing by just running the core UNION ALL part
to see if it works. Then add the rest from there.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Alaa said:
I have query which collects the total expenses and income per month from two
tables which works fine, now I want to add a third table to collect the total
payroll per month: here is my query and when I try to run it it gives me the
error:"Data type mismatch in the criteria expression"

Query:
SELECT DatePart('m', Date2) AS MDate, SUM(IncAmount) AS TInc,
SUM(ExpAmount) AS TExp, SUM(PayAmount) AS TPay
FROM [SELECT I.IncDate AS Date2,I.Amount AS IncAmount,null AS
ExpAmount,null AS PayAmount FROM Income I WHERE I.IncDate BETWEEN
#1/Jul/2007# AND #30/Jun/2008#
UNION ALL SELECT E.ExpDate AS Date2,null AS IncAmount,E.Amount AS
ExpAmount,null AS PayAmount FROM Expenses E WHERE E.ExpDate BETWEEN
#1/Jul/2007# AND #30/Jun/2008#
UNION ALL SELECT P.PayDate AS Date2,null AS IncAmount,null AS
ExpAmount,P.Net AS PayAmount FROM Payroll P WHERE P.PayDate BETWEEN
#1/Jul/2007# AND #30/Jun/2008#
]. AS U
GROUP BY DatePart('m', Date2)
ORDER BY DatePart('m', Date2)

============
As I said, if you include only 2 tables , the query will run with no
problem, but as soon as I add the third table, it shows the above error.

Much appreciated for any help to solve my problem.
Click to expand...
Click to expand...
Click to expand...
 
Well, Let me start again
I have a report which shows the total expenses and income for the selected
financial year as below:
If no expenses/incomes per that month, then it will be empty "Null"
I am using VB.Net Report viewer to create the reprot:

Month Expenses Income
July 50,000.00
August 20,000.00
October 14,000.00 77,000.00

The following query creates the above report with no problem:

SELECT DatePart('m', U.Date2) AS MDate, SUM(U.IncAmount) AS TInc,
SUM(U.ExpAmount) AS TExp
FROM [SELECT I.IncDate AS Date2,I.Amount AS IncAmount,null AS
ExpAmount FROM Income I
UNION ALL SELECT E.ExpDate AS Date2,null AS IncAmount,E.Amount AS ExpAmount
FROM Expenses E]. AS U
WHERE U.Date2 BETWEEN #1/Jul/2007# AND #30/Jun/2008#
GROUP BY DatePart('m', U.Date2)
ORDER BY DatePart('m', U.Date2)


Now, what I need is to add a third column to my report to collect the total
payroll per month
Month Payroll Expenses Income
July 5000.00 50,000.00
August 20,000.00
October 7000.00 14,000.00 77,000.00

I have tried this query but I keep getting the "Data type mismatch in the
criteria expression"

SELECT DatePart('m', U.Date2) AS MDate, SUM(U.IncAmount) AS TInc,
SUM(U.ExpAmount) AS TExp, SUM(U.PayAmount) AS TPay
FROM [SELECT I.IncDate AS Date2,I.Amount AS IncAmount,null AS
ExpAmount,null AS PayAmount FROM Income I WHERE I.IncDate BETWEEN
#1/Jul/2007# AND #30/Jun/2008#
UNION ALL SELECT E.ExpDate AS Date2,null AS IncAmount,E.Amount AS
ExpAmount,null AS PayAmount FROM Expenses E WHERE E.ExpDate BETWEEN
#1/Jul/2007# AND #30/Jun/2008#
UNION ALL SELECT P.PayDate AS Date2,null AS IncAmount,null AS
ExpAmount,P.Net AS PayAmount FROM Payroll P WHERE P.PayDate BETWEEN
#1/Jul/2007# AND #30/Jun/2008#
]. AS U
GROUP BY DatePart('m', U.Date2)
ORDER BY DatePart('m', U.Date2)

Much appreciated
 
Hi Jerry,
The query will not work as you try to retrieve the PayDate while grouping by
DatePart.
I have been working on this query for awhile now and it seems I am running
out of options.
So, I am going to ask if you have a valid query to retrieve the information
I want as below:
Return the total expenses (Amount), total income(Amount), and total
Payroll(Net) per month for a selected period.

Tables Structure:
Expenses: ExpDate,Amount, other fields are not required to be included in
this query
Income: IncDate,Amount, other fields are not required to be included in this
query
Payroll: PayDate,Net, other fields are not required to be included in this
query

The query should output as below:
Month TotalExpense TotalIncome TotalPayroll
July 13000 25000
6000
August 10000 30000
September 30000
7000


Much appreciated

Jerry Whittle said:
Does this work properly?

SELECT P.PayDate AS Date2,
null AS IncAmount,
null AS ExpAmount,
P.Net AS PayAmount
FROM Payroll P
WHERE P.PayDate BETWEEN
#1/Jul/2007# AND #30/Jun/2008#
GROUP BY DatePart('m', P.Date2)
ORDER BY DatePart('m', P.Date2)
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Alaa said:
Well, Let me start again
I have a report which shows the total expenses and income for the selected
financial year as below:
If no expenses/incomes per that month, then it will be empty "Null"
I am using VB.Net Report viewer to create the reprot:

Month Expenses Income
July 50,000.00
August 20,000.00
October 14,000.00 77,000.00

The following query creates the above report with no problem:

SELECT DatePart('m', U.Date2) AS MDate, SUM(U.IncAmount) AS TInc,
SUM(U.ExpAmount) AS TExp
FROM [SELECT I.IncDate AS Date2,I.Amount AS IncAmount,null AS
ExpAmount FROM Income I
UNION ALL SELECT E.ExpDate AS Date2,null AS IncAmount,E.Amount AS ExpAmount
FROM Expenses E]. AS U
WHERE U.Date2 BETWEEN #1/Jul/2007# AND #30/Jun/2008#
GROUP BY DatePart('m', U.Date2)
ORDER BY DatePart('m', U.Date2)


Now, what I need is to add a third column to my report to collect the total
payroll per month
Month Payroll Expenses Income
July 5000.00 50,000.00
August 20,000.00
October 7000.00 14,000.00 77,000.00

I have tried this query but I keep getting the "Data type mismatch in the
criteria expression"

SELECT DatePart('m', U.Date2) AS MDate, SUM(U.IncAmount) AS TInc,
SUM(U.ExpAmount) AS TExp, SUM(U.PayAmount) AS TPay
FROM [SELECT I.IncDate AS Date2,I.Amount AS IncAmount,null AS
ExpAmount,null AS PayAmount FROM Income I WHERE I.IncDate BETWEEN
#1/Jul/2007# AND #30/Jun/2008#
UNION ALL SELECT E.ExpDate AS Date2,null AS IncAmount,E.Amount AS
ExpAmount,null AS PayAmount FROM Expenses E WHERE E.ExpDate BETWEEN
#1/Jul/2007# AND #30/Jun/2008#
UNION ALL SELECT P.PayDate AS Date2,null AS IncAmount,null AS
ExpAmount,P.Net AS PayAmount FROM Payroll P WHERE P.PayDate BETWEEN
#1/Jul/2007# AND #30/Jun/2008#
]. AS U
GROUP BY DatePart('m', U.Date2)
ORDER BY DatePart('m', U.Date2)

Much appreciated
Click to expand...
Click to expand...
 
i have a method it is a prity dirty one but it works

4 queries

SELECT Month([expdate]) AS expensesmonth, Year([expdate]) AS
expensesyear, Sum(expenses.amount) AS SumOfamount
FROM expenses
GROUP BY Month([expdate]), Year([expdate]);

qrytotalexpenses

SELECT Month([incdate]) AS incomemonth, Year([incdate]) AS incomeyear,
Sum(income.amount) AS SumOfamount
FROM income
GROUP BY Month([incdate]), Year([incdate]);

qrytotalincome


SELECT Month([paydate]) AS payrollmonth, Year([paydate]) AS
mayrollyear, Sum(payroll.net) AS SumOfnet
FROM payroll
GROUP BY Month([paydate]), Year([paydate]);

qrytotalpayroll


SELECT DISTINCT qrytotalincome.incomemonth,
qrytotalexpenses.SumOfamount, qrytotalincome.SumOfamount,
qrytotalpayroll.SumOfnet
FROM (qrytotalincome LEFT JOIN qrytotalexpenses ON
(qrytotalincome.incomeyear = qrytotalexpenses.expensesyear) AND
(qrytotalincome.incomemonth = qrytotalexpenses.expensesmonth)) LEFT
JOIN qrytotalpayroll ON (qrytotalincome.incomeyear =
qrytotalpayroll.mayrollyear) AND (qrytotalincome.incomemonth =
qrytotalpayroll.payrollmonth);


namethiswhatever


it only works is the income table has an entry for each month beacause
it left joins the records from there it will not work if that table
doesnt though it will basically miss records this is so because in the
sample data you supplied it appeard that the total expenses and total
payroll can be empty from month to month

hope this helps

remember as a note this is a bandaid solution but since there dosnt
appear to be any other method for joining the tables together it is
"A" solution not "THE" solution

Regards
Kelvan
 
Back
Top