Validating sales records against campaign reference data

[Guest]

Hi there - and many thanks for reading (and helping hopefully...)

I have some reference data regarding what sorts of offers can be given out
during sales calls that looks like this:

Campaign Offer_No
A 1000
A 1250
B 1345
C 1500

So - you can see that for campaign A, two offers being applied is valid,
whereas only one offer can be applied for campaigns B and C.

My sale records data then looks like this:

Cust ID Salesman ID Sale Number Offer
001 Jack 001 1000
001 Jack 001 1250
002 Jim 002 1345
002 Jim 002 1000

Reading the sales data, you can see that Jack made a valid sale using
Campaign A, but Jim has made an invalid sale as his combination of offers
isn't one that is in the campaign reference table.

Any ideas at all how I can do that process autmiatically in Access? The
range of offers and their combination into campaigns changes quite a lot as
well, so whatever solution I find needs to be done autimatically from the
input data - I cannot rebuild queries each time I add a new offer or campaign.

Many Many Thanks
D

 

[Neil Sunderland]

I have some reference data regarding what sorts of offers can be given out
during sales calls that looks like this:

Campaign Offer_No
A 1000
A 1250
B 1345
C 1500

So - you can see that for campaign A, two offers being applied is valid,
whereas only one offer can be applied for campaigns B and C.

My sale records data then looks like this:

Cust ID Salesman ID Sale Number Offer
001 Jack 001 1000
001 Jack 001 1250
002 Jim 002 1345
002 Jim 002 1000

Reading the sales data, you can see that Jack made a valid sale using
Campaign A, but Jim has made an invalid sale as his combination of offers
isn't one that is in the campaign reference table.

Any ideas at all how I can do that process autmiatically in Access? The
range of offers and their combination into campaigns changes quite a lot as
well, so whatever solution I find needs to be done autimatically from the
input data - I cannot rebuild queries each time I add a new offer or campaign.

You need to do it as two queries. First get the list of sales and
their campaigns:

SELECT
S.Cust_ID, S.Salesman_ID, S.Sale_Number, S.Campaign
FROM
sales_data AS S
INNER JOIN
campaigns AS C ON S.Offer = C.Offer_No
GROUP BY
S.Cust_ID, S.Salesman_ID, S.Sale_Number, S.Campaign

=========================================
Cust_ID Salesman_ID Sale_Number Campaign
1 Jack 1 A
2 Jim 2 A
2 Jim 2 B
=========================================

Then you need to find all the sales which have more than one campaign:

SELECT
Cust_ID, Salesman_ID, Sale_Number
FROM
qrySales
GROUP BY
Cust_ID, Salesman_ID, Sale_Number
HAVING
Count(qrySales.Sale_Number)>1;

=========================================
Cust_ID Salesman_ID Sale_Number
2 Jim 2
=========================================

 

[Guest]

Neil - many thanks - I clearly ommitted to make it clear that some offers can
be used in several campaigns - which I thnk blows out your process of finding
sales where offers can arise from multiple campaigns - as this is actaully a
valid circumstance. Have updated the reference and example data to show what
I mean more clearly:

Reference data

Campaign Offer_No
A 1000
A 1250
B 1345
B 1000
C 1500

Sales data

Cust ID Salesman ID Sale Number Offer
001 Jack 001 1000
001 Jack 001 1250
002 Jim 002 1345
002 Jim 002 1000
003 Rob 003 1000
003 Rob 003 1500

Now we have sale 001 being valid as it uses the offers for campaign A, 002
being valid as it is campaign B but sale 003 being invalid as its offers are
not consistent with any campaign in the reference table.

Hope that more clearly explains the problem.

Thanks once more
D

 

[Neil Sunderland]

Neil - many thanks - I clearly ommitted to make it clear that some offers can
be used in several campaigns

You rotten swine, you

- which I thnk blows out your process of finding
sales where offers can arise from multiple campaigns - as this is actaully a
valid circumstance.

You only need to change the process slightly - this time, it's three
queries.

First, combine the sales with the campaigns:

SELECT
S.Cust_ID, S.Salesman_ID, S.Sale_Number, S.Offer, S.Campaign
FROM
sales_data AS S
INNER JOIN
campaigns AS C ON S.Offer = C.Offer_No
GROUP BY
S.Cust_ID, S.Salesman_ID, S.Sale_Number, S.Offer, S.Campaign

==============================================
Cust_ID Salesman_ID Sale_Number Offer Campaign
1 Jack 1 1000 A
1 Jack 1 1000 B
1 Jack 1 1250 A
2 Jim 2 1000 A
2 Jim 2 1000 B
2 Jim 2 1345 B
3 Rob 3 1000 A
3 Rob 3 1000 B
3 Rob 3 1500 C
==============================================

I've included the offer column, because we will have to compare the
number of rows for each campaign with the number of rows for each sale
in the next step.

Second, get all the sales which are valid:

SELECT
Cust_ID, Salesman_ID, Sale_Number, Campaign,
FROM
qrySales
GROUP BY
Cust_ID, Salesman_ID, Sale_Number, Campaign,
HAVING Count(qrySales.Campaign) = (
SELECT
Count(*)
FROM
sales_data AS S
WHERE qrySales.Sale_number = S.Sale_number)

========================================
Cust_ID Salesman_ID Sale_Number Campaign
1 Jack 1 A
2 Jim 2 B
========================================

Then the invalid ones are all the ones that aren't valid:

SELECT
Cust_ID, Salesman_ID, Sale_Number, Offer
FROM
sales_data
WHERE
Sale_Number Not In (SELECT Sale_Number FROM qryValidSales)

========================================
Cust_ID Salesman_ID Sale_Number Offer
3 Rob 3 1000
3 Rob 3 1500
========================================

 

[Guest]

You STAR!

You rotten swine, you


You only need to change the process slightly - this time, it's three
queries.

First, combine the sales with the campaigns:

SELECT
S.Cust_ID, S.Salesman_ID, S.Sale_Number, S.Offer, S.Campaign
FROM
sales_data AS S
INNER JOIN
campaigns AS C ON S.Offer = C.Offer_No
GROUP BY
S.Cust_ID, S.Salesman_ID, S.Sale_Number, S.Offer, S.Campaign

==============================================
Cust_ID Salesman_ID Sale_Number Offer Campaign
1 Jack 1 1000 A
1 Jack 1 1000 B
1 Jack 1 1250 A
2 Jim 2 1000 A
2 Jim 2 1000 B
2 Jim 2 1345 B
3 Rob 3 1000 A
3 Rob 3 1000 B
3 Rob 3 1500 C
==============================================

I've included the offer column, because we will have to compare the
number of rows for each campaign with the number of rows for each sale
in the next step.

Second, get all the sales which are valid:

SELECT
Cust_ID, Salesman_ID, Sale_Number, Campaign,
FROM
qrySales
GROUP BY
Cust_ID, Salesman_ID, Sale_Number, Campaign,
HAVING Count(qrySales.Campaign) = (
SELECT
Count(*)
FROM
sales_data AS S
WHERE qrySales.Sale_number = S.Sale_number)

========================================
Cust_ID Salesman_ID Sale_Number Campaign
1 Jack 1 A
2 Jim 2 B
========================================

Then the invalid ones are all the ones that aren't valid:

SELECT
Cust_ID, Salesman_ID, Sale_Number, Offer
FROM
sales_data
WHERE
Sale_Number Not In (SELECT Sale_Number FROM qryValidSales)

========================================
Cust_ID Salesman_ID Sale_Number Offer
3 Rob 3 1000
3 Rob 3 1500
========================================

--
Neil Sunderland
Braunton, Devon

Please observe the Reply-To address