Query Data Not matching

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I run this query (qryISRtotGT150) and I get 10 unique records by my S/Q#:
SELECT CALL_COUNT_SUMMARY_BY_ISR.*
FROM CALL_COUNT_SUMMARY_BY_ISR
WHERE (((CALL_COUNT_SUMMARY_BY_ISR.scheduled)>150));

However when I run this Query (qryUPDTCallAllo>150) I get 10 records but
there should be more than 10 because 1 S/Q# has more than 1 route. Also the
query is only appending the last two ID's of the query above
INSERT INTO [Call_AllocationCHG>150] ( [Curr_S#], BX, SC, Rt )
SELECT qryISRtotGT150.salesman, Route_Master.BX, Route_Master.SC,
Route_Master.Rt
FROM qryISRtotGT150 INNER JOIN Route_Master ON qryISRtotGT150.salesman =
Route_Master.[S/Q#];
 
It looks like you are getting the salesman records from the "one" side (which
you said there were only 10).

Then, in the Append query, you are using query "qryISRtotGT150" to limit the
number of "salesman Number" records returned (to 10 in this case); you want
all salesman from "Route_Master", so the select clause should be using the
salesman FK "Route_Master".


Try creating a query using:

SELECT qryISRtotGT150.salesman, Route_Master.BX, Route_Master.SC,
Route_Master.Rt
FROM qryISRtotGT150 INNER JOIN Route_Master ON qryISRtotGT150.salesman =
Route_Master.[S/Q#];

How many records are returned? (I think it will be 10)


Create another query substituting "Route_Master.[S/Q#] " in place of
"qryISRtotGT150.salesman":

SELECT Route_Master.[S/Q#], Route_Master.BX, Route_Master.SC,
Route_Master.Rt
FROM qryISRtotGT150 INNER JOIN Route_Master ON qryISRtotGT150.salesman =
Route_Master.[S/Q#];

How many records are returned now? If it returns more than 10 records, is
the number closer to what you think should be returned?

If the second query returns the correct number of records, change the Append
query to

INSERT INTO [Call_AllocationCHG>150] ( [Curr_S#], BX, SC, Rt )
SELECT Route_Master.[S/Q#], Route_Master.BX, Route_Master.SC,
Route_Master.Rt
FROM qryISRtotGT150 INNER JOIN Route_Master ON qryISRtotGT150.salesman =
Route_Master.[S/Q#];


Warning: I haven't tried this, it is just a guess.
 
Back
Top