C
carissahodson
BACKGROUND
I conducted a project that awarded shipments to truckload carriers. I
need to track and compare these three things (monthly):
- the awarded carriers and their awarded percentages
- the carriers and percentages communicated to the facilities who are
using the carriers (should be the same as the awards, but might not)
in a "routing guide"
- the carriers and actual percentages shipped
I have these three pieces of information stored in these queries:
qryAwards
- Lane-Carrier (Key)
- PctAwarded
qryRoutingGuide
- Lane-Carrier-Period (Key)
- Lane-Carrier
- PctRequired
qryActual
- Lane-Carrier-Period (Key)
- Lane-Carrier
- PctActual
IDEAL OUTPUT
Lane-Carrier-Period PctAwarded PctRequired
PctActual
Lane1-CarrierA-0801 50%
30% 20%
Lane1-CarrierB-0801 50%
60% 60%
Lane1-CarrierC-0801 0%
10% 10%
Lane1-CarrierD-0801 0%
0% 10%
CHALLENGES
- Since the awards are good for a year, it was not necessary to have a
"Lane-Carrier-Period" field in the "qryAwards" query, but I can create
that field if it is easier to have it as a key in all of my queries.
- There could be "Lane-Carrier-Period" (LCP) records that exist in one
query and not the others. (eg, Carrier D does not exist in
"qryRoutingGuide" and Carriers C and D do not exist in "qryActual")
- I'd like the LCP records that do have corresponding records in the
other queries to match up so that the percentages can be viewed
across, in the output example above.
QUESTIONS
- I thought that maybe I needed to do a union query, but that will
only stack my results, correct?
- Would my final result come from a multiple-step process of creating
a Union Query to create a master list of LCPs and then use left joins
to add on each set of percentages?
- Is there another, simpler way of going about this?
PRAISE AND ACCOLLADES
.... to anyone that can help! Thanks!
I conducted a project that awarded shipments to truckload carriers. I
need to track and compare these three things (monthly):
- the awarded carriers and their awarded percentages
- the carriers and percentages communicated to the facilities who are
using the carriers (should be the same as the awards, but might not)
in a "routing guide"
- the carriers and actual percentages shipped
I have these three pieces of information stored in these queries:
qryAwards
- Lane-Carrier (Key)
- PctAwarded
qryRoutingGuide
- Lane-Carrier-Period (Key)
- Lane-Carrier
- PctRequired
qryActual
- Lane-Carrier-Period (Key)
- Lane-Carrier
- PctActual
IDEAL OUTPUT
Lane-Carrier-Period PctAwarded PctRequired
PctActual
Lane1-CarrierA-0801 50%
30% 20%
Lane1-CarrierB-0801 50%
60% 60%
Lane1-CarrierC-0801 0%
10% 10%
Lane1-CarrierD-0801 0%
0% 10%
CHALLENGES
- Since the awards are good for a year, it was not necessary to have a
"Lane-Carrier-Period" field in the "qryAwards" query, but I can create
that field if it is easier to have it as a key in all of my queries.
- There could be "Lane-Carrier-Period" (LCP) records that exist in one
query and not the others. (eg, Carrier D does not exist in
"qryRoutingGuide" and Carriers C and D do not exist in "qryActual")
- I'd like the LCP records that do have corresponding records in the
other queries to match up so that the percentages can be viewed
across, in the output example above.
QUESTIONS
- I thought that maybe I needed to do a union query, but that will
only stack my results, correct?
- Would my final result come from a multiple-step process of creating
a Union Query to create a master list of LCPs and then use left joins
to add on each set of percentages?
- Is there another, simpler way of going about this?
PRAISE AND ACCOLLADES
.... to anyone that can help! Thanks!