After Unmatched Query Runs, Getting the Difference from Totals

V

Vylent Fyre

I have created an Unmatched Quiz for two tables - tbl_WRFNSR Curr Month and
tbl_WRFNSR Prior Months.

My objective is once this query is done, I'd like to get the difference
between the unmatched results. Both tables have the Inside Labor column.
Every time I try to do:

Diff: {tbl_WRFNSR_Curr Month].[Inside Labor] - [tbl_WRFNSR_Prior
Month].[Inside Labor] I get nothing.

If I try to bring the Inside Labor from the tbl_WRFNSR Prior months, I get
nothing, even though there are totals in the table. Below is the SQL -
Please keep in mind the Unmatched Query works great, except I'd like to take
it one step further and get a Difference column in there for my report.

I've also tried a new query that pulls this Unmatched Query and the Prior
Months Table to see if I can get it to pull the Inside Labor totals and it
does not work.


Below is the SQL for the Unmatched Query:

SELECT [tbl_WRFNSR Current Month].[Month Downloaded], [tbl_WRFNSR Current
Month].Div, [tbl_WRFNSR Current Month].[AFE#], [tbl_WRFNSR Current
Month].Class, [tbl_WRFNSR Current Month].Description, [tbl_WRFNSR Current
Month].Loc, [tbl_WRFNSR Current Month].[Yard Description], [tbl_WRFNSR
Current Month].[Inside Labor], [tbl_WRFNSR Current Month].[Outside Labor],
[tbl_WRFNSR Current Month].Materials
FROM [tbl_WRFNSR Current Month] LEFT JOIN [tbl_WRFNSR Prior Months] ON
([tbl_WRFNSR Current Month].[AFE#] = [tbl_WRFNSR Prior Months].[AFE#]) AND
([tbl_WRFNSR Current Month].[Inside Labor] = [tbl_WRFNSR Prior
Months].[Inside Labor])
WHERE ((([tbl_WRFNSR Current Month].[Inside Labor])<>0) AND (([tbl_WRFNSR
Prior Months].[Inside Labor]) Is Null));


I bet it is possible, I'm just not catching what I need to do! :)

Thank you to everyone who will answer my question! :) :)
 
K

KARL DEWEY

You do not seem to realize what an unmatched query result is giving you.
Your query is seeking records that has matching [Inside Labor] --
([tbl_WRFNSR Current Month].[Inside Labor] = [tbl_WRFNSR Prior
Months].[Inside Labor])
Then you are say you only want records in which [tbl_WRFNSR Prior
Months].[Inside Labor]) has no information - WHERE .... [tbl_WRFNSR Prior
Months].[Inside Labor]) Is Null

there are no records that have it both ways.

When you sutract [tbl_WRFNSR_Curr Month].[Inside Labor] - [tbl_WRFNSR_Prior
Month].[Inside Labor] you would get nothing because when you perform a math
function with a value and a NULL the results will always be NULL.

Explain a little more as to what you are trying to do. How do you want to
compare the two tables?

Why do you have two tables instead of just one that has a field for the date
so you can derive the month?
 
V

Vylent Fyre

I have two tables for this reason: Every month, I have to run a report from
the beginning of the year to the current end of the month for a project
total. (I just ran it through the end of May 2008 last week.)
Unfortunately, a lot of people "back date" their entries and I need to
capitalize all new projects to their specified AFE #'s. They go back even as
far as (In May 2008) to January 2008. I've tried for almost a year to get
this set up to where this cannot happen but I am having no luck in this.

So, to do this - I have one table "Prior Months" (which will hold
information from the reports I have done previously) and then the Current
Month's table will have the information from the report I just "ran that day"
so to speak. I then need to get the new items (based off of each table's AFE
#'s) to capitalize and book accordingly. This is why I created the unmatched
query. Keep in mind the report is from another system and I convert into a
database format through an external program. Don't worry - I've ensured the
totals match and it picks up everything.

Now, if it was just new items, that would be great - I already have a query
for this and a report to book these entries accordingly. However, I've come
to one new problem - Someone changed the amount instead of adding a new one
(AFE #). So, I need it to not only know when there are "unmatched items" but
I need it to let me know if a particular AFE # has a different amount now as
well.

Please let me know if this is possible and what part of my query needs to be
changed or what I need to add. I'm fairly new to the SQL language at this
time and I won't deny that I may appear like a total idiot to certain people.



KARL DEWEY said:
You do not seem to realize what an unmatched query result is giving you.
Your query is seeking records that has matching [Inside Labor] --
([tbl_WRFNSR Current Month].[Inside Labor] = [tbl_WRFNSR Prior
Months].[Inside Labor])
Then you are say you only want records in which [tbl_WRFNSR Prior
Months].[Inside Labor]) has no information - WHERE .... [tbl_WRFNSR Prior
Months].[Inside Labor]) Is Null

there are no records that have it both ways.

When you sutract [tbl_WRFNSR_Curr Month].[Inside Labor] - [tbl_WRFNSR_Prior
Month].[Inside Labor] you would get nothing because when you perform a math
function with a value and a NULL the results will always be NULL.

Explain a little more as to what you are trying to do. How do you want to
compare the two tables?

Why do you have two tables instead of just one that has a field for the date
so you can derive the month?
--
KARL DEWEY
Build a little - Test a little


Vylent Fyre said:
I have created an Unmatched Quiz for two tables - tbl_WRFNSR Curr Month and
tbl_WRFNSR Prior Months.

My objective is once this query is done, I'd like to get the difference
between the unmatched results. Both tables have the Inside Labor column.
Every time I try to do:

Diff: {tbl_WRFNSR_Curr Month].[Inside Labor] - [tbl_WRFNSR_Prior
Month].[Inside Labor] I get nothing.

If I try to bring the Inside Labor from the tbl_WRFNSR Prior months, I get
nothing, even though there are totals in the table. Below is the SQL -
Please keep in mind the Unmatched Query works great, except I'd like to take
it one step further and get a Difference column in there for my report.

I've also tried a new query that pulls this Unmatched Query and the Prior
Months Table to see if I can get it to pull the Inside Labor totals and it
does not work.


Below is the SQL for the Unmatched Query:

SELECT [tbl_WRFNSR Current Month].[Month Downloaded], [tbl_WRFNSR Current
Month].Div, [tbl_WRFNSR Current Month].[AFE#], [tbl_WRFNSR Current
Month].Class, [tbl_WRFNSR Current Month].Description, [tbl_WRFNSR Current
Month].Loc, [tbl_WRFNSR Current Month].[Yard Description], [tbl_WRFNSR
Current Month].[Inside Labor], [tbl_WRFNSR Current Month].[Outside Labor],
[tbl_WRFNSR Current Month].Materials
FROM [tbl_WRFNSR Current Month] LEFT JOIN [tbl_WRFNSR Prior Months] ON
([tbl_WRFNSR Current Month].[AFE#] = [tbl_WRFNSR Prior Months].[AFE#]) AND
([tbl_WRFNSR Current Month].[Inside Labor] = [tbl_WRFNSR Prior
Months].[Inside Labor])
WHERE ((([tbl_WRFNSR Current Month].[Inside Labor])<>0) AND (([tbl_WRFNSR
Prior Months].[Inside Labor]) Is Null));


I bet it is possible, I'm just not catching what I need to do! :)

Thank you to everyone who will answer my question! :) :)
Click to expand...
Click to expand...
 
K

KARL DEWEY

Try this --
SELECT [tbl_WRFNSR Current Month].[Month Downloaded], [tbl_WRFNSR Current
Month].Div, [tbl_WRFNSR Current Month].[AFE#], [tbl_WRFNSR Current
Month].Class, [tbl_WRFNSR Current Month].Description, [tbl_WRFNSR Current
Month].Loc, [tbl_WRFNSR Current Month].[Yard Description], [tbl_WRFNSR
Current Month].[Inside Labor], [tbl_WRFNSR Current Month].[Outside Labor],
[tbl_WRFNSR Current Month].Materials, [tbl_WRFNSR Current Month].[Inside
Labor]-[tbl_WRFNSR Prior Months].[Inside Labor] AS Diff
FROM [tbl_WRFNSR Current Month] LEFT JOIN [tbl_WRFNSR Prior Months] ON
[tbl_WRFNSR Current Month].[AFE#] = [tbl_WRFNSR Prior Months].[AFE#]
WHERE ((([tbl_WRFNSR Current Month].[Inside Labor])<>[tbl_WRFNSR Prior
Months].[Inside Labor])) OR ((([tbl_WRFNSR Prior Months].[Inside Labor]) Is
Null));

--
KARL DEWEY
Build a little - Test a little


Vylent Fyre said:
I have two tables for this reason: Every month, I have to run a report from
the beginning of the year to the current end of the month for a project
total. (I just ran it through the end of May 2008 last week.)
Unfortunately, a lot of people "back date" their entries and I need to
capitalize all new projects to their specified AFE #'s. They go back even as
far as (In May 2008) to January 2008. I've tried for almost a year to get
this set up to where this cannot happen but I am having no luck in this.

So, to do this - I have one table "Prior Months" (which will hold
information from the reports I have done previously) and then the Current
Month's table will have the information from the report I just "ran that day"
so to speak. I then need to get the new items (based off of each table's AFE
#'s) to capitalize and book accordingly. This is why I created the unmatched
query. Keep in mind the report is from another system and I convert into a
database format through an external program. Don't worry - I've ensured the
totals match and it picks up everything.

Now, if it was just new items, that would be great - I already have a query
for this and a report to book these entries accordingly. However, I've come
to one new problem - Someone changed the amount instead of adding a new one
(AFE #). So, I need it to not only know when there are "unmatched items" but
I need it to let me know if a particular AFE # has a different amount now as
well.

Please let me know if this is possible and what part of my query needs to be
changed or what I need to add. I'm fairly new to the SQL language at this
time and I won't deny that I may appear like a total idiot to certain people.



KARL DEWEY said:
You do not seem to realize what an unmatched query result is giving you.
Your query is seeking records that has matching [Inside Labor] --
([tbl_WRFNSR Current Month].[Inside Labor] = [tbl_WRFNSR Prior
Months].[Inside Labor])
Then you are say you only want records in which [tbl_WRFNSR Prior
Months].[Inside Labor]) has no information - WHERE .... [tbl_WRFNSR Prior
Months].[Inside Labor]) Is Null

there are no records that have it both ways.

When you sutract [tbl_WRFNSR_Curr Month].[Inside Labor] - [tbl_WRFNSR_Prior
Month].[Inside Labor] you would get nothing because when you perform a math
function with a value and a NULL the results will always be NULL.

Explain a little more as to what you are trying to do. How do you want to
compare the two tables?

Why do you have two tables instead of just one that has a field for the date
so you can derive the month?
--
KARL DEWEY
Build a little - Test a little


Vylent Fyre said:
I have created an Unmatched Quiz for two tables - tbl_WRFNSR Curr Month and
tbl_WRFNSR Prior Months.

My objective is once this query is done, I'd like to get the difference
between the unmatched results. Both tables have the Inside Labor column.
Every time I try to do:

Diff: {tbl_WRFNSR_Curr Month].[Inside Labor] - [tbl_WRFNSR_Prior
Month].[Inside Labor] I get nothing.

If I try to bring the Inside Labor from the tbl_WRFNSR Prior months, I get
nothing, even though there are totals in the table. Below is the SQL -
Please keep in mind the Unmatched Query works great, except I'd like to take
it one step further and get a Difference column in there for my report.

I've also tried a new query that pulls this Unmatched Query and the Prior
Months Table to see if I can get it to pull the Inside Labor totals and it
does not work.


Below is the SQL for the Unmatched Query:

SELECT [tbl_WRFNSR Current Month].[Month Downloaded], [tbl_WRFNSR Current
Month].Div, [tbl_WRFNSR Current Month].[AFE#], [tbl_WRFNSR Current
Month].Class, [tbl_WRFNSR Current Month].Description, [tbl_WRFNSR Current
Month].Loc, [tbl_WRFNSR Current Month].[Yard Description], [tbl_WRFNSR
Current Month].[Inside Labor], [tbl_WRFNSR Current Month].[Outside Labor],
[tbl_WRFNSR Current Month].Materials
FROM [tbl_WRFNSR Current Month] LEFT JOIN [tbl_WRFNSR Prior Months] ON
([tbl_WRFNSR Current Month].[AFE#] = [tbl_WRFNSR Prior Months].[AFE#]) AND
([tbl_WRFNSR Current Month].[Inside Labor] = [tbl_WRFNSR Prior
Months].[Inside Labor])
WHERE ((([tbl_WRFNSR Current Month].[Inside Labor])<>0) AND (([tbl_WRFNSR
Prior Months].[Inside Labor]) Is Null));


I bet it is possible, I'm just not catching what I need to do! :)

Thank you to everyone who will answer my question! :) :)
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Remove zero totals in query 4
how to resolve a parameter conflict with unmatched records query? 7
compare and unmatched 8
Summary query sum from different tables 15
To reconstruct a data table 1
Query records from the current year, month and records with Null value 2
if function (if more that 7 nesting) 2
Find unmatched query with selected date 2

Top