duplicate data in column in union query

G

Guest

Hi all.
Once a month our department needs to generate a report for the purchase of
new vehicles. To complicate matters each make of vehicle needs to be split
by a certain percentage between dealerships. I have been told that VB code
could be the way to go, but I've tried a union query at this stage.

SELECT DISTINCT TOP 50 PERCENT Vehicle_Purchase.Serial_new,
Vehicle_Purchase.Serial_old, Vehicle_Purchase.[Vehicle Types],
Vehicle_Purchase.Revised_lease_period, Vehicle_Purchase.new_veh_colour,
Vehicle_Purchase.Order_Month, Vehicle_Purchase.addit_options,
Vehicle_Purchase.addit_options1, Vehicle_Purchase.addit_options2,
Vehicle_Purchase.addit_options3, "Jubilee Ford" AS Expr1
FROM Vehicle_Purchase
WHERE (((Vehicle_Purchase.[Vehicle Types]) Like "for*") AND
((Vehicle_Purchase.Order_Month) Between [start date] And [end date] And
(Vehicle_Purchase.Order_Month)<=[end date] And
(Vehicle_Purchase.Order_Month)>=[start date]))
ORDER BY Vehicle_Purchase.Serial_new DESC
UNION SELECT DISTINCT TOP 50 PERCENT Vehicle_Purchase.Serial_new,
Vehicle_Purchase.Serial_old, Vehicle_Purchase.[Vehicle Types],
Vehicle_Purchase.Revised_lease_period, Vehicle_Purchase.new_veh_colour,
Vehicle_Purchase.Order_Month, Vehicle_Purchase.addit_options,
Vehicle_Purchase.addit_options1, Vehicle_Purchase.addit_options2,
Vehicle_Purchase.addit_options3, "Alto Ford" AS Expr1
FROM Vehicle_Purchase
WHERE (((Vehicle_Purchase.[Vehicle Types]) Like "for*") AND
((Vehicle_Purchase.Order_Month) Between [start date] And [end date] And
(Vehicle_Purchase.Order_Month)<=[end date] And
(Vehicle_Purchase.Order_Month)>=[start date]))
ORDER BY Vehicle_Purchase.Serial_new ASC;


Ford Purchase
Serial_new Serial_old Vehicle Types
69176 66145 FORD FALCON SEDAN Alto Ford
69176 66145 FORD FALCON SEDAN Jubilee Ford

Sorry for the jumbled last part, but as you can see there is a duplication
of data in the serial_new column. This occurs in the middle of the query at
the 50/50 split. Obviously we can't be purchasing 2 cars to replace 1,
although the manufacturer wouldn't mind :)
Any ideas would be welcome
Regards
Brad
 
R

Rob Oldfield

What determines which one of the duplicates you actually want to buy? If
there's a definite rule e.g. always go for the Alto instead of the Jubilee
then you can just run a group query to find duplicates and delete the
Jubilee records. If there's not then you could create a make table based on
your union then pop a message up saying "69176 is duplicated. Please delete
one." You would be needing to use some code in the second scenario, but
nothing too complex.

What's your ideal situation?
 
G

Guest

Thanks for the quick reply Rob.
There is no hard and fast rule. The query I posted returned 54 vehicles for
the month for Ford. It was only the 2 records shown that returned a
duplicate, so I would only need to remove one for the purchase report.
However for Holden(GMH) the split is 65/15/20 between 3 dealers, so do you
think that a union query is the most efficient/accurate or should I be
looking at some other method? I'm fairly new to Access so I may have bitten
off more than I can chew. As a side note, I'm seting this up for other
people to use, who are not somputer savvy, so the least they have to do the
better.

Regards
Brad

Rob Oldfield said:
What determines which one of the duplicates you actually want to buy? If
there's a definite rule e.g. always go for the Alto instead of the Jubilee
then you can just run a group query to find duplicates and delete the
Jubilee records. If there's not then you could create a make table based on
your union then pop a message up saying "69176 is duplicated. Please delete
one." You would be needing to use some code in the second scenario, but
nothing too complex.

What's your ideal situation?


Ruggo said:
Hi all.
Once a month our department needs to generate a report for the purchase of
new vehicles. To complicate matters each make of vehicle needs to be split
by a certain percentage between dealerships. I have been told that VB code
could be the way to go, but I've tried a union query at this stage.

SELECT DISTINCT TOP 50 PERCENT Vehicle_Purchase.Serial_new,
Vehicle_Purchase.Serial_old, Vehicle_Purchase.[Vehicle Types],
Vehicle_Purchase.Revised_lease_period, Vehicle_Purchase.new_veh_colour,
Vehicle_Purchase.Order_Month, Vehicle_Purchase.addit_options,
Vehicle_Purchase.addit_options1, Vehicle_Purchase.addit_options2,
Vehicle_Purchase.addit_options3, "Jubilee Ford" AS Expr1
FROM Vehicle_Purchase
WHERE (((Vehicle_Purchase.[Vehicle Types]) Like "for*") AND
((Vehicle_Purchase.Order_Month) Between [start date] And [end date] And
(Vehicle_Purchase.Order_Month)<=[end date] And
(Vehicle_Purchase.Order_Month)>=[start date]))
ORDER BY Vehicle_Purchase.Serial_new DESC
UNION SELECT DISTINCT TOP 50 PERCENT Vehicle_Purchase.Serial_new,
Vehicle_Purchase.Serial_old, Vehicle_Purchase.[Vehicle Types],
Vehicle_Purchase.Revised_lease_period, Vehicle_Purchase.new_veh_colour,
Vehicle_Purchase.Order_Month, Vehicle_Purchase.addit_options,
Vehicle_Purchase.addit_options1, Vehicle_Purchase.addit_options2,
Vehicle_Purchase.addit_options3, "Alto Ford" AS Expr1
FROM Vehicle_Purchase
WHERE (((Vehicle_Purchase.[Vehicle Types]) Like "for*") AND
((Vehicle_Purchase.Order_Month) Between [start date] And [end date] And
(Vehicle_Purchase.Order_Month)<=[end date] And
(Vehicle_Purchase.Order_Month)>=[start date]))
ORDER BY Vehicle_Purchase.Serial_new ASC;


Ford Purchase
Serial_new Serial_old Vehicle Types
69176 66145 FORD FALCON SEDAN Alto Ford
69176 66145 FORD FALCON SEDAN Jubilee Ford

Sorry for the jumbled last part, but as you can see there is a duplication
of data in the serial_new column. This occurs in the middle of the query at
the 50/50 split. Obviously we can't be purchasing 2 cars to replace 1,
although the manufacturer wouldn't mind :)
Any ideas would be welcome
Regards
Brad
Click to expand...
Click to expand...
 
R

Rob Oldfield

Without knowing more about your business, it's impossible to say whether the
union query is the most efficient. It actually comes down to the results
that you're looking for as defined by your business.

I'd guess though that if, as you say, "there is no hard and fast rule" then
you're probably going to be looking for 'too many' results from whatever
query or method you come up with, and then allowing a user to go through and
make judgement calls on whether to go for the Jubilee or the Alto. If
that's right then the method that you've come with seems to do the job
pretty well. If you wanted to fine tune it to get your method to come out
with the exact number required then you would have to apply some definite
rules in there somewhere.

I'd tend to suggest that you take your results (from a few different
scenarios) and see what your colleagues think of the output that you're
getting. If they agree that your way is good, you can then move on to
building some forms that allow users to (easily) view and choose between
duplicates.


Ruggo said:
Thanks for the quick reply Rob.
There is no hard and fast rule. The query I posted returned 54 vehicles for
the month for Ford. It was only the 2 records shown that returned a
duplicate, so I would only need to remove one for the purchase report.
However for Holden(GMH) the split is 65/15/20 between 3 dealers, so do you
think that a union query is the most efficient/accurate or should I be
looking at some other method? I'm fairly new to Access so I may have bitten
off more than I can chew. As a side note, I'm seting this up for other
people to use, who are not somputer savvy, so the least they have to do the
better.

Regards
Brad

Rob Oldfield said:
What determines which one of the duplicates you actually want to buy? If
there's a definite rule e.g. always go for the Alto instead of the Jubilee
then you can just run a group query to find duplicates and delete the
Jubilee records. If there's not then you could create a make table based on
your union then pop a message up saying "69176 is duplicated. Please delete
one." You would be needing to use some code in the second scenario, but
nothing too complex.

What's your ideal situation?


Ruggo said:
Hi all.
Once a month our department needs to generate a report for the purchase of
new vehicles. To complicate matters each make of vehicle needs to be split
by a certain percentage between dealerships. I have been told that VB code
could be the way to go, but I've tried a union query at this stage.

SELECT DISTINCT TOP 50 PERCENT Vehicle_Purchase.Serial_new,
Vehicle_Purchase.Serial_old, Vehicle_Purchase.[Vehicle Types],
Vehicle_Purchase.Revised_lease_period, Vehicle_Purchase.new_veh_colour,
Vehicle_Purchase.Order_Month, Vehicle_Purchase.addit_options,
Vehicle_Purchase.addit_options1, Vehicle_Purchase.addit_options2,
Vehicle_Purchase.addit_options3, "Jubilee Ford" AS Expr1
FROM Vehicle_Purchase
WHERE (((Vehicle_Purchase.[Vehicle Types]) Like "for*") AND
((Vehicle_Purchase.Order_Month) Between [start date] And [end date] And
(Vehicle_Purchase.Order_Month)<=[end date] And
(Vehicle_Purchase.Order_Month)>=[start date]))
ORDER BY Vehicle_Purchase.Serial_new DESC
UNION SELECT DISTINCT TOP 50 PERCENT Vehicle_Purchase.Serial_new,
Vehicle_Purchase.Serial_old, Vehicle_Purchase.[Vehicle Types],
Vehicle_Purchase.Revised_lease_period, Vehicle_Purchase.new_veh_colour,
Vehicle_Purchase.Order_Month, Vehicle_Purchase.addit_options,
Vehicle_Purchase.addit_options1, Vehicle_Purchase.addit_options2,
Vehicle_Purchase.addit_options3, "Alto Ford" AS Expr1
FROM Vehicle_Purchase
WHERE (((Vehicle_Purchase.[Vehicle Types]) Like "for*") AND
((Vehicle_Purchase.Order_Month) Between [start date] And [end date] And
(Vehicle_Purchase.Order_Month)<=[end date] And
(Vehicle_Purchase.Order_Month)>=[start date]))
ORDER BY Vehicle_Purchase.Serial_new ASC;


Ford Purchase
Serial_new Serial_old Vehicle Types
69176 66145 FORD FALCON SEDAN Alto Ford
69176 66145 FORD FALCON SEDAN Jubilee Ford

Sorry for the jumbled last part, but as you can see there is a duplication
of data in the serial_new column. This occurs in the middle of the
Click to expand...
query
at
the 50/50 split. Obviously we can't be purchasing 2 cars to replace 1,
although the manufacturer wouldn't mind :)
Any ideas would be welcome
Regards
Brad
Click to expand...
Click to expand...
Click to expand...
 
Top