By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,948 Members | 804 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,948 IT Pros & Developers. It's quick & easy.

Create Ytd And Current Month Columns

P: 4
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.:

Expand|Select|Wrap|Line Numbers
  1. cust 1      vendor 1         ytd
  2. cust 1      vendor 1                     ytd    
  3. cust 1      vendor 1                                 cm
  4. etc.
I want it to look like this:

Expand|Select|Wrap|Line Numbers
  1. cust 1     vendor 1     ytd       ytd        cm       cm
  2.            vendor 2     ytd       ytd        cm       cm
etc.

CAN ANYONE HELP?!?!?!?!
Mar 9 '07 #1
Share this Question
Share on Google+
10 Replies


NeoPa
Expert Mod 15k+
P: 31,342
Can you post the queries you have already.
Can you explain why there are two ytd values shown?
Mar 10 '07 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
Something like the following should work

Assuming table structure as follows:

VendorName
SalesDate
SalesAmount
Expand|Select|Wrap|Line Numbers
  1. SELECT VendorName, 
  2. Sum(IIf(Year([SalesDate]) = Year(Date()), [SalesAmount],0)) As CurYTD,
  3. Sum(IIf(Year([SalesDate]) = Year(Date())-1, [SalesAmount],0)) As PrevYTD,
  4. Sum(IIf(Year([SalesDate]) = Year(Date()) And Month([SalesDate]) = Month(Date()), [SalesAmount],0)) As CurMth,
  5. Sum(IIf(Year([SalesDate]) = Year(Date())-1 And Month([SalesDate]) = Month(Date()), [SalesAmount],0)) As PrevMth
  6. FROM TableQueryName
  7. GROUP BY VendorName;
  8.  
Mary
Mar 10 '07 #3

NeoPa
Expert Mod 15k+
P: 31,342
Something like the following should work

Assuming table structure as follows:

VendorName
SalesDate
SalesAmount
Expand|Select|Wrap|Line Numbers
  1. SELECT VendorName, 
  2. Sum(IIf(Year([SalesDate]) = Year(Date()), [SalesAmount],0)) As CurYTD,
  3. Sum(IIf(Year([SalesDate]) = Year(Date())-1, [SalesAmount],0)) As PrevYTD,
  4. Sum(IIf(Year([SalesDate]) = Year(Date()) And Month([SalesDate]) = Month(Date()), [SalesAmount],0)) As CurMth,
  5. Sum(IIf(Year([SalesDate]) = Year(Date())-1 And Month([SalesDate]) = Month(Date()), [SalesAmount],0)) As PrevMth
  6. FROM TableQueryName
  7. GROUP BY VendorName;
  8.  
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 :(
Mar 10 '07 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
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!
Mar 10 '07 #5

NeoPa
Expert Mod 15k+
P: 31,342
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)
Mar 11 '07 #6

P: 4
Thanks a lot. I'll try this and let you know how it works. Although, it sounds like you are pretty confident.
Mar 13 '07 #7

P: 4
[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?
Mar 13 '07 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Mar 13 '07 #9

P: 4
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?
Mar 14 '07 #10

NeoPa
Expert Mod 15k+
P: 31,342
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 :
Expand|Select|Wrap|Line Numbers
  1. WHERE Sum([Sales])>0
Mar 14 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.