Select Query Not Returning All Records (1 Viewer)

[DeansOnToast]

Was investigating an issue where a query based on form parameters were not returning all relevant records and found it went one level worse and even a new select query with no criteria just passing the table through doesn't even return all the records.

I'm confident its a rookie error as in the table design I have no primary key set but would prefer someone to call me stupid than be stuck with this issue

edit: just for some context the table contains ~1500 records but is only returning 927 of them

 

[plog]

Can you post the SQL?

Educated guesses:

1. You are using an INNER JOIN and it is acting as criteria, limiting the resulting records to just those that match between the two tables.

2. It is an aggregate query (using GROUP BY) and you have essentially duplicate records which roll together in the results.

 

[DeansOnToast]

SELECT [table1].FORMNUMBER,[table1].PARTNO,[table1].PARTDESC,[table1].COST,[table1].QTY
FROM [table1] INNER JOIN [table1] ON [BoM].ManufactuererPartNo = [table1].PARTNO;

Removed some identifying info just for safety sake, and seems it is an inner join. Was intending to use the relation as a way to grab additional specs about the part but seems I'm using it wrong. what's the correct way to state a one sided relation. Seems I should ask work to be put on an access course.

 

[MajP]

SQL LEFT JOIN Keyword

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

This site is a great resource.

 

[CJ_London]

this part will never work

FROM [table1] INNER JOIN [table1] ON [BoM].ManufactuererPartNo = [table1].PARTNO;

it should be

FROM [table1] INNER JOIN [BoM] ON [BoM].ManufactuererPartNo = [table1].PARTNO;

but change INNER to LEFT and it should return all records in table1

 

[plog]

1. Identify info? You're it doing wrong. Names in a database should be generic. Tables and fields should be named after common words. Identifying data should be values in the table not names of tables or fields.

2. Why's BOM even in that query? You're not using any of it's fields for any purpose .

 

[DeansOnToast]

1. Identify info? You're it doing wrong. Names in a database should be generic. Tables and fields should be named after common words. Identifying data should be values in the table not names of tables or fields.

2. Why's BOM even in that query? You're not using any of it's fields for any purpose .

1. BoM is Bill of Materials - Very generic
2. I stated that I removed some identifying information that's why