Is this possible?

[jasons]

Hi,

This is my first post but I've used your forum many times to get myself out of jams primarily by using the search function. I have a snag the search function hasn't been able to help me with so any direction or help you can provide would be tremendous.

I've wrote an access db for an appliance delivery company that tracks parts used, delivery charges, delivery driver, etc. This db is poplulated via a csv file that is sent from the company's vendor. I will explain why this is important in a moment. During validation I came across a snag that will be hard to explain so I'm attaching a screenshot of the query output and the code to help explain.

The query output in the screenshot has a count of each description grouped by driver. Each count is accurate except for the M100 count which is the company's internal code for delivery charge. Turns out the company only charges one delivery charge when mulitple items are delivered to the same address. The csv import however has a delivery charge for every item regardless of the address. Of course I have to iron out this little snag. My original though was to use a distinct count on the address field of the database which would eliminate multiple delivery charges to the same address. But this would throw off the count when the same part is used multiple times at the same address. Not good. So I would need to do a distinct count on address when the description is M100.

My question is that possible as the client wants this all on one report or will have to create a seperate report just for delivery charges where I can use a distinct count?

Here is the code for the query.

SELECT Main.Driver, Main.Date, Main.Description, Count([MS revenue].Description) AS CountOfDescription, Sum([MS revenue].Cost) AS SumOfCost, [Employee Percentage].[Revenue Percentage], Sum([Employee Percentage]![Revenue Percentage]*[MS revenue]!Cost) AS [Employee Revenue], [MS revenue].Cost, [Employee Percentage].[Employee Number], [MS revenue].[M Code], Main.Rescheduled
FROM (Main INNER JOIN [MS revenue] ON Main.Description = [MS revenue].Description) INNER JOIN [Employee Percentage] ON Main.Driver = [Employee Percentage].Employee
GROUP BY Main.Driver, Main.Date, Main.Description, [Employee Percentage].[Revenue Percentage], [MS revenue].Cost, [Employee Percentage].[Employee Number], [MS revenue].[M Code], Main.Rescheduled
HAVING (((Main.Date) Between [Enter Start Date (12/1/09)] And [Enter End Date (1/1/10)]) AND ((Main.Rescheduled) Is Null) AND (([Main]![Driver]) Is Not Null))
ORDER BY Main.Driver, Main.Description;

Any help or direction is appreciated as I'm truly stuck.

Regards,

Jason

 

[georgedwilkinson]

You could use an Iif() function around your Count() function or you could use a union query.

 

[jasons]

Thanks for the direction George. I tried using the iif function previously but could never get it to work without errors. I assumed it wouldn't do what I needed which is perform a select statement if description = "M100" and perform different select statement if the condition was false.

The distinct count code I am using when description="M100" is

SELECT street, Count(t1.street) AS CountOfDescription
FROM [select Distinct street from Main group by street]. AS t1
GROUP BY street
ORDER BY street;

This code works just fine but can't get it to work within the iif() function.

Do you mind helping me get the iif() function working in the code from my first post as I can't seem to get past this last snag to finish the project?


Thanks again,

Jason

 

[georgedwilkinson]

You're right, you can't use a non-aggregating function in place of an aggregate function.

Try:

Select * from
(
SELECT street, Count(*) AS CountOfDescription
FROM Main AS t1
where description <> 'M100'
GROUP BY street
UNION
SELECT street, 1 AS CountOfDescription
FROM Main AS t1
where description = 'M100'
)
order by street

Hopefully that'll get you closer.

 

[jasons]

Thanks again George. With your direction I was able to build a Union query which resulted in the output I was looking for. To simply things I used a limited set of data and queried on minimal fields and worked on the code until it came out right. The only issue I have outstanding is merging the new query with the existing query. I keep getting a sytax error in the join operation so I am missing something in the join. Can you spot it for me?

Here is the new code that works with a limited set of data.

[SIZE=3][FONT=Times New Roman]SELECT Main.Driver, Main.Description, Count(Main.Description) AS CountOfDescription[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]FROM Main[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]GROUP BY Main.Driver, Main.Description[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]HAVING (((Main.Description)<>'M100'));[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]UNION[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]SELECT Main.Driver, Main.Description, Count(* ) AS CountOfDescription[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]FROM (SELECT distinct street AS N, description, driver[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]FROM Main[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]where description = 'M100')[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]group by driver, description[/FONT][/SIZE]

Here is the old code I need to merge the above code with.

SELECT Main.Driver, Main.Date, Main.Description, Count([MS revenue].Description) AS CountOfDescription, Sum([MS revenue].Cost) AS SumOfCost, [Employee Percentage].[Revenue Percentage], Sum([Employee Percentage]![Revenue Percentage]*[MS revenue]!Cost) AS [Employee Revenue], [MS revenue].Cost, [Employee Percentage].[Employee Number], [MS revenue].[M Code], Main.Rescheduled
FROM (Main INNER JOIN [MS revenue] ON Main.Description = [MS revenue].Description) INNER JOIN [Employee Percentage] ON Main.Driver = [Employee Percentage].Employee
GROUP BY Main.Driver, Main.Date, Main.Description, [Employee Percentage].[Revenue Percentage], [MS revenue].Cost, [Employee Percentage].[Employee Number], [MS revenue].[M Code], Main.Rescheduled
HAVING (((Main.Date) Between [Enter Start Date (12/1/09)] And [Enter End Date (1/1/10)]) AND ((Main.Rescheduled) Is Null) AND (([Main]![Driver]) Is Not Null))
ORDER BY Main.Driver, Main.Description;

Here is what I tried that fails with a sytax error in join operation.

[SIZE=3][FONT=Times New Roman]SELECT Main.Driver, Main.Date, Main.Description, Count([MS revenue].Description) AS CountOfDescription, Sum([MS revenue].Cost) AS SumOfCost, [Employee Percentage].[Revenue Percentage], Sum([Employee Percentage]![Revenue Percentage]*[MS revenue]!Cost) AS [Employee Revenue], [MS revenue].Cost, [Employee Percentage].[Employee Number], [MS revenue].[M Code], Main.Rescheduled[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]FROM (Main INNER JOIN [MS revenue] ON Main.Description = [MS revenue].Description) INNER JOIN [Employee Percentage] ON Main.Driver = [Employee Percentage].Employee[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]GROUP BY Main.Driver, Main.Date, Main.Description, [Employee Percentage].[Revenue Percentage], [MS revenue].Cost, [Employee Percentage].[Employee Number], [MS revenue].[M Code], Main.Rescheduled[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]HAVING (((Main.Date) Between [Enter Start Date (12/1/09)] And [Enter End Date (1/1/10)]) AND ((Main.Rescheduled) Is Null) AND (([Main]![Driver]) Is Not Null)) AND (((Main.Description)<>'M100'))[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]ORDER BY Main.Driver, Main.Description;[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]UNION[/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]SELECT Main.Driver, Main.Date, Main.Description, Count(*) AS CountOfDescription, Sum([MS revenue].Cost) AS SumOfCost, [Employee Percentage].[Revenue Percentage], Sum([Employee Percentage]![Revenue Percentage]*[MS revenue]!Cost) AS [Employee Revenue], [MS revenue].Cost, [Employee Percentage].[Employee Number], [MS revenue].[M Code], Main.Rescheduled[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]FROM (SELECT distinct street AS N, description, driver, date from Main INNER JOIN [MS revenue] ON Main.Description = [MS revenue].Description) INNER JOIN [Employee Percentage] ON Main.Driver = [Employee Percentage].Employee[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]GROUP BY Main.Driver, Main.Date, Main.Description, [Employee Percentage].[Revenue Percentage], [MS revenue].Cost, [Employee Percentage].[Employee Number], [MS revenue].[M Code], Main.Rescheduled[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]HAVING (((Main.Date) Between [Enter Start Date (12/1/09)] And [Enter End Date (1/1/10)]) AND ((Main.Description)='M100') AND ((Main.Rescheduled) Is Null) AND (([Main]![Driver]) Is Not Null))[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]ORDER BY Main.Driver, Main.Description;[/SIZE][/FONT]

I tried the query before the union by itself and it works fine so it has to do with the second query after the union in particular the nested select statement that calls the distinct street so that it may be counted. I'm sure the syntax I am missing is apparent but I can't find it.

Thanks,

Jason