Access 03 Removing Records during Query that appear to be duplicat

[rgluch74]

I am working on a report for my company, and I am linking tables to our
accounting software and all information is coming through on the tables, but
when I create a query to narrow down the information, Access is removing
certain records that appear to be duplicate information. This is not the
case however, and I am wondering if anyone knows where I can look to turn
this feature off, if it is a feature that is on? Please help!!!

 

[Jerry Whittle]

Queries don't remove records unless it's a delete query. Rather they don't
return records that don't meet the criteria. Therefore we need to know what
the query is asking to return.

Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here. Information on primary keys and relationships
would be a nice touch too.

 

[rgluch74]

SELECT GLM_MASTER__ACCOUNT1.Account, GLM_MASTER__ACCOUNT1.Account_Title,
GLT_CURRENT__TRANSACTION.Accounting_Date, GLT_CURRENT__TRANSACTION.Batch,
GLT_CURRENT__TRANSACTION.Transaction_Desc, GLT_CURRENT__TRANSACTION.Debit,
GLT_CURRENT__TRANSACTION.Credit
FROM GLM_MASTER__ACCOUNT1 INNER JOIN GLT_CURRENT__TRANSACTION ON
GLM_MASTER__ACCOUNT1.Account = GLT_CURRENT__TRANSACTION.Account
GROUP BY GLM_MASTER__ACCOUNT1.Account, GLM_MASTER__ACCOUNT1.Account_Title,
GLT_CURRENT__TRANSACTION.Accounting_Date, GLT_CURRENT__TRANSACTION.Batch,
GLT_CURRENT__TRANSACTION.Transaction_Desc, GLT_CURRENT__TRANSACTION.Debit,
GLT_CURRENT__TRANSACTION.Credit
HAVING (((GLM_MASTER__ACCOUNT1.Account)>="1-az-88-6007" And
(GLM_MASTER__ACCOUNT1.Account)<="1-az-99-9997") AND
((GLT_CURRENT__TRANSACTION.Accounting_Date)>=#1/1/2007# And
(GLT_CURRENT__TRANSACTION.Accounting_Date)<=#9/30/2007#));

I am pulling account # and account title from the GLM Master, and then the
accounting information from the GLT Current Transaction

 

[Jerry Whittle]

You have a Totals query which will remove duplicates. The Group By clause is
inserted in a Totals query. As you aren't doing any aggregrate funtions, such
as count or sum, in the Select part of the query, you don't need the Group By
unless you don't want to see dupes.

In Query Design view with the QBE grid showing, go up to View on the menu
and uncheck Totals.