Query help needed

[Derevon]

Hello everyone,

I have two tables, let's call them tbA and tbB.

In tbA I keep track of the number of invoices added to the system for each location on a given date and in tbB how many invoices were processed for each location on a given date. My primary keys for both tables are composite keys of location and date (no location can have more than one entry per date).

For sake of simplicity, let's say both tables are identical in structure and contain the following fields:

aDate (date)
Location (integer)
Invoices (integer)

Now I would like to create a query that shows me the backlog (invoices added minus invoices processed) for each location given a certain date range. At first I tried to create a query where I join the group fields and the location fields from the two tables and calculate the Invoices from "added" minus invoices from "processed", but then I realised there is no guarantee that there will be entries in both tables for the same dates, so that won't work.

What would be a good way to solve this? My end goal is to have a form with a subform showing all locations and their backlogs. There will be textboxes to fill in the date range to which the calculations should apply.

Any help is appreciated.

 

[namliam]

Try this query, it should do the backlog per day per location

select adate, Location, sum(invoices) from ( 
Select aDate, Location, Invoices from tbA
union all
select aDate, Location, Invoices * -1 from tbB ) as UnionQry
where adate Between #12/01/2013# and #12/31/2013#
group by adate, Location

If you want it per period... Perhaps something like:

select Location,min(adate) as StartPeriod, max(adate) as EndPeriod, sum(invoices) from ( 
Select aDate, Location, Invoices from tbA
union all
select aDate, Location, Invoices * -1 from tbB ) as UnionQry
where adate Between #12/01/2013# and #12/31/2013#
group by Location

 

[Derevon]

Thanks a lot. They worked great.

 

[Derevon]

I'm now trying to use the above SQL-query as a recordsource for a subform in my form, but I would also like to throw in some sorting so the records are listed in order of backlog size (descending). I modified the second query a bit by throwing in an "Abs" around the sum(invoices) in order to get the backlog in positive numbers rather than negative, and I added "As MyName" after the "abs(sum(invoices))" and thereafter tried to throw in a "Order by Myname" in the end of the query, but for some reason Access asks me to manually input a value for this "Myname" when the SQL-query is executed, although the sorting actually seems to work even if I just hit enter. Any idea what causes this and how it could be eliminated?

Thanks

 

[namliam]

You cant sort on alias names in access, you sort on the original formula, i.e. the sum...

If you want the backlog in postive numbers, why not simply turn around the positive and negative side in the union?

select Location,min(adate) as StartPeriod, max(adate) as EndPeriod, sum(invoices) from ( 
Select aDate, Location, Invoices [COLOR="DarkOrange"]* -1[/COLOR] from tbA
union all
select aDate, Location, Invoices [COLOR="DarkOrange"]*  1[/COLOR] from tbB ) as UnionQry
where adate Between #12/01/2013# and #12/31/2013#
group by Location
[COLOR="DarkOrange"]Order by Sum(Invoices) desc[/COLOR]

 

[Derevon]

I guess after hours of trying to figure out a solution I was not thinking straight and didn't fully understand how your query worked. Now that I do, I have to say it's a very neat solution. One that I would never have come up with for sure. Thanks again

 

[Derevon]

You cant sort on alias names in access, you sort on the original formula, i.e. the sum...

If you want the backlog in postive numbers, why not simply turn around the positive and negative side in the union?

select Location,min(adate) as StartPeriod, max(adate) as EndPeriod, sum(invoices) from ( 
Select aDate, Location, Invoices [COLOR=darkorange]* -1[/COLOR] from tbA
union all
select aDate, Location, Invoices [COLOR=darkorange]*  1[/COLOR] from tbB ) as UnionQry
where adate Between #12/01/2013# and #12/31/2013#
group by Location
[COLOR=darkorange]Order by Sum(Invoices) desc[/COLOR]

When I try multiplying Invoices by -1 (or anything else for that matter) in the first of the two Select lines (tbA) it doesn't work for some reason and I'm prompted to enter "Invoices" manually. I suppose it must be because I'm using a query (not the tables directly anymore) that sums the total count of each invoice type together, and that this Sum got some other datatype or something, but I don't really understand why.

I use queries for both now instead of table, and in the processed invoices query there is a field like "Invoices: InvoiceTypeA + InvoiceTypeB + InvoiceTypeC + InvoiceTypeD", but shouldn't the result be stored as a numerical value with which calculations could be made?

Thanks

 

[Derevon]

As it turns out it worked if I just swapped position of the two Select lines in the Union. No idea about why that would matter, though.

 

[namliam]

Probably because the column name is created by the first select in a union, by multiplying it by -1 that name isnt proper anymore....

The INVOICES that you are prompted for, isnt from the union query rather from the select statement that is summing it....

select Location,min(adate) as StartPeriod, max(adate) as EndPeriod, sum(Inv) as BacklogInvoices from ( 
Select aDate, Location, Invoices * -1  as Inv from tbA
union all
select aDate, Location, Invoices *  1 from tbB ) as UnionQry
where adate Between #12/01/2013# and #12/31/2013#
group by Location
Order by Sum(Inv) desc

That should give you the idea.