complex querey

[Access WannaBe]

I link to a table (dbf) on a server that stores transaction details (last
count 170,000 lines). I would like to be able to sort this table by year and
month. Ultimately telling me how many widgets I sold each month for the
current year and each of the prior two years.

I found a way to do it, using 3 queries to sort out the dates, 3 cross tab
queries to total each month, and one final query to bring it all together. I
have two problems with this, first it’s kind of slow, and second I have to
reconstruct queries, forms, and reports each year.

I tried to use some date functions in my queries to automate the change of
year. It worked but made everything run unbearably slow.

Can someone give me a direction to head in? I’m sure I’ve left out critical
information, let me know what I can do to help you help me.

Thank you
Chad Shouse

 

[KARL DEWEY]

This does the breakout by year and month --
TRANSFORM Sum([Change Requests].x) AS SumOfx
SELECT Format([Date close],"yyyy") AS [Year]
FROM [Change Requests]
GROUP BY Format([Date close],"yyyy")
PIVOT Format([Date close],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

A further breakout by type of Wiget --
TRANSFORM Sum([Change Requests].x) AS SumOfx
SELECT Format([Date close],"yyyy") AS [Year], [Change Requests].Wigets
FROM [Change Requests]
GROUP BY Format([Date close],"yyyy"), [Change Requests].Wigets
PIVOT Format([Date close],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

 

[Access WannaBe]

Karl, That did it. Thank you for your help, I appreciated it very much.

This does the breakout by year and month --
TRANSFORM Sum([Change Requests].x) AS SumOfx
SELECT Format([Date close],"yyyy") AS [Year]
FROM [Change Requests]
GROUP BY Format([Date close],"yyyy")
PIVOT Format([Date close],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

A further breakout by type of Wiget --
TRANSFORM Sum([Change Requests].x) AS SumOfx
SELECT Format([Date close],"yyyy") AS [Year], [Change Requests].Wigets
FROM [Change Requests]
GROUP BY Format([Date close],"yyyy"), [Change Requests].Wigets
PIVOT Format([Date close],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

--
KARL DEWEY
Build a little - Test a little

I link to a table (dbf) on a server that stores transaction details (last
count 170,000 lines). I would like to be able to sort this table by year and
month. Ultimately telling me how many widgets I sold each month for the
current year and each of the prior two years.

I found a way to do it, using 3 queries to sort out the dates, 3 cross tab
queries to total each month, and one final query to bring it all together. I
have two problems with this, first it’s kind of slow, and second I have to
reconstruct queries, forms, and reports each year.

I tried to use some date functions in my queries to automate the change of
year. It worked but made everything run unbearably slow.

Can someone give me a direction to head in? I’m sure I’ve left out critical
information, let me know what I can do to help you help me.

Thank you
Chad Shouse