Union All Query

[mike]

Hi. I have an account list table and an sales table. I'm
trying to create a table that shows how much sales each of
my accounts made for every month in 2003 and 2004. The
problem is that some accounts in the list don't have sales
in every month and every year.

I've tried getting aroudn this by creating a table that
has the years 2003 and 2004 in it, a table that has the
months of the year in it, and creating a Union All query.
I then combine that Union All query with my account list
in another Union All query to get a list of every one of
my accounts with the years 2003 and 2004 and each month of
the year next to it.

I then have a query that joins all records from the Union
All query above with my actual sales table and join on the
account number, month, and year with an Nz function on the
sales column so that I have a value represented for each
account, year and month. There's gotta be an easier way.
Any thoughts? THANKS!

Here's my second Union All query:

SELECT tblAccountList.CUSTOMER__ AS AccountNumber,
qryYearMonth.Year, qryYearMonth.MonthID
FROM qryYearMonth, tblAccountList
ORDER BY tblAccountList.CUSTOMER__, qryYearMonth.Year,
qryYearMonth.MonthID;

Here's my final query:
SELECT qryYearMonthAccount.AccountNumber,
qryYearMonthAccount.Year, qryYearMonthAccount.MonthID AS
[Month], Nz([DollarsShipped],0) AS Sales
FROM qryYearMonthAccount LEFT JOIN
qryShippedByAccountByMonth ON (qryYearMonthAccount.MonthID
= qryShippedByAccountByMonth.Month) AND
(qryYearMonthAccount.Year =
qryShippedByAccountByMonth.Year) AND
(qryYearMonthAccount.AccountNumber =
qryShippedByAccountByMonth.AccountNumber);

 

[Michel Walsh]

Hi,

Have you explored the possibilities to make a crosstab, with the
accounts in GROUPS (vertical) and the month and the year as PIVOT
(horizontal), aggregate with a SUM(amount).

TRANSFORM Nz(SUM(amount), 0)
SELECT Company
FROM Accounts
GROUP BY Company
PIVOT Format(BillingDate, "yyyy-mm")


or something like that.


Hoping it may help,
Vanderghast, Access MVP

 

[mike]

Michel, Great suggestion. I don't necessarily need to join
the year field with anything at this point, so that should
work well. Thanks!

-----Original Message-----
Hi,

Have you explored the possibilities to make a crosstab, with the
accounts in GROUPS (vertical) and the month and the year as PIVOT
(horizontal), aggregate with a SUM(amount).

TRANSFORM Nz(SUM(amount), 0)
SELECT Company
FROM Accounts
GROUP BY Company
PIVOT Format(BillingDate, "yyyy-mm")


or something like that.


Hoping it may help,
Vanderghast, Access MVP

Hi. I have an account list table and an sales table. I'm
trying to create a table that shows how much sales each of
my accounts made for every month in 2003 and 2004. The
problem is that some accounts in the list don't have sales
in every month and every year.

I've tried getting aroudn this by creating a table that
has the years 2003 and 2004 in it, a table that has the
months of the year in it, and creating a Union All query.
I then combine that Union All query with my account list
in another Union All query to get a list of every one of
my accounts with the years 2003 and 2004 and each month of
the year next to it.

I then have a query that joins all records from the Union
All query above with my actual sales table and join on the
account number, month, and year with an Nz function on the
sales column so that I have a value represented for each
account, year and month. There's gotta be an easier way.
Any thoughts? THANKS!

Here's my second Union All query:

SELECT tblAccountList.CUSTOMER__ AS AccountNumber,
qryYearMonth.Year, qryYearMonth.MonthID
FROM qryYearMonth, tblAccountList
ORDER BY tblAccountList.CUSTOMER__, qryYearMonth.Year,
qryYearMonth.MonthID;

Here's my final query:
SELECT qryYearMonthAccount.AccountNumber,
qryYearMonthAccount.Year, qryYearMonthAccount.MonthID AS
[Month], Nz([DollarsShipped],0) AS Sales
FROM qryYearMonthAccount LEFT JOIN
qryShippedByAccountByMonth ON (qryYearMonthAccount.MonthID
= qryShippedByAccountByMonth.Month) AND
(qryYearMonthAccount.Year =
qryShippedByAccountByMonth.Year) AND
(qryYearMonthAccount.AccountNumber =
qryShippedByAccountByMonth.AccountNumber);

.