Sam,
I'm probably being too picky, but because I overused it in the past I had major slow down and it possibly was the cause of "too many databases open" messages. so I'm wary and if there's a way to avoid it I do. Most of the time I've needed it because I've not done joins correctly in my queries.
The duplicate records are both price and date.
I'm creating a BeforeUpdate event to control what the users are putting in so that they don't accidentally input duplicate records.
Connie
--- In MS_Access_Professionals@yahoogroups.com, "sarmbraugh" <sarmbraugh@...> wrote:
>
> Hi mrsgoudge,
>
> DISTINCT is something I use rarely so I'm not sure what the issues with it might be -- is there a problem you want to prevent by avoiding using it?
>
> Also wondering if your duplicate records are duplicated in terms of both price and date.
>
> Sam
>
> --- In MS_Access_Professionals@yahoogroups.com, mrsgoudge <no_reply@> wrote:
> >
> > Today I had not planned on working on Access--too much other stuff to do, but I'm encounter one issue after another. I am having duplicates returned in a qCurrentPriceAll that has a subquery with SELECT TOP 1.The query qCurrentPriceAll is based on is a union query joining price and dates from the tables Listings and DropPrice. Because of user mistakes or unique circumstances there is sometimes a duplicate entry in the DropPrice table as the Listings table. qCurrentPriceAll is supposed to find the most current date and give that line. That is working except in the instances of duplicate entries in which case there is a line for both.
> >
> > Is there a way to change this without using DISTINCT? I've discovered and would rather not use it if not necessary!
> >
> >
> > SELECT qCurrentPrice1.ListID, qCurrentPrice1.CurrentPrice, qCurrentPrice1.Dat
> > FROM qCurrentPrice1
> > WHERE (((qCurrentPrice1.Dat) In (SELECT TOP 1 Dat
> > FROM qCurrentPrice1 As Dupe
> > WHERE qCurrentPrice1.ListID = Dupe.ListID And (Dupe.CurrentPrice Is Not Null)
> > ORDER BY Dupe.ListID, Dupe.Dat DESC) Or (qCurrentPrice1.Dat) Is Null));
> >
>
Tidak ada komentar:
Posting Komentar