Rows in select query will not match

[angelesta]

Hi All,

Because of the huge amount of information we export from a corporate system, I have to save a file as a text file.
Today's file had 146,999 rows.
It is a linked file and when I open the file as a table in Access, it matches the original text file of 146,999 rows.

I run a couple of update queries and the end product also has the same amount of rows.

I then have a simple select query where I have to match up a couple of different tables but the rows did not match. I also end up with about 500-1300 rows less!
I couldn't figure out why so I took out all relationships and simply added in the table and all the fields in the table and pulled it up that way and the rows STILL don't match! Again, it produces the same amount of rows about 500-1300 less (so I know it's not the relationships I had in there).

I have no filters - I'm simply pulling a table into a query that matches the table...why wouldn't the number of rows match?
I also would like to mention that all columns are labeled as "Group By"

I'm pretty stumped!
Has anyone else had anything like this happen to them before?
Thanks for the help!

 

[boblarson]

What is the SQL of the query that you are trying to use?

How are you linking up the other tables and do they have an exact number of records that match the other? I am guessing not and therefore you probably have to use an outer join to join them.

 

[angelesta]

I took out all the relationships so it's not that. I created a Select Query that pulls in all the same fields as the table.
I'm a still new to Access so where would I find the SQL?
Thanks!

 

[angelesta]

Found my SQL View

SELECT [2-tbl2012History].[Soldto No], [2-tbl2012History].[Soldto Name], [2-tbl2012History].[Dept No], [2-tbl2012History].GenStyle, [2-tbl2012History].GenStyleSKU, [2-tbl2012History].[Category Desc], [2-tbl2012History].[Sub Category Desc], [2-tbl2012History].[Pfg Desc], [2-tbl2012History].Description, [2-tbl2012History].[Pillar Desc], [2-tbl2012History].[Data Type], [2-tbl2012History].[Calendar Month], [2-tbl2012History].YYYYMM, [2-tbl2012History].[Fiscal Year], [2-tbl2012History].[Fiscal Qtr], [2-tbl2012History].[Order Number], [2-tbl2012History].[Blanket Type], [2-tbl2012History].[Full Price], [2-tbl2012History].[Sell Price], [2-tbl2012History].[Unit Cost], [2-tbl2012History].[Line Units], [2-tbl2012History].[Line Value], [2-tbl2012History].[Line Costs], [2-tbl2012History].[Margin Tier], [2-tbl2012History].[Margin Percent], [2-tbl2012History].[Margin Value]
FROM [2-tbl2012History]
GROUP BY [2-tbl2012History].[Soldto No], [2-tbl2012History].[Soldto Name], [2-tbl2012History].[Dept No], [2-tbl2012History].GenStyle, [2-tbl2012History].GenStyleSKU, [2-tbl2012History].[Category Desc], [2-tbl2012History].[Sub Category Desc], [2-tbl2012History].[Pfg Desc], [2-tbl2012History].Description, [2-tbl2012History].[Pillar Desc], [2-tbl2012History].[Data Type], [2-tbl2012History].[Calendar Month], [2-tbl2012History].YYYYMM, [2-tbl2012History].[Fiscal Year], [2-tbl2012History].[Fiscal Qtr], [2-tbl2012History].[Order Number], [2-tbl2012History].[Blanket Type], [2-tbl2012History].[Full Price], [2-tbl2012History].[Sell Price], [2-tbl2012History].[Unit Cost], [2-tbl2012History].[Line Units], [2-tbl2012History].[Line Value], [2-tbl2012History].[Line Costs], [2-tbl2012History].[Margin Tier], [2-tbl2012History].[Margin Percent], [2-tbl2012History].[Margin Value];

 

[jasmith4]

This SQL shows only one table. Please do what you do to link it to the other tables, then show the SQL.

Hint: as you link tables, you'll get lines from one table to another -- double-click each line and choose join type 1, 2 or 3 wisely...

 

[Norie]

How exactly are you trying to match records?

In that query you don't seem to be comparing anything and all the fields/values are from one table.

Also, do you want to group by anything? If you don't either delete the entire GROUP BY CLAUSE or deselct Totals in query design view.

Oh, wait a minute the SQL you posted isn't for the query you are using to 'match'?

Is that right?

 

[angelesta]

I did not want to link the other tables right away - I wanted to get the same amount of rows but I did delete the "Group By" language and THAT DID IT!!
THANK YOU! Never looked at the SQL before - learned something new
THanks everyone!

 

[boblarson]

The Group By will eliminate some records if they have direct matches with other records.

EDIT - Oh, I posted this just after you did.

 

[angelesta]

Thank you!!! We posted at the same time! That was all it was...never knew how to change that before