Detailing an Access report by Month 1

[rutkus]

hey, ive been trying to figure this out for two weeks now and have decided that i do not have the knowledge that is necessary to figure this out. (this is the third forum I post at)

I have a report setup that lists every vendor and their respective information categorized by month, so e.g.

January
--Vendor A
----Total Invoices
--Vendor B
----Total Invoices

I need to setup a report that lists each vendors monthly invoices individually, so e.g.

Jan Feb Mar ...
Vendor A
--Total Invoices

Is there a formula i can use for it to spit out each of these calculated columns or am i going to have to setup a section for each column and have it enter its respective information

Im not sure if i can program this in VB or if i need to setup the report to categorize itself through access,

any and all help will be appreciated,
thank you
Omar Qureshy
HI-Tek Computing

 

[jjonesal]

You should be able to create a crosstab query with the vendor as your row value & the month as the column value...

J. Jones
jjones@cybrtyme.com

 

[rutkus]

thanks jjones,

i dont know much about queries, this is the third access database ive made and ive yet to use one. that may sound really weird, but i also only learned access by reading help files and playing with the programs themselves. so im not quite sure how to setup a crosstab query.

right now im in the query design view and ive set the vendor as the first "field" and the ApprovedDate(the field that defines the monthly seperations) as the second "field" now what should i look for it to do?? do i have to edit the "total:", "sort:" or "criteria" sections for either "field"

and once i setup the query how do i even hook this up with the report??

i really dont know much about queries. ill start reading the help files and see if i can figure out what im actually doing.

thanks,
omar qureshy
hi-tek

 

[jjonesal]

There's a wizard to help in creating crosstab queries. Or, from query design view, select Query, Crosstab Query from the menu.

This will provide you a new row in the query design grid (Crosstab).

For the vendor field, set the Crosstab row to Row Heading. For the date field, set the Crosstab row to Column Heading. Then, add a new field to your query for the invoice amount & set the Total row in the query design grid to Sum and the Crosstab row in the query design grid to Value. If you want the column values to be just the month, then you can use the Format function to convert the date to just a Month literal.

I can understand completely your frustration, as I learned Access the same way (starting with the first release). J. Jones
jjones@cybrtyme.com

 

[rutkus]

hey jjones,

thanks alot for your help, i think im starting to get an understanding of queries. I've run into another snag though and i was hoping someone could help me.

I've got the crosstab query working, but the thing is i need more than one value to be broken down by month. I need to know the total # of invoices, the total $$ of invoices and the avg processing time for those invoices, per month. Right now i have the vendor name as a row heading,along with the sum of $$ amount of invoices, and the count of # of invoices, the months are the column heading and the avg process time is the value.

Im assuming ill need to be able to set the $$, # and avg process time all as values to be able to get them individually broken down by month. BUT the crosstab query says i can only set one field as a value. So maybe theres another query type thatll be better suited for me??? or maybe im just looking at this all wrong. hehe, either way, im gonna keep playing with it, but if anyone has any clue what to do, id REALLY appreciate it. I'm so close to getting this done and it would really help me keep a job, woo hoo.

thanks
omar qureshy
DSC Networks

 

[jjonesal]

Your SQL for the crosstab query should look something like:

TRANSFORM Sum(tbl.invamt) AS SumOfamt
SELECT tbl.vendor
FROM tbl
GROUP BY tbl.vendor
PIVOT tbl.mydate;

In SQL View, you should be able to add a new column to the select.... like

TRANSFORM Sum(tbl.invamt) AS SumOfamt
SELECT tbl.vendor, count(tbl.invid)
FROM tbl
GROUP BY tbl.vendor
PIVOT tbl.mydate;

See if that helps....



J. Jones
jjones@cybrtyme.com

 

[rutkus]

Alright Mr. Jones, (assuming youre a male, as chauvinistic as that is)

this is exactly what my SQL View looks like:

TRANSFORM Sum(([ApprovedDate]-[ReceivedDate])) AS [Avg Processing Time]
SELECT Invoices.Vendor, Count(Invoices.Invoice) AS CountOfInvoice, Sum(Invoices.ApprovedAmount) AS SumOfApprovedAmount
FROM Invoices
GROUP BY Invoices.Vendor
ORDER BY Invoices.Vendor
PIVOT Format([ApprovedDate],"mmm"


I'm not quite sure what you meant, it seems like the "count (tbl.invid)" is already there. maybe im just not looking at it right. what i think i need to do is:

have it GROUP BY invoices.vendor, countofinvoice and sumofapprovedamount.

im assuming doing this will organize everything by vendor, their number of invoices and sum of app amount. this may not be correct, but it seems some what logical to me.

right now, when i look at the query in datasheet view it shows me each vendor, the TOTAL number of invoices, the TOTAL amount of invoices and then the avg proc time by month.

thanks so much for your time jjones, i really appreciate it

Omar Qureshy
DSC Networks

 

[jjonesal]

I'm not sure the grouping you suggested will work the way you plan.

But, how about this....create two crosstab queries (one that uses one calculation for VALUE and another that uses the other calculation for VALUE - everything else the same).

Then, create a third query joining these two queries by vendor.....I know this is kinda klunky, but it WILL work...

BTW: It is Ms. Jones :-0 J. Jones
jjones@cybrtyme.com

 

[rutkus]

Wow, Ms. Jones, I could just snuggle up with you

I could see what youre saying, and how it makes sense. Basically I copied the query twice, renamed them and took out the other variables, leaving the vendor, approved date and approvedamount/invoice. Now, I'm trying to join them and I cant seem to get the datasheet looking right, basically, I get the vendors for each row and month for each column, then in each column im getting a lump sum. Now the numbers are nowhere near the invoice amounts and way to high to be adding up the number of invoices and avg processing time. Im guessing its that nothing im doing is right, SO...

how would i go about joining these marvelous creations together to form something like a SuperQuery??

is it something in the design view? relationships? in the baby queries?

and what exactly will this look like in datasheet form?? i was thinking it would be like a tree expansion thing (haha, you know like trees that expand)and then i gotta worry about figuring out in report form.

im sorry, i have absolutely no clue what I'm doing.

thank you so much ms. jones,

Omar Qureshy
DSC Net

 

[jjonesal]

You should be able to join these two queries just by vendor id.

One of your crosstab queries will have vendor for row, date for column, and sum of invoice for value.

The other crosstab query will have vendor for row, date for column, and count of invoice for value.

Then, in your "Super Query" (a simple select query joining both of the above by vendor), you'll have a column for vendor id, a column for the first date's value from the first query, a column for the first date's value from the second query, the second date's value from the first query, the second date's value from the second query, ...

Does that make sense??? J. Jones
jjones@cybrtyme.com