|
| |
|
|
Nathan Goulette
Posts: 274
Joined: 1/12/2003
From: Phoenix, AZ
Status: offline
 |
RE: Adding (SUM) DB Entries Together - 6/9/2004 16:28:45
I tried that and got:quote:
Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
Number: -2147217904 (0x80040E10)
Source: Microsoft OLE DB Provider for ODBC Drivers
Also, for giggles, I tried changing it to ORDER BY tblMeals.MealID DESC and got this:quote:
Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
Number: -2147217913 (0x80040E07)
Source: Microsoft OLE DB Provider for ODBC Drivers
Thanks for working thru this with me.
Nate
|
|
|
|
BeTheBall
Posts: 6487
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Adding (SUM) DB Entries Together - 6/9/2004 17:42:08
Actually, you were right. It should be tblMeals.MealID. Is entryDATE a text field in the db or a date field? If it's a date field, then change AND Detail.entryDATE = '::entryDATE::' to AND Detail.entryDATE = #::entryDATE::#
SELECT tblMeals.MealID, tblMeals.MealName, Detail.entryNAME, Detail.entryDATE, Detail.entryDESC, Detail.entryPOINTS, SUM(Detail.entryPOINTS) AS TotalPOINTS, Individual.entrantGOAL FROM (Individual RIGHT JOIN Detail ON Individual.entrantNAME=Detail.entryNAME) INNER JOIN tblMeals ON tblMeals.MealID=Detail.MealID GROUP BY Detail.entryNAME, Detail.entryDESC, Detail.entryPOINTS, Individual.entrantGOAL, Detail.entryDATE, tblMeals.MealID, tblMeals.MealName HAVING Detail.entryNAME='::entryNAME::' AND Detail.entryDATE='::entryDATE::' ORDER BY tblMeals.MealID DESC
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
BeTheBall
Posts: 6487
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Adding (SUM) DB Entries Together - 6/9/2004 21:22:05
Two possible solutions. First a possibly easier way. I am guessing you want the various foods to appear in the order they were eaten, right? Well, if they are entered into the database in the order eaten we can just use the entryID field for the ORDER BY criteria. Something like this:
fp_sQry="SELECT Detail.EntryID, Detail.entryNAME, Detail.entryDATE, Detail.entryDESC, Detail.entryPOINTS, SUM(Detail.entryPOINTS) AS TotalPOINTS, Individual.entrantGOAL FROM Individual RIGHT JOIN Detail ON Individual.entrantNAME=Detail.entryNAME GROUP BY EntryID, Detail.entryNAME, Detail.entryDESC, Detail.entryPOINTS, Individual.entrantGOAL, Detail.entryDATE HAVING Detail.entryNAME='::entryNAME::' AND Detail.entryDATE=#::entryDATE::# ORDER BY EntryID ASC"
If that doesn't work, this SQL should work with the tblMeals table:
fp_sQry="SELECT tblMeals.MealID, tblMeals.MealName, Detail.entryNAME, Detail.entryDATE, Detail.entryDESC, Detail.entryPOINTS, SUM(Detail.entryPOINTS) AS TotalPOINTS, Individual.entrantGOAL FROM (Individual RIGHT JOIN Detail ON Individual.entrantNAME=Detail.entryNAME) INNER JOIN tblMeals ON tblMeals.MealsID=Detail.MealID GROUP BY Detail.entryNAME, Detail.entryDESC, Detail.entryPOINTS, Individual.entrantGOAL, Detail.entryDATE HAVING Detail.entryNAME='::entryNAME::' AND Detail.entryDATE='::entryDATE::' ORDER BY MealID DESC"
Good luck!
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
) but the actual sorting is not taking place. They just being displayed alphabetically and NOT based on the MealID. 