Query Question (1 Viewer)

sajarac

Registered User.
Local time
Today, 19:03
Joined
Aug 18, 2015
Messages
126
Hi there, I am having this issue, and I can not find the way to produce the report needed.



In my data table I have several columns but the most important for me now are:

AuditFY

Customer

Sales Rep

JobFY



When I get the job I recorded the "AuditFY" but the customer could accept the job in the same FY or in a Different FY "JobFY"



For both "AuditFY" and "JobFY" I have dropdowns menus like "FY2019", "FY2018", FY2017"...etc



If I try to run a query without any filter just the query to see AuditFT, Customer, Sales Rep, JobFY, I can see all of the data. but once I try to see any specifit FY it doesn't match for both.



Somethin like this:

Sorted by AuditFY





AuditFT AuditValue JobFY Job Value Sales Rep ID
FY2019 $6,860.00 FY2019 $6,860.00 219
FY2019 $351,641.00 252
FY2019 $20,670.00 368
FY2019 $65,260.00 360
FY2019 $23,140.00 208
FY2019 $95,211.00 276
FY2019 $145,191.00 126
FY2019 $17,711.00 252
FY2019 $42,861.00 368
FY2019 $29,401.00 309
FY2019 $56,161.30 365


Sorted by JobFY





AuditFY AuditValue JobFY Job Value Sales Rep ID
FY2019 $6,860.00 FY2019 $6,860.00 219
FY2018 $40,000.00 FY2019 $8,586.00 317
FY2018 $23,200.00 FY2019 $13,572.00 239
FY2018 $52,790.40 FY2019 $2,613.00 368
FY2018 $41,457.00 FY2019 $41,457.00 368
FY2018 $39,607.00 FY2019 $39,607.00 217
FY2018 $4,699.50 FY2019 $4,699.50 343
FY2018 FY2019 $42,205.00 368
FY2018 $28,865.20 FY2019 $28,865.20 368
FY2018 $31,289.00 FY2019 $31,288.40 368


So in summary:



I would like to see my query sorted like:



IF(AuditFY=FY2019, and JobFY=2019) Sum [AuditValue]+[Job Value]



Thanks in advance for any help.



Regards,
 

Minty

AWF VIP
Local time
Today, 23:03
Joined
Jul 26, 2013
Messages
10,355
Try something like;

Code:
SELECT Customer, SalesReprID , Sum([AuditValue]+[Job Value]) as Total
FROM YourTable
WHERE AuditFY='FY2019' and JobFY= 'FY2019'
Group By Customer, SalesRepID
 

sajarac

Registered User.
Local time
Today, 19:03
Joined
Aug 18, 2015
Messages
126
Thank you very much for your prompt reply.

I guess I've followed your instruction, but at the result was only a single record retrieved.
 

Minty

AWF VIP
Local time
Today, 23:03
Joined
Jul 26, 2013
Messages
10,355
Perhaps you could post up an excel spreadsheet or sample database with some sample data?
 

sajarac

Registered User.
Local time
Today, 19:03
Joined
Aug 18, 2015
Messages
126
Hi, I'm only getting a single record because that person is the only person who meets both conditions. I mean that Sales Rep has an audit value in FY2019 and also has a jobvalue in FY2019.
 

Minty

AWF VIP
Local time
Today, 23:03
Joined
Jul 26, 2013
Messages
10,355
Ah that's the old data problem. You can only get the results your data actually has :)

You can remove the Salesperson field but It looked important in your original question.
 

sajarac

Registered User.
Local time
Today, 19:03
Joined
Aug 18, 2015
Messages
126
That is correct, I need the sales rep name to sort the table, what I am trying to achive is to see the results in the current fiscal year by sales rep, total sales between audits and jobs, but as you can see jobs are not always in the same fiscal year.
 

Minty

AWF VIP
Local time
Today, 23:03
Joined
Jul 26, 2013
Messages
10,355
I can't tell from your original post what you expect as a result from the data you supplied. You showed us sorted results but not an actual expected outcome based on the data provided.

So please give us 3 sample things - Starting data set, Your criteria , and Your expected result set, either in an Excel sheet or some made up Access tables. We'll need enough data to cover all the expected outcomes.
 

sajarac

Registered User.
Local time
Today, 19:03
Joined
Aug 18, 2015
Messages
126
Hi there, in the enclosed file you will see the first TAB is the query for the Audits in the FY2019 "AuditFY"
Secong TAB Jobs by FY2019 "JobFY"

Pivot TAB I've just copy and paste both tables to get a summary by Sales Rep

The highlighted are the guys who meet both conditions but I would like to see the same list sorted by largest to smallest value
 
Last edited:

Minty

AWF VIP
Local time
Today, 23:03
Joined
Jul 26, 2013
Messages
10,355
Seem to be missing the attachment ? ?
 

sajarac

Registered User.
Local time
Today, 19:03
Joined
Aug 18, 2015
Messages
126
Yes, sorry, file is greater than 2MB, and even in rar file doesn't fit.

Let me see what can I do.
 

Minty

AWF VIP
Local time
Today, 23:03
Joined
Jul 26, 2013
Messages
10,355
Okay , but before you get too far - I think we could do with the un-queried table data first, as it may be better to sub query that for your end result.

Make sure you compact and repair before trying to zip it.
 

Minty

AWF VIP
Local time
Today, 23:03
Joined
Jul 26, 2013
Messages
10,355
Looking at your data and your queries I have a feeling you're storing this in an obtuse or slightly difficult manner. Can you simply show us the table(s) involved fields please (all of them!).

And you can easily post the excel tables directly here they are well under the size limit for the site. A lot of people won't go to external sites to download things.
 

Users who are viewing this thread

Top Bottom