HELP~Query Not pulling all the data

  • Thread starter Thread starter DBarker
  • Start date Start date
D

DBarker

I will try to explain this.
I have an annual budget table
And I have a detail table
Values can be in either one of the tables and not
necessarily
in the other table.

I have tried setting up separate queries and then
combining them this pulls $$$ that exists in one but
not the $$$$ that exist in the other.

Basically if there is not a budget for that account but
there are charges posted to that account I want them to
show as well as the accounts that have budgets and nothing
charged to the account for the year.

I am driving myself crazy with this so any HELP would be
appreciated.
 
DBarker,

Yes, this will have to be done in two steps. Or three, I suppose. You
will need a query including both tables, with a Left Join from Budget to
Detail, to return all Budget items and the Detail items associated with
them. Then you will need a query with a Left Join from Detail to
Budget, to return all Detail items where there is no corresponding
Budget account. And then you will need a Union Query to combine the
results of these two. Please post back with details of your fields and
the outcome you want, if you need any more specific help with any of
these steps.
 
I have created both the queries that returned the data I
was looking for. Now the issue of the Union Query. I
opened a new query and put both queries created in there
and told it to make it a Union Query. Both queries have
all the same fields that I want. I can only find
examples using tables and SELECT and not Queries so I am
having problems with the syntax. I want to get Account
#, Name of Account, Department #, Department Name, Budget
Year, Annual Budget & SumofAmount. I need the data
included in both separate queries to show up. HELP
PLEEZE~
 
DBarker,

You can't make a Union Query using the query designer. You have to do
it in SQL view. If you follow these steps, it will pretty much give it
to you...

Open the query design of the second query you have created, and from the
View menu, select SQL. Select the SQL and Copy (Ctrl+C or Edit|Copy).
Open the first query in design view, and from the View menu select SQL.
Delete the ; from the end of the SQL, press Enter to go to a new line,
type UNION, press Enter to go to a new line, and Paste (Ctrl+V or
Edit|Paste). Try running this query, and let us know how you go. If it
doesn't do what you want, paste the whole SQL of the union query into
your reply post.

By the way, as an aside, it is not a good idea to use a # in the name of
a field or control or database object.
 
1st I have to thank you this as close as I have gotten
yet. It is bringing me all the data but in the budget
year it is placing it as currency and also includes an
amount posted without a budget. And it is not showing
any sum of amount in that column. Below is the SQL

SELECT [Annual Budget Query].[Account #], [Annual Budget
Query].Name_of_Account, [Annual Budget Query].[Department
#], [Annual Budget Query].Department_Name, [Annual Budget
Query].[Budget Year], [Annual Budget Query].[Annual
Budget], [Account Charges Query].SumOfAmount
FROM [Annual Budget Query] LEFT JOIN [Account Charges
Query] ON [Annual Budget Query].[Account #] = [Account
Charges Query].[Account #]

UNION

SELECT [Account Charges Query].[Account #], [Account
Charges Query].Name_of_Account, [Account Charges Query].
[Department #], [Account Charges Query].Department_Name,
[Account Charges Query].SumOfAmount, [Account Charges
Query].[Budget Year], Sum((Nz([Annual Budget],0))) AS
Budget
FROM [Account Charges Query] LEFT JOIN [Annual Budget
Query] ON [Account Charges Query].[Account #] = [Annual
Budget Query].[Account #]
GROUP BY [Account Charges Query].[Account #], [Account
Charges Query].Name_of_Account, [Account Charges Query].
[Department #], [Account Charges Query].Department_Name,
[Account Charges Query].SumOfAmount, [Account Charges
Query].[Budget Year];
 
Actually I figured it out, I had the order of the data
different in both queries when I made it the same it
pulled the data that I was looking for. Now my issue is
within the SQL I want to format it so that it will show 0
values as $0.00 instead of coming back as blank.

Thanks for all of your help.

These newsgroups have been such a big help~

Debbie
-----Original Message-----
1st I have to thank you this as close as I have gotten
yet. It is bringing me all the data but in the budget
year it is placing it as currency and also includes an
amount posted without a budget. And it is not showing
any sum of amount in that column. Below is the SQL

SELECT [Annual Budget Query].[Account #], [Annual Budget
Query].Name_of_Account, [Annual Budget Query]. [Department
#], [Annual Budget Query].Department_Name, [Annual Budget
Query].[Budget Year], [Annual Budget Query].[Annual
Budget], [Account Charges Query].SumOfAmount
FROM [Annual Budget Query] LEFT JOIN [Account Charges
Query] ON [Annual Budget Query].[Account #] = [Account
Charges Query].[Account #]

UNION

SELECT [Account Charges Query].[Account #], [Account
Charges Query].Name_of_Account, [Account Charges Query].
[Department #], [Account Charges Query].Department_Name,
[Account Charges Query].SumOfAmount, [Account Charges
Query].[Budget Year], Sum((Nz([Annual Budget],0))) AS
Budget
FROM [Account Charges Query] LEFT JOIN [Annual Budget
Query] ON [Account Charges Query].[Account #] = [Annual
Budget Query].[Account #]
GROUP BY [Account Charges Query].[Account #], [Account
Charges Query].Name_of_Account, [Account Charges Query].
[Department #], [Account Charges Query].Department_Name,
[Account Charges Query].SumOfAmount, [Account Charges
Query].[Budget Year];

-----Original Message-----
DBarker,

You can't make a Union Query using the query designer. You have to do
it in SQL view. If you follow these steps, it will pretty much give it
to you...

Open the query design of the second query you have created, and from the
View menu, select SQL. Select the SQL and Copy (Ctrl+C or Edit|Copy).
Open the first query in design view, and from the View menu select SQL.
Delete the ; from the end of the SQL, press Enter to go to a new line,
type UNION, press Enter to go to a new line, and Paste (Ctrl+V or
Edit|Paste). Try running this query, and let us know how you go. If it
doesn't do what you want, paste the whole SQL of the union query into
your reply post.

By the way, as an aside, it is not a good idea to use a # in the name of
a field or control or database object.
Click to expand...
data
I
.
Click to expand...
.
Click to expand...
 
Debbie,

I see you have already used a Nz() function within one of the queries,
so you obviously familiar with it. I think this will be applicable.
For example, one of the segments from your your SQL might be like this...

SELECT ... Budget], Nz([Account Charges Query].SumOfAmount,0) AS
TotalAmount FROM [Annual Budget ...
 
Below is my union query where I ask the user to enter a
department # and a Budget year but it still is bringing
back all the departments and I am not sure why. HELP~


SELECT [Annual Budget Query].[Account #], [Annual Budget
Query].Name_of_Account, [Annual Budget Query].[Department
#], [Annual Budget Query].Department_Name, [Annual Budget
Query].[Budget Year], Sum((Nz([SumOfAmount]))) AS Amount,
Sum((Nz([Annual Budget]))) AS Budget
FROM [Annual Budget Query] LEFT JOIN [Account Charges
Query] ON [Annual Budget Query].[Account #] = [Account
Charges Query].[Account #]
GROUP BY [Annual Budget Query].[Account #], [Annual
Budget Query].Name_of_Account, [Annual Budget Query].
[Department #], [Annual Budget Query].Department_Name,
[Annual Budget Query].[Budget Year]

UNION SELECT [Account Charges Query].[Account #],
[Account Charges Query].Name_of_Account, [Account Charges
Query].[Department #], [Account Charges
Query].Department_Name, [Account Charges Query].[Budget
Year], Sum((Nz([SumOfAmount]))) AS Amount, Sum((Nz
([Annual Budget]))) AS Budget
FROM [Account Charges Query] LEFT JOIN [Annual Budget
Query] ON [Account Charges Query].[Account #] = [Annual
Budget Query].[Account #]
GROUP BY [Account Charges Query].[Account #], [Account
Charges Query].Name_of_Account, [Account Charges Query].
[Department #], [Account Charges Query].Department_Name,
[Account Charges Query].[Budget Year]

HAVING ((([Account Charges Query].[Department #])=[Enter
Department #]) AND (([Account Charges Query].[Budget
Year])=[Enter Budget Year]));
-----Original Message-----
Debbie,

I see you have already used a Nz() function within one of the queries,
so you obviously familiar with it. I think this will be applicable.
For example, one of the segments from your your SQL might be like this...

SELECT ... Budget], Nz([Account Charges Query].SumOfAmount,0) AS
TotalAmount FROM [Annual Budget ...

--
Steve Schapel, Microsoft Access MVP

Actually I figured it out, I had the order of the data
different in both queries when I made it the same it
pulled the data that I was looking for. Now my issue is
within the SQL I want to format it so that it will show 0
values as $0.00 instead of coming back as blank.

Thanks for all of your help.

These newsgroups have been such a big help~

Debbie
Click to expand...
.
Click to expand...
 
Back
Top