How to use Total->count to build these 2 query?

[colinxt]

1. I have 2 tables that named [SHIPPING] and [TRANSACTION]. In the shipping table, there are transactionID and DateShipped fields; in the transaction table, there are transactionID, TransactionDate and Location fields. I want to create a query that counts all online orders(shows in the Location field) that took more than three days to ship. Named that TotalDelayed. How can I do this?

2. I also want to create a field that counts all online orders. Named that TotalShipped.

The reason I create these two queries is to calculate the percentage of TotalDelayed/TotalShipped. But I have tried tons of methods and only shows me the numerator is always larger than the denominator. I dont know what is wrong.

Thank you!

 

[HiTechCoach]

1) Iff(DateDiff("d", SHIPPING.DateShipped, TRANSACTION.TransactionDate) > 3, 1, 0 ) as DelayedShipping

This can be summed as TotalDelayed


2) IIF(TRANSACTION.Location = "Online", 1, 0 ) as OnlineTrx

This can be summed as TotalShipped

 

[colinxt]

1) Iff(DateDiff("d", SHIPPING.DateShipped, TRANSACTION.TransactionDate) > 3, 1, 0 ) as DelayedShipping

This can be summed as TotalDelayed


2) IIF(TRANSACTION.Location = "Online", 1, 0 ) as OnlineTrx

This can be summed as TotalShipped

Thank you for the answer.
The first one works, but the second one needs variables. Any other ideas?

 

[HiTechCoach]

Thank you for the answer.
The first one works, but the second one needs variables. Any other ideas?

Without more information about your database is really is have to say much more.

Based on the lack of information about your database, I was just offer suggestions that would hopefully give you some ideas.

Can you post a sample of your database?
If not, start with the SQL for your queries.

 

[colinxt]

Without more information about your database is really is have to say much more.

Based on the lack of information about your database, I was just offer suggestions that would hopefully give you some ideas.

Can you post a sample of your database?
If not, start with the SQL for your queries.

Problem solved. It seems like the relation between those two tables have something to do with it. Your method is ok. Thank you.

 

[HiTechCoach]

You're welcome.

Thanks for the update.

Glad you got it solved. Great job!