Recent content by Zybron

I have a usercontrol that contains a collection as a list(of <custom class>). Within the collection class for this usercontrol I have a shared event that is raised when a collection member is added to the class. Within the usercontrol class I have an event that is triggered when the collection...

Here's an interesting problem. I have the following query stored in a database that I'm working on called NotPaid17Days: SELECT A1.CurrentPrice, Max(I1.PartNum) AS PartNumber, I1.ShortDescription AS Title, DateValue([A1.EndDate]) AS EndDate FROM (Items AS I1 INNER JOIN Auctions AS A1 ON...

Steve, Thanks for the reply and I think the Outer Join may be the answer I'm looking for but, despite the prolific use of Joins in that query, I've rarely used them for anything more than to associate data in two tables. I know that the various types of outer joins include data that does not...

The following query gives me the exact information I need but it takes about 10 minutes for it to run. Is there anyway to speed this query up? SELECT Auctions.CurrentPrice, Items.PartNum, Items.ShortDescription, Max(DateValue([Auctions.EndDate])) AS [Closing Date] FROM (Sales INNER JOIN Items...

Bob, Great! That works. I sent you an email with a sample database. It took a while to pare it down to something manageable by email. I guess you can just delete it. Thanks again for your help.

Yeah, I just wish my acorn wasn't so hard to find, or I wasn't so blind. ;) I still get the same error message. Perhaps I should have mentioned this earlier, but the database in question is in Access 2000 format. Is this a limitation of that version?

Hmm, well when I attempt to run the new query I get &quot;You tried to run a query that does not include the specified expression 'A.CurrentPrice' as part of an aggregate function.&quot; When I include A.CurrentPrice or CurrentPrice in the Group By I get duplicate PartNums (which I would kind of...

First, my last post overlapped (we gotta stop doing that :) Second, Bob, that does look like it would work, but I get a syntax error in the Having clause. Any ideas?

Hmm, well, sort of. The above query will produce duplicate PartNums, as they might exist more than once with different CurrentPrices, but it would definately be faster for me to parse through a sorted table of PartNums and pull out the duplicates with lower CurrentPrices than the way I'm doing...

Actually, I don't need the ItemIDs at all, though I see what your question is getting at. If the data is sorted by CurrentPrice DESC, then the highest amount is always the one that I want, so it's not an issue as to which one of the itemIDs I get. As long as it's the one with the highest...

Ok, here's a portion of the results, sorted by PartNum to illustrate a point: ItemID PartNum 13630 31D12450C06 14330 31D12451G0B 13639 31D12452J1B 13617 31D12456C06 13674 31D12457C07 13603 31D12459J1B 14620 31D13143G0B 13823 31D13143G0B 14642 31D13144G0B 13787...

One other question about the subquery? Let's suppose I have: ItemID PartNum 1 123 2 321 3 123 Wouldn't the above subquery produce: ItemID PartNum 1 123 3 123 Since it finds distinct values for each row, or am I mistaken? I don't see any duplicate...

Ah, it does once the following change is made: FROM Items, Auctions I didn't notice that before, but it's still giving me a syntax error in the JOIN operation.

Hmmm, I seem to still be having problems with this. Let me show the actual query text and maybe what I'm trying to get will make more sense. SELECT Items.PartNum, Auctions.CurrentPrice FROM Auctions INNER JOIN (SELECT DISTINCT Items.ItemID, Items.PartNum FROM Items WHERE...

SELECT Table1.Field1, (SELECT DISTINCT Table2.Field2 FROM Table2 WHERE Table2.FieldID = Table1.FieldID AND Table1.Field3 >= #1/1/200# AND Table1.Field3 < #7/7/2003# AND Table1.Field4 > 40) FROM Table1 WHERE Table2.FieldID = Table1.FieldID...