Query isn't returning all results

[Caleb]

Hello, I am designing a query that does three different things, It takes a
'Product SKU' table and a freshly imported 'orders' table. The query displays
only orders <1000000000 (necessary, these orders arent shipped because
they're recurring). The query also only displays orders that have been
Accepted in the ApproveStatus field. Lastly, I need the query to add the
fields Purchased and NextPurchase from the 'Product SKU' table. Im not sure
why the code here is the way it is, I originally set the criteria for
Purchased and NextPurchase as something else (dont remember what now) and it
worked but after saving and closing I reopened and discovered that a new
field had been added to take care of that instead of my original code. The
new field is taken from the Fresh Imports (orders) table and the criteria is:

[Product SKU]![sku] And [Product SKU]![sku]

But this makes no sence to me. I tried getting rid of everything after AND
but it broke the query.

So anyway my real problem is after running my query the number of
Accepted/Non-Recurring orders that also display the product Purchased and the
recommended NextPurchase doesn't match the number of records that should be
left. About 50 orders disapeared, orders that match all the criteria
(<1000000000), and (Like "*Accepted*")

Thanks in advance for any help. Please do ask for more specific info if I
wasn't clear enough.

 

[Caleb]

Almost forgot... Here is the SQL for the query.

 

[Caleb]

I just checked some numbers... The Fresh Imports (daily orders) contains 284
records that I want. When I run the query with only the <1000000000, and
"*Accepted*" and not the Purchased and NextPurchase fields, the query return
the correct amount 284. However when I try to run the same query with the
Purchased and NextPurchase fields and that strange [Product SKU]![sku] And
[Product SKU]![sku] I only get 253 Records returned. Now this part of the
query uses the SKU field in the SKU table to identify which of these records
get which Purchased and NextPurchase entrys. Now sometimes orders in the
fresh imports (daily orders) table have no sku (which only happenes when
someone hasnt updated the list to include new products.) These orders do not
show up in the query due to the criteria i have set aparently. Firstly, I'd
like to include these orders (every one without a sku simply has this string
"(no sku)" instead and secondly back to my numbers: 35 records have no SKU,
but I'm only missing 31 records? I double checked and there are no orders
listed in the final query that have no sku so it can't be that a few of those
are getting through.
Hope this helped a little

 

[Caleb]

And yet again I forgot to post the SQL, here it is:

SELECT [Fresh Import From 1ShoppingCart].OrderId, [Fresh Import From
1ShoppingCart].Email, [Fresh Import From 1ShoppingCart].FirstName, [Fresh
Import From 1ShoppingCart].LastName, [Fresh Import From
1ShoppingCart].ApproveStatus, [Fresh Import From 1ShoppingCart].Phone, [Fresh
Import From 1ShoppingCart].OrderDate, [Fresh Import From
1ShoppingCart].ProductsOrdered, [Fresh Import From 1ShoppingCart].SKU, [Fresh
Import From 1ShoppingCart].Total, [Fresh Import From
1ShoppingCart].ShipToName, [Fresh Import From 1ShoppingCart].ShipToAddress1,
[Fresh Import From 1ShoppingCart].ShipToAddress2, [Fresh Import From
1ShoppingCart].ShipToCity, [Fresh Import From 1ShoppingCart].ShipToState,
[Fresh Import From 1ShoppingCart].ShipToZip, [Fresh Import From
1ShoppingCart].ShipToCountry, [Fresh Import From 1ShoppingCart].HowHeard,
[Fresh Import From 1ShoppingCart].price, [Product SKU].Purchased, [Product
SKU].NextPurchase
FROM [Fresh Import From 1ShoppingCart], [Product SKU]
WHERE ((([Fresh Import From 1ShoppingCart].OrderId)<1000000000) AND (([Fresh
Import From 1ShoppingCart].ApproveStatus) Like "*Accepted*") AND (([Fresh
Import From 1ShoppingCart]![SKU])=[Product SKU]![sku] And ([Fresh Import From
1ShoppingCart]![SKU])=[Product SKU]![sku]));