Help with Isolating duplicate records by date.

[Guest]

I have a query that pulls transaction information by an item number and I
have the day the transactions are done. I am trying to figure out a way to
group or isolate just the items/transactions that are being duplicated on a
specific day. For example I may have done a hundred transactions in the month
of January but I need to see all the transactions where I deliverd an item
twice on 1/1/07. Any help or ideas on this would be much appreciated. Thanks

 

[Guest]

Dan

You can just put the date you want to isolate in the criteria for the date
part of your query in design view. Alternatively put [Enter Date] as the
criteria and you will receive a prompt to enter the date when you run the
query.

If you want to also isolate those items with more than one sale on that date
create another query that uses the above, date filtered, query. Put something
like this in the item number query criteria:-

In (SELECT [ItemNo] FROM [qryTransactions] As Tmp GROUP BY [ItemNo] HAVING
Count(*)>1 )

where qryTransactions is the name of your first query.

Hope this helps.

Andy

 

[Guest]

Andy, This is good info but I am trying to not have to enter the specific
date, I have about a years worth of info and I am just trying to have access
isolate those items that are duplicates on the same day.

Dan

You can just put the date you want to isolate in the criteria for the date
part of your query in design view. Alternatively put [Enter Date] as the
criteria and you will receive a prompt to enter the date when you run the
query.

If you want to also isolate those items with more than one sale on that date
create another query that uses the above, date filtered, query. Put something
like this in the item number query criteria:-

In (SELECT [ItemNo] FROM [qryTransactions] As Tmp GROUP BY [ItemNo] HAVING
Count(*)>1 )

where qryTransactions is the name of your first query.

Hope this helps.

Andy

I have a query that pulls transaction information by an item number and I
have the day the transactions are done. I am trying to figure out a way to
group or isolate just the items/transactions that are being duplicated on a
specific day. For example I may have done a hundred transactions in the month
of January but I need to see all the transactions where I deliverd an item
twice on 1/1/07. Any help or ideas on this would be much appreciated. Thanks

 

[Guest]

Dan

Try this:-

SELECT Transactions.ItemNo, Transactions.Date
FROM Transactions
WHERE (((Transactions.ItemNo) In (SELECT [ItemNo] FROM [Transactions] As Tmp
GROUP BY [ItemNo],[Date] HAVING Count(*)>1 And [Date] =
[Transactions].[Date])))
ORDER BY Transactions.ItemNo, Transactions.Date;

or paste this into the criteria for your Item no:-

In (SELECT [ItemNo] FROM [Transactions] As Tmp GROUP BY [ItemNo],[Date]
HAVING Count(*)>1 And [Date] = [Transactions].[Date])

having amended it for your field names etc.


Andy

Andy, This is good info but I am trying to not have to enter the specific
date, I have about a years worth of info and I am just trying to have access
isolate those items that are duplicates on the same day.

Dan

You can just put the date you want to isolate in the criteria for the date
part of your query in design view. Alternatively put [Enter Date] as the
criteria and you will receive a prompt to enter the date when you run the
query.

If you want to also isolate those items with more than one sale on that date
create another query that uses the above, date filtered, query. Put something
like this in the item number query criteria:-

In (SELECT [ItemNo] FROM [qryTransactions] As Tmp GROUP BY [ItemNo] HAVING
Count(*)>1 )

where qryTransactions is the name of your first query.

Hope this helps.

Andy

I have a query that pulls transaction information by an item number and I
have the day the transactions are done. I am trying to figure out a way to
group or isolate just the items/transactions that are being duplicated on a
specific day. For example I may have done a hundred transactions in the month
of January but I need to see all the transactions where I deliverd an item
twice on 1/1/07. Any help or ideas on this would be much appreciated. Thanks