Search results

Using some nested subqueries, you could obtain your results this way:SELECT DISTINCT tMain.Name, (SELECT TOP 1 L1.Location FROM myTable L1 WHERE L1.Name=tMain.Name ORDER BY L1.Location) As Location1, (SELECT TOP 1 L2.Location FROM myTable L2 WHERE L2.Name=tMain.Name AND L2.Location >...

The trick is you need to set the range of ID's for AcctNum which pertain to a specific fund. In order to do that you need to determine the ID of the next fund (so the AcctNums don't keep getting returned after that point). You find the ID for the next fund with a subquery - So: SELECT TOP 1...

Yes, it was a summer day for sure - not to be missed. Regarding SQL vs code, you could say that SQL is a table's natural habitat. When you dissect the recordset with a VBA control structure, it's like taking a fish out of water and putting it on a petri dish, or something to that effect...

NP, Truth be told, I have a lot of homework to do myself with data modeling and working with the table relationships. - sadly, it always seems to be given the least attention when we start jumping into db's. I suppose the immediacy of Queries and Forms are just too much of a draw to resist...

Without using code, I would add an Autonumber field to your table [AutoID], then build your query: SELECT Right(Trim(t1.F1),4) AS Fund, t2.F1 AS AcctNum FROM myTable t1 INNER JOIN myTable t2 ON t1.AutoID+2=t2.AutoID WHERE t1.F1 Like "Fund*"; (Assuming your Fund will always be 4 characters long)...

xirokx, Listen to RainLover, who is giving you good advice and is very experienced. It's good of you to consider MS Access as a solution. But what you are considering is something that will take some time to learn since you're new to Access. However, it will also be very rewarding time if...

You're Welcome! ;)

"""" & [ContactNickNameID].[Column](1) & """" I think... maybe only 3 double quotes in a row, but I seem to recall it should be 4 in a row to make a single 'double-quote' (") HTH, John

Dvent, Your 2nd IIf is missing the False argument: IIf(IsNull([myField3]), IIf(IsNull([myField2]),[myField1],[???]), [myField2])

missing 3rd (if false) argument in your 2nd IIf statement

NP - time for me to 'hit the hay' ;)

brickelldb, I just tested the query with the sample data you posted. It works fine as long as your data meets the following condition: ---> You can't have more than 1 VendorID with the same minimum price (or even the same VendorID repeated with the same minimum price) So I changed the query...

brickelldb, You mention that you continue to get the message "only 1 record can be returned at a time with this subquery" - so I'm curious what your data values are. For same ItemID, can more than 1 VendorID have the same minimum price? You should be getting all ItemID's coming back from the...

Your main query should be returning a list of all ItemID's in your table, along with all associated VendorID's. If you have equal values for MinOfCost, it will return both so maybe try modifying your Subquery: (SELECT DISTINCT TOP 1 ... ) Hopefully, that gets rid of the message you're getting...

my bad - take out the comma after "MinOfCost"

By renaming the original table "Tbl_PriceCheck" directly in the Query, you're creating an alias for it. If you look in Design View it should show your table name as "tPCMain" - it's using the alias. So leave the SQL statement as-is without adding any tables. The only thing you'll probably...

ok - I see what you mean. This is a common dilemma. Subqueries can be one way to tackle this - Try: SELECT DISTINCT tPCMain.ItemID, (SELECT TOP 1 tPC.Cost FROM Tbl_PriceCheck tPC WHERE tPC.ItemID = tPCMain.ItemID ORDER BY tPC.Cost) AS MinOfCost, (SELECT TOP 1 tPCV.VendorID...

The error is because you didn't include VendorID in your GROUP BY clause: GROUP BY Tbl_PriceCheck.ItemID, Tbl_PriceCheck.VendorID; Of course you're probably already including VendorID in your SELECT clause, as appropriate (otherwise it wouldn't throw you that error) HTH, John

Use a Subquery. Although not intimately familiar with the native processes of DLookup vs Subquery, I would tend to think the SQL solution requires less 'overhead' than a DLookup.

Use your Form's BeforeUpdate event