queries

  • Thread starter Thread starter memalef
  • Start date Start date
M

memalef

i have already 3 queries.
first query gets me result for all period.
second query gets me result for last month.
third query gets me result for last week.
these 3 results i have to put into different fields of another "report"
table that looks like this:
| COMPANY | TOTAL | LAST_MONTH | LAST_WEEK |
 
You could probably do this in a single query, but since you already have the
3, I'd use them. Join the 3 queries in a new query, just like you would
tables, on Company, or better yet on CompanyID, put the 3 columns in (1 from
each) and use that new query as a recordsource for your report.
 
I don't understand how I have to join 3 queries in order to get results like
this:
| COMPANY | TOTAL | LAST_MONTH | LAST_WEEK |

-total queries-
SELECT tbl_total.PO_NAME, Sum(tbl_total.TOTAL) AS TOTAL,
Sum(tbl_total.CHARGE) AS CHARGE, Sum(tbl_total.PACKING) AS PACKING,
Sum(tbl_total.GLASS) AS GLASS, Sum(tbl_total.PAYMENT) AS PAYMENT,
Sum(tbl_total.OTHER) AS OTHER,
Sum(tbl_total.TOTAL)+Sum(tbl_total.CHARGE)+Sum(tbl_total.PACKING)+Sum(tbl_total.GLASS)+Sum(tbl_total.PAYMENT)+Sum(tbl_total.OTHER) AS ["*SUM*"]
FROM tbl_total
GROUP BY tbl_total.PO_NAME;

-last month query-
SELECT tbl_total.PO_NAME, Sum(tbl_total.TOTAL) AS TOTAL,
Sum(tbl_total.CHARGE) AS CHARGE, Sum(tbl_total.PACKING) AS PACKING,
Sum(tbl_total.GLASS) AS GLASS, Sum(tbl_total.PAYMENT) AS PAYMENT,
Sum(tbl_total.OTHER) AS OTHER,
Sum(tbl_total.TOTAL)+Sum(tbl_total.CHARGE)+Sum(tbl_total.PACKING)+Sum(tbl_total.GLASS)+Sum(tbl_total.PAYMENT)+Sum(tbl_total.OTHER) AS LAST_MONTH
FROM tbl_total
WHERE (((Exists (SELECT tbl_total.PO_NAME, tbl_total.TOTAL,
tbl_total.CHARGE, tbl_total.PACKING, tbl_total.GLASS, tbl_total.PAYMENT,
tbl_total.OTHER,
(tbl_total.TOTAL+tbl_total.CHARGE+tbl_total.PACKING+tbl_total.GLASS+tbl_total.PAYMENT+tbl_total.OTHER) AS [LAST_MONTH]
FROM tbl_total
WHERE (((tbl_total.SHIPPING_DATE) Between
DateSerial(Year(Date()),Month(Date()),0) And Date()))))<>False) AND
((tbl_total.SHIPPING_DATE) Between DateSerial(Year(Date()),Month(Date()),0)
And Date()))
GROUP BY tbl_total.PO_NAME;


-last week query-
SELECT tbl_total.PO_NAME, tbl_total.TOTAL, tbl_total.CHARGE,
tbl_total.PACKING, tbl_total.GLASS, tbl_total.PAYMENT, tbl_total.OTHER,
(tbl_total.TOTAL+tbl_total.CHARGE+tbl_total.PACKING+tbl_total.GLASS+tbl_total.PAYMENT+tbl_total.OTHER) AS LAST_WEEK
FROM tbl_total
WHERE (((tbl_total.SHIPPING_DATE) Between Date()-Weekday(Date())+1 And
Date()));
 
Because each query is getting data from a different period. You don't have
to join them if you create a column for each period that uses an IIF ()
Function to isolate the data from each period in a single query.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


memalef said:
I don't understand how I have to join 3 queries in order to get results
like
this:
| COMPANY | TOTAL | LAST_MONTH | LAST_WEEK |

-total queries-
SELECT tbl_total.PO_NAME, Sum(tbl_total.TOTAL) AS TOTAL,
Sum(tbl_total.CHARGE) AS CHARGE, Sum(tbl_total.PACKING) AS PACKING,
Sum(tbl_total.GLASS) AS GLASS, Sum(tbl_total.PAYMENT) AS PAYMENT,
Sum(tbl_total.OTHER) AS OTHER,
Sum(tbl_total.TOTAL)+Sum(tbl_total.CHARGE)+Sum(tbl_total.PACKING)+Sum(tbl_total.GLASS)+Sum(tbl_total.PAYMENT)+Sum(tbl_total.OTHER)
AS ["*SUM*"]
FROM tbl_total
GROUP BY tbl_total.PO_NAME;

-last month query-
SELECT tbl_total.PO_NAME, Sum(tbl_total.TOTAL) AS TOTAL,
Sum(tbl_total.CHARGE) AS CHARGE, Sum(tbl_total.PACKING) AS PACKING,
Sum(tbl_total.GLASS) AS GLASS, Sum(tbl_total.PAYMENT) AS PAYMENT,
Sum(tbl_total.OTHER) AS OTHER,
Sum(tbl_total.TOTAL)+Sum(tbl_total.CHARGE)+Sum(tbl_total.PACKING)+Sum(tbl_total.GLASS)+Sum(tbl_total.PAYMENT)+Sum(tbl_total.OTHER)
AS LAST_MONTH
FROM tbl_total
WHERE (((Exists (SELECT tbl_total.PO_NAME, tbl_total.TOTAL,
tbl_total.CHARGE, tbl_total.PACKING, tbl_total.GLASS, tbl_total.PAYMENT,
tbl_total.OTHER,
(tbl_total.TOTAL+tbl_total.CHARGE+tbl_total.PACKING+tbl_total.GLASS+tbl_total.PAYMENT+tbl_total.OTHER)
AS [LAST_MONTH]
FROM tbl_total
WHERE (((tbl_total.SHIPPING_DATE) Between
DateSerial(Year(Date()),Month(Date()),0) And Date()))))<>False) AND
((tbl_total.SHIPPING_DATE) Between
DateSerial(Year(Date()),Month(Date()),0)
And Date()))
GROUP BY tbl_total.PO_NAME;


-last week query-
SELECT tbl_total.PO_NAME, tbl_total.TOTAL, tbl_total.CHARGE,
tbl_total.PACKING, tbl_total.GLASS, tbl_total.PAYMENT, tbl_total.OTHER,
(tbl_total.TOTAL+tbl_total.CHARGE+tbl_total.PACKING+tbl_total.GLASS+tbl_total.PAYMENT+tbl_total.OTHER)
AS LAST_WEEK
FROM tbl_total
WHERE (((tbl_total.SHIPPING_DATE) Between Date()-Weekday(Date())+1 And
Date()));





Arvin Meyer said:
You could probably do this in a single query, but since you already have
the
3, I'd use them. Join the 3 queries in a new query, just like you would
tables, on Company, or better yet on CompanyID, put the 3 columns in (1
from
each) and use that new query as a recordsource for your report.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com





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