Create Ytd And Current Month Columns

[Marcia]

I am new to this and not to brag, but I'm very impressed at the progress that I have made considering that I have NO formal tutoring on any kind of computer program. It has all be trial and error for me. Anyway, I have created a database for my work. I am satisfied with it except I am stuck on a report. I have a table with:
Fields:
customer id
vendor name
date
invoice
sales

I need to have a report that lists every customer and every vendor with sales columns that show YTD this year, YTD last year, Current Mo this year, and Current Month last year.
I have succeeded in making the different queries but cannot seem to get all of the information in one querie. I did the sub report thing, but it shows the information in "groups" instead of straight across:

i.e.:

cust 1      vendor 1         ytd
cust 1      vendor 1                     ytd    
cust 1      vendor 1                                 cm
etc.

I want it to look like this:

cust 1     vendor 1     ytd       ytd        cm       cm
           vendor 2     ytd       ytd        cm       cm

etc.

CAN ANYONE HELP?!?!?!?!

[NeoPa]

Can you post the queries you have already.
Can you explain why there are two ytd values shown?

[MMcCarthy]

Something like the following should work

Assuming table structure as follows:

VendorName
SalesDate
SalesAmount

SELECT VendorName, 
Sum(IIf(Year([SalesDate]) = Year(Date()), [SalesAmount],0)) As CurYTD,
Sum(IIf(Year([SalesDate]) = Year(Date())-1, [SalesAmount],0)) As PrevYTD,
Sum(IIf(Year([SalesDate]) = Year(Date()) And Month([SalesDate]) = Month(Date()), [SalesAmount],0)) As CurMth,
Sum(IIf(Year([SalesDate]) = Year(Date())-1 And Month([SalesDate]) = Month(Date()), [SalesAmount],0)) As PrevMth
FROM TableQueryName
GROUP BY VendorName;
 

Mary

[NeoPa]

Something like the following should work

Assuming table structure as follows:

VendorName
SalesDate
SalesAmount

SELECT VendorName, 
Sum(IIf(Year([SalesDate]) = Year(Date()), [SalesAmount],0)) As CurYTD,
Sum(IIf(Year([SalesDate]) = Year(Date())-1, [SalesAmount],0)) As PrevYTD,
Sum(IIf(Year([SalesDate]) = Year(Date()) And Month([SalesDate]) = Month(Date()), [SalesAmount],0)) As CurMth,
Sum(IIf(Year([SalesDate]) = Year(Date())-1 And Month([SalesDate]) = Month(Date()), [SalesAmount],0)) As PrevMth
FROM TableQueryName
GROUP BY VendorName;
 

Mary

OK smartie.
So I missed the bit of the explanation where the current & previous stuff was detailed. I was trying to go on the example that was shown which threw me off the scent a bit :D.
Well spotted.
But!
Not to be outdone I went through your SQL with a fine-tooth comb, only to find it was perfect :(

[MMcCarthy]

OK smartie.
So I missed the bit of the explanation where the current & previous stuff was detailed. I was trying to go on the example that was shown which threw me off the scent a bit :D.
Well spotted.
But!
Not to be outdone I went through your SQL with a fine-tooth comb, only to find it was perfect :(

Well of course it was, when do I ever make mistakes. Shh!

[NeoPa]

Well of course it was, when do I ever make mistakes. Shh!

Never ask a question you don't know the answer to!
(I suppose you didn't huh? hence the Shh :D)

[Marcia]

Thanks a lot. I'll try this and let you know how it works. Although, it sounds like you are pretty confident.

[Marcia]

[quote=NeoPa]Never ask a question you don't know the answer to!
(I suppose you didn't huh? hence the Shh :D)[/QUOTe
Can I do this in the report controls?

[MMcCarthy]

Can I do this in the report controls?

You can make this query the Record Source of your report if that's what you mean. I'm not sure what you are asking in your question.

Mary

[Marcia]

You can make this query the Record Source of your report if that's what you mean. I'm not sure what you are asking in your question.

Mary

Got it. It worked great. The only problem is that on the ytd column for previous year, it showed all sales for the entire year instead of just up to the current month. I worked that out. My only other question is...The report is showing customers that have had any sales at any time during the year for last year or this year even if the amounts for the time period is $0.00. How can I illiminate the customers with no sales at all for this time period without affecting the other customers that may have $0.00 values in just some instances?

[NeoPa]

If you only want those customers that have had 'some' sales in the specified period (regardless of the value of those sales), then an INNER JOIN with the sales data (I don't think we know what/where that is stored) would do you.
If you want only those customers whose sales exceed 0 in the specified period, then you put something like the following in the WHERE clause :

WHERE Sum([Sales])>0