help with access query

[herman]

I have been tearing my hair out over this query. I have the table with:

company date quantity
acme 1/1/2005 1
acme 2/1/2004 2
acme 3/1/2005 3
apex 4/1/05 1
apex 3/1/04 4
abc 6/1/05 7

I want a query that gives me this:

Company 2004 2005
acme 2 4
apex 4 1
abc 0 7

Can someone please help me?

I use MS Access.

Thank you!

 

[Chris M]

Somthing like this?

select company, sum(p2004) as p2004, sum(p2005) as p2005
select company,0 as p2004,sum(qty) as p2005
from myTable
where date between #1/1/05# and #31/12/05#
union all
select company,sum(qty) as p2004, 0 as p2005
from myTable
where date between #1/1/04# and #31/12/04#

This was written off the top of my head, so the syntax may not be quite
right. The general idea is Ok though...

Cheers,

Chris.

 

[Chris M]

Sorry, posted a bit too soon...

select company, sum(p2004) as p2004, sum(p2005) as p2005
from
(
select company,0 as p2004,sum(qty) as p2005
from myTable
where date between #1/1/05# and #31/12/05#
union all
select company,sum(qty) as p2004, 0 as p2005
from myTable
where date between #1/1/04# and #31/12/04#
)

 

[Guest]

Try a 2 step process:
Query would be:
SELECT company, year([Date]) AS Expr1, CountOfUnits
FROM MyTable
GROUP BY company, Year([Date]);

Save the query above and then run a Form -> Pivot Table on it.
Make the rows = company and columns = year and data = count

Hope that helped.

 

[herman]

Thank you everyone for excellent help! Now I get to keep my hair

Now if I have something like this:

company date quantity1 quantity2
acme 1/1/2005 1 5
acme 2/1/2004 2 3
acme 3/1/2005 3 6
apex 4/1/05 1 2
apex 3/1/04 4 2
abc 6/1/05 7 8
xyz 2/1/04 2 5

I want a report showing by company sum of all qty1 for 2004 and 2005
and sum of qty2 for only 2005. How can i have it out like this?

comp sumqty1 (04 and 05) sumqty2(05 only)
acme 6 11
apex 5 2
abc 7 8
xyz 2 0

thanks so much!

A crosstab query will do this. Here is the SQL:
TRANSFORM Sum(Comp.Quantity) AS SumOfQuantity
SELECT Comp.Company, Sum(Comp.Quantity) AS [Total Of Quantity]
FROM [Comp]
GROUP BY Comp.Company
PIVOT Format([dater],"yyyy");

Noteon't name a field with a reserved word such as date. That is why
I always use dater to name a date field.

I have been tearing my hair out over this query. I have the table with:

company date quantity
acme 1/1/2005 1
acme 2/1/2004 2
acme 3/1/2005 3
apex 4/1/05 1
apex 3/1/04 4
abc 6/1/05 7

I want a query that gives me this:

Company 2004 2005
acme 2 4
apex 4 1
abc 0 7

Can someone please help me?

I use MS Access.

Thank you!