Query doesn't pull every record from table

[ankrumc]

Hi,

My query is pulling about half of the records it should from the corresponding table. I don't think there is a filter in between the two, but if this sounds like an oversight on my part, fire away. Are there any other reasons for this?

Also, it seems to be cutting off after about 600, and there are about 1000.

Thanks!

 

[HiTechCoach]

Do you have multiple tables in your query? If yes, is this a one-to-many relationships where the table on the one side has at least one record in the many side's related table.

 

[ankrumc]

Umm, just 2 actually. I have the main table for the whole database, and I'm using three fields from that one, and then a side (sub) table that has a different set of fields. I didn't want to bog down the main table, and maybe that's what I have to do.

All the tables track back to the ID on the original table.

Thanks!

 

[ankrumc]

Ok, I'm seeming to find that I'm limited to 700 records in this query, is that possible?

 

[wiklendt]

what you need to do is double click the join line between the two tables (edit: this is in the design view of your query). you will see a dialog pop up and at the bottom half you have three options - these are ways to join the tables.

make sure that you choose the option which says to show all the records from your MainTable (whatever it may be called), and only those from you SubTable whose recrods match.

at the moment, it sounds like you've got jsut the default join, which is to display only records which are matches on both sides... so if a record in MainTable doesn't have a record in SubTable, it's not displayed.

choosing the correct option, you should see ALL the records form your MainTable and any that match in the SubTable.

 

[ankrumc]

Thanks! That was the trick. I tried to copy the fields before but it would then erase more, but that was the fix I needed!

 

[mushrush]

i'm trying to analyze my company's inventory purchasing history. i've created a query using an inventory master file table (~ 200 items) & a table that will summarized receipt activity on any of those 200 items.

i would like to the query to produce all 200 items regardless if they have activity or not. but it only produces those items w/ activity, ~ 50 items. i tried changing the join property as recommended but to no avail as it still produces the same 50.

i'm new to the forum so i don't know if a screen print of my query would help in resolving. any assistance would be much appreciated. thank you.

 

[wiklendt]

mushrush, a screen shot of your query design may be helpful, or posting the SQL of the query (in the "view" button you can select many different views of the query, including design and SQL) and make sure you select the SQL you've pasted, then hit the hash icon ("#") in your forum message toolbar, to make it easier to read.

make sure you have changed the join property to go the correct way. if you are unsure, change it anyway and see what happens. in any case, the correct 'way' would be for the arror head to point AWAY from the table you want everything to appear from.

 

[mushrush]

wiklendt - thank you for your reply. i tried your suggestion & still no success. as requested, i have posted the SQL below. i have also included a print screen of the query as an attachment. i hope this helps. thank you.

SELECT Inventory_QOH.[Item#], Inventory_QOH.ItemDescription, Sum(IM5_TransactionDetail.TransactionQty) AS QtyRec
FROM IM5_TransactionDetail RIGHT JOIN Inventory_QOH ON IM5_TransactionDetail.ItemNumber = Inventory_QOH.[Item#]
WHERE (((IM5_TransactionDetail.TransactionCode)="PO") AND ((IM5_TransactionDetail.TransactionDate) Between [start date] And [end date]))
GROUP BY Inventory_QOH.[Item#], Inventory_QOH.ItemDescription;

 

[wiklendt]

have you tried removing the criteria to see if that is the cause? i also see you are GROUPING your data. this would group all the activity for one item down to one item. (edit: this can happen even more 'alarmingly' when you chose NOT do display certain fields (i.e., you date field and transaction code field)

essentially, what you are doing is just listing the items, but you are also restricting them by a date and "PO" values. you may want to see if something like this would help:

Like "PO" Or Is Null

...?

the query looks like you're restricting the results, so it's no wonder they aren't all 200 showing...

try working back to see where the truncation of the data occurs. first just to a plain select query with no grouping and no criteria. then one by one add your other requirements and see where access is making the restricitons.