loisellec
asked on
Think with me for exception reporting
I am working on a project for the newspaper I work for. We have 2 systems, one for ordering ads and one for what makes the paper and how it's laid out (production system). We have moved production system data into the ordering system in custom tables. So I have tables with our order data and tables with our production data. Great!
Now how do I do an unmatched query where either dates don't match (ie... something was ordered but didn't make the production system) or publications don't match, or ad size doesn't match, or color does not match? Can this be done in Crystal Reports?
Now how do I do an unmatched query where either dates don't match (ie... something was ordered but didn't make the production system) or publications don't match, or ad size doesn't match, or color does not match? Can this be done in Crystal Reports?
Auric1983
Do you have any way of referencing the order to what was done on the production system? If you have some sort of key field then you could write some query's
ASKER
Yes, they both have an order number that joins. However... we want to also see if any orders didn't make it to the other system (so production would have no data) or any order in production that were not ordered in the ordering system. So Im not sure joining on any field will give us the results we need... unless through Crystal I need to write 5 reports or something.
I would do something like the following, at least for the non-existant orders.
select * from order table where order number not in (select * from production orders)
as for the other query you could just do an inner join and use some logic to evaluate if the size was the same etc.
ASKER
So logically it could not be done on the same report?
If I wrote a bunch of reports could I take one main report and list them all on it as subreports so all the info could be pulled at once? Something like that?
If I wrote a bunch of reports could I take one main report and list them all on it as subreports so all the info could be pulled at once? Something like that?
You can also use a LEFT OUTER JOIN to get the information that didn't make it.
mlmcc
mlmcc
ASKER
Yes, I could... and suppress the ones that match on other criteria... but what do I join on? I think I need a full outer join where I can get records from each table that are not in the other table. Does that make sense?
If they are all on one table i'd join on the order number :P
ASKER
If I linked orders to production by orderno with a left outer join how would I get records from the production table that are not in the orders table?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
GREAT! I needed some ideas.
The reason that happens is we have a dept that takes orders and pulls them down and lays out the paper (dummy). Sometimes a manager or sales rep will bring in a late ad and they will put it on the dummy of the paper, but the rep or manager will forget to order the ad in the ordering system after it does make the paper. Then ads get in the paper and run but were never ordered and never will be billed.
Sometimes ads won't make the paper because in the ordering system the rep will forget to take the ad off hold or they will order it for a section that is not in that days paper, or they will order it late and they didn't realize the dummy was already pulled and forget to tell the other dept to repull the dummy so the late order makes the paper. There are numerous other reasons for the mismatches, sometimes ad size ordered does not match the ad size that made the paper, or the page number it was ordered for is not the page number it appeared on, etc...
Its rare this happens but everyday accounting physically goes in and measures every ad in the paper and matches the actual physical paper to the ordering system to make sure everything matches, this is time consuming and outdated. If we can match the 2 systems developmentally then they will only have to check on stuff that comes out on an exception report.
The reason that happens is we have a dept that takes orders and pulls them down and lays out the paper (dummy). Sometimes a manager or sales rep will bring in a late ad and they will put it on the dummy of the paper, but the rep or manager will forget to order the ad in the ordering system after it does make the paper. Then ads get in the paper and run but were never ordered and never will be billed.
Sometimes ads won't make the paper because in the ordering system the rep will forget to take the ad off hold or they will order it for a section that is not in that days paper, or they will order it late and they didn't realize the dummy was already pulled and forget to tell the other dept to repull the dummy so the late order makes the paper. There are numerous other reasons for the mismatches, sometimes ad size ordered does not match the ad size that made the paper, or the page number it was ordered for is not the page number it appeared on, etc...
Its rare this happens but everyday accounting physically goes in and measures every ad in the paper and matches the actual physical paper to the ordering system to make sure everything matches, this is time consuming and outdated. If we can match the 2 systems developmentally then they will only have to check on stuff that comes out on an exception report.