|
| |
|
|
Nathan Goulette
Posts: 274
Joined: 1/12/2003
From: Phoenix, AZ
Status: offline
 |
Adding (SUM) DB Entries Together - 1/30/2004 20:58:20
Hello All.
What I am looking to do is to have a form where certain data can be entered. On this form there will be a text field for the description and another field that will contain an point value (a number) and the date. The results will be displayed on another page queried by date.
The form and the filtering is not an issue, I can do that but what i need help on is the following.
What I am looking to do is to add up all of the points for each day and subtract them from a designated value.
I do not have the table created yet but it would be something like:
ID - Autonumber
Name - Text
Date - Date
Description - Memo
The number of entries entered per day will vary so there may not always be the same number of points to add up.
Thoughts or Suggestions?
Thanks
Nate
< Message edited by Nathan Goulette -- 1/30/2004 17:59:33 >
|
|
|
|
Nathan Goulette
Posts: 274
Joined: 1/12/2003
From: Phoenix, AZ
Status: offline
|
RE: Adding (SUM) DB Entries Together - 1/31/2004 16:12:26
Ok, I now have the database fields set up.
I have 2 tables, one that lists the names of those who will be entering the data and the goal points for each of them.
TABLE NAME: Individual
TABLE FIELDS:
entrantID (this is an autonumber field)
entrantNAME (this is a text field)
entrantGOAL (this is a number field)
The second table is the table that they will be posting data to via a form.
TABLE NAME: Detail
TABLE FIELDS:
entryID (this is an autonumber field)
entryNAME (this is a text field)
entryDATE (this is a date/time field)
entryDESC (this is a memo field)
entryPOINTS (this is a number field)
This "project" is something that I am doing for my GF who has been trading emails multiple times a day with a friend in regards to the "points" they have used during the day in association with the food they eat (weight watchers).
She asked if I could do up a form that will allow them to enter in the the foods they eat and the "points" associated with that food. She wants to be able to view the results with side by side view of the foods/points she had on any given day with the same info of her friend (who will also be entering her info into the form).
All of this so far I can do. Setting up the form and the results is not an issue. However, what she is looking to do is on the view page, she wants all of the days food points to be totaled and have that total subtracted from their daily allowance showing how many points for the day are left.
My plan is to have a main page that shows a list of the days and points without the food item descriptions. The date will be a link to another page that will display all the info (side by side) for the date they selected.
Can anyone help me out with the SQL portion that will display the SUM of all the points (entryPOINTS) for the day selected (entryDATE) by person (entryNAME) of the DETAIL TABLE and have that SUM subtracted from the designated goal points (entrantGOAL) of the INDIVIDUAL TABLE?
Any assistance would be greatly appreciated.
Regards,
Nate
| |
|
|
|
Nathan Goulette
Posts: 274
Joined: 1/12/2003
From: Phoenix, AZ
Status: offline
|
RE: Adding (SUM) DB Entries Together - 2/1/2004 12:19:20
Sorry for my lack of coding abilities but would you mind posting the code for the process you mentioned?
Thanks in advance.
Nate
| |
|
|
|
Nathan Goulette
Posts: 274
Joined: 1/12/2003
From: Phoenix, AZ
Status: offline
|
RE: Adding (SUM) DB Entries Together - 2/3/2004 20:06:17
Sorry for the delay in responding, I thought I had notification turned on but I guess not.
Anywho, yes I do have a page. Here is the page that will display the results based on the date and name parameters that will be passed on from the first page.
<table width="100%" style="border-collapse: collapse" bordercolor="#111111" cellpadding="0" cellspacing="0">
<tbody>
<!--#include file="../_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database
Results component on this page is unable to display database content. The page
must have a filename ending in '.asp', and the web must be hosted on a server
that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT * FROM Detail WHERE (entryDATE = '::entryDATE::' AND entryNAME = '::entryNAME::') ORDER BY entryDATE ASC,entryNAME ASC"
fp_sDefault="entryDATE=&entryNAME="
fp_sNoRecords="<tr><td colspan=2 align=left width=""100%"">No records returned.</td></tr>"
fp_sDataConn="wwMAIN"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice="entryDESC"
fp_sMenuValue="entryDESC"
fp_iDisplayCols=2
fp_fCustomQuery=False
BOTID=1
fp_iRegion=BOTID
%>
<!--#include file="../_fpclass/fpdbrgn1.inc"-->
<tr>
<td>
<font face="Arial" size="2">
<%=FP_FieldVal(fp_rs,"entryDESC")%></font></td>
<td width="50">
<p align="right"><font face="Arial" size="2">
<%=FP_FieldVal(fp_rs,"entryPOINTS")%></font></td>
</tr>
<!--#include file="../_fpclass/fpdbrgn2.inc"-->
</tbody>
</table>
What I would like to do is two part.
1 - Total up all of the <%=FP_FieldVal(fp_rs,"entryPOINTS")%> values
2 - Subtract that total from the daily allowed points for that person.
This value is defined in entrantGOAL of a table titled Individual.
Does that make sense?
Thanks Mucho
Nate
< Message edited by Nathan Goulette -- 2/3/2004 17:07:54 >
| |
|
|
|
Nathan Goulette
Posts: 274
Joined: 1/12/2003
From: Phoenix, AZ
Status: offline
|
RE: Adding (SUM) DB Entries Together - 2/5/2004 14:41:11
Spooky mentioned doing it in the ASP and am still awaiting his reply but in the meantime does anyone have a SQL query solution?
Thanks
Nate
| |
|
|
|
Nathan Goulette
Posts: 274
Joined: 1/12/2003
From: Phoenix, AZ
Status: offline
|
RE: Adding (SUM) DB Entries Together - 2/8/2004 11:30:33
Any help in providing an ASP or SQL solution would be greatly appreciated.
Thanks
Nate
| |
|
|
|
BeTheBall
Posts: 6493
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Adding (SUM) DB Entries Together - 2/8/2004 13:05:17
quote:
TABLE NAME: Individual
TABLE FIELDS:
entrantID (this is an autonumber field)
entrantNAME (this is a text field)
entrantGOAL (this is a number field)
The second table is the table that they will be posting data to via a form.
TABLE NAME: Detail
TABLE FIELDS:
entryID (this is an autonumber field)
entryNAME (this is a text field)
entryDATE (this is a date/time field)
entryDESC (this is a memo field)
entryPOINTS (this is a number field)
What field ties the two tables together? Do both entrant.NAME and entry.NAME both refer to the person? If so, you can try something like this:
SELECT Individual.entrantNAME, SUM(Details.entryPOINTS) AS TotalPOINTS, Individual.entrantGOAL FROM Individual RIGHT JOIN Details ON Individual.entrantNAME=Details.entryNAME GROUP BY Individual.entrantNAME, Individual.entrantGOAL HAVING Details.entryNAME='::entryNAME::' AND Details.entryDate='::entryDATE::'
Now, if entryDATE is a date/time field in the db, the '::entryDATE::' should be #::entryDATE::#.
Then to display the total points used for the day:
<%=FP_FieldVal(fp_rs,"TotalPOINTS")-FP_FieldVal(fp_rs,"entrantGOAL")%>
May need some tweaking, but should be a good start.
_____________________________
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.
| |
|
|
|
Nathan Goulette
Posts: 274
Joined: 1/12/2003
From: Phoenix, AZ
Status: offline
|
RE: Adding (SUM) DB Entries Together - 2/8/2004 13:33:28
Hey Duane, thanks for the response.
Yes, the entrantNAME and the entryNAME both refer to the person.
The entryDATE field is text as it was causing me issues as date/time earlier.
I have tried this as is and is not verifying properly but will try to work thru it, thanks, much appreciated.
Nate
| |
|
|
|
Nathan Goulette
Posts: 274
Joined: 1/12/2003
From: Phoenix, AZ
Status: offline
|
RE: Adding (SUM) DB Entries Together - 2/8/2004 16:51:08
I am getting an error from with in FP when I try to verify the query. Here is the code that I have at this point:<table width="100%" style="border-collapse: collapse" bordercolor="#111111" cellpadding="0" cellspacing="0">
<tbody>
<!--#include file="../_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database
Results component on this page is unable to display database content. The page
must have a filename ending in '.asp', and the web must be hosted on a server
that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT * FROM Detail WHERE (entryDATE = '::entryDATE::' AND entryNAME = '::entryNAME::') ORDER BY entryDATE ASC,entryNAME ASC"
fp_sDefault="entryDATE=&entryNAME=Kim"
fp_sNoRecords="<tr><td colspan=2 align=left width=""100%"">No records returned.</td></tr>"
fp_sDataConn="wwMAIN"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice="entryDESC"
fp_sMenuValue="entryDESC"
fp_iDisplayCols=2
fp_fCustomQuery=False
BOTID=1
fp_iRegion=BOTID
%>
<!--#include file="../_fpclass/fpdbrgn1.inc"-->
<tr>
<td>
<font face="Arial" size="2">
<%=FP_FieldVal(fp_rs,"entryDESC")%></font></td>
<td width="50">
<p align="right"><font face="Arial" size="2">
<%=FP_FieldVal(fp_rs,"entryPOINTS")%></font></td>
</tr>
<%=FP_FieldVal(fp_rs,"TotalPOINTS")-FP_FieldVal(fp_rs,"entrantGOAL")%>
<!--#include file="../_fpclass/fpdbrgn2.inc"-->
</tbody>
</table>
Is there something that is obviously incorrect?
Thanks
< Message edited by Nathan Goulette -- 2/8/2004 13:53:10 >
| |
|
|
|
BeTheBall
Posts: 6493
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Adding (SUM) DB Entries Together - 2/8/2004 17:51:24
This won't verify???
fp_sQry="SELECT * FROM Detail WHERE (entryDATE = '::entryDATE::' AND entryNAME = '::entryNAME::') ORDER BY entryDATE ASC,entryNAME ASC"
I can't see a problem with it. If you want, you can email me a copy of the page and your db and I will work with it a little more. Send a PM for my email address.
_____________________________
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.
| |
|
|
|
Nathan Goulette
Posts: 274
Joined: 1/12/2003
From: Phoenix, AZ
Status: offline
|
RE: Adding (SUM) DB Entries Together - 2/8/2004 19:51:56
Duane, I sent you a message via the forum.
I just noticed that the code that I posted was PRIOR to the recomendations that you made previously, my bad.
Here is the real deal of where i am at:<table width="100%" style="border-collapse: collapse" bordercolor="#111111" cellpadding="0" cellspacing="0">
<tbody>
<!--#include file="../_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database
Results component on this page is unable to display database content. The page
must have a filename ending in '.asp', and the web must be hosted on a server
that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT Individual.entrantNAME, SUM(Detail.entryPOINTS) AS TotalPOINTS, Individual.entrantGOAL FROM Individual RIGHT JOIN Detail ON Individual.entrantNAME=Detail.entryNAME GROUP BY Individual.entrantNAME, Individual.entrantGOAL HAVING Detail.entryNAME='::entryNAME::' AND Detail.entryDATE='::entryDATE::' ORDER BY entryDATE ASC,entryNAME ASC"
fp_sDefault="entryDATE=&entryNAME"
fp_sNoRecords="<tr><td colspan=2 align=left width=""100%"">No records returned.</td></tr>"
fp_sDataConn="wwMAIN"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_iDisplayCols=2
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="../_fpclass/fpdbrgn1.inc"-->
<tr>
<td>
<font face="Arial" size="2">
<%=FP_FieldVal(fp_rs,"entryDESC")%></font></td>
<td width="50">
<p align="right"><font face="Arial" size="2">
<%=FP_FieldVal(fp_rs,"entryPOINTS")%></font></td>
</tr>
<!--#include file="../_fpclass/fpdbrgn2.inc"-->
</tbody>
</table>I am more than willing to send you the DB and the page if it would be easier that way.
Thanks again!
Nate
| |
|
|
|
BeTheBall
Posts: 6493
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Adding (SUM) DB Entries Together - 2/9/2004 10:39:08
Nate, can you give us an idea of what the final output should look like? In other words, what fields should be displayed on the page?
_____________________________
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.
| |
|
|
|
Nathan Goulette
Posts: 274
Joined: 1/12/2003
From: Phoenix, AZ
Status: offline
|
RE: Adding (SUM) DB Entries Together - 2/9/2004 12:23:18
Yes, here is what I have so far...
WW Journal
The page is being queryied on the date of 01/31/2004 and another date in the currently in the DB is 12/30/2003.
The foods and the points are in the DB (I added stuff for sake of show since i sent you the DB). The three fields under the bottom line are not in the DB (they are after the DRW area) and is what I am trying to do now.
I would like to have the total points of entryPOINTS added up and displayed next to the Total Points. Then to have the persons goal displayed and finally to have the total points subtracted from the goal leaving the remaining points for the day.
If you need any more clarification, please let me know.
Thanks so much!
Nate
| |
|
|
|
BeTheBall
Posts: 6493
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Adding (SUM) DB Entries Together - 2/9/2004 23:40:42
Hey Nate. This SQL should get you all the data you need.
fp_sQry="SELECT 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 Detail.entryNAME, Detail.entryDESC, Detail.entryPOINTS, Individual.entrantGOAL, Detail.entryDATE HAVING Detail.entryNAME='::entryNAME::' AND Detail.entryDATE='::entryDATE::'"
_____________________________
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.
| |
|
|
|
Nathan Goulette
Posts: 274
Joined: 1/12/2003
From: Phoenix, AZ
Status: offline
|
RE: Adding (SUM) DB Entries Together - 2/10/2004 16:13:38
Ok, Kewl.
The issue I have now is that the totals are run for every line item rather than all the line items together.
This is what I would like to try and get it to look like: WHERE I WANT TO BE
And this is where we are now: CURRENTLY
Here is the code for one table... <table border="3" cellspacing="3" bordercolor="#FF0000" width="100%" id="AutoNumber2" cellpadding="3">
<tr>
<td width="100%">
<table border="0" cellspacing="1" id="AutoNumber3" width="100%">
<tr>
<td><font face="Arial" size="2">Foods For The Day</font></td>
<td width="50">
<p align="right"><font face="Arial" size="2">Points</font></td>
</tr>
</table>
</td>
</tr>
<tr>
<td width="100%">
<table width="100%">
<tbody>
<!--#include file="../_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT 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 Detail.entryNAME, Detail.entryDESC, Detail.entryPOINTS, Individual.entrantGOAL, Detail.entryDATE HAVING Detail.entryNAME='::entryNAME::' AND Detail.entryDATE='::entryDATE::'"
fp_sDefault="entryNAME=Wendi&entryDATE="
fp_sNoRecords="<tr><td colspan=6 align=left width=""100%"">No records returned.</td></tr>"
fp_sDataConn="wwMAIN"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice="entryNAME"
fp_sMenuValue="entryNAME"
fp_iDisplayCols=6
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="../_fpclass/fpdbrgn1.inc"-->
<tr>
<td>
<table border="0" cellspacing="1" width="100%" id="AutoNumber6">
<tr>
<td valign="top"><font size="2" face="Arial">
<%=FP_FieldVal(fp_rs,"entryDESC")%></font></td>
<td width="50" valign="top">
<p align="right"><font size="2" face="Arial">
<%=FP_FieldVal(fp_rs,"entryPOINTS")%></font></td>
</tr>
</table>
<table border="0" cellspacing="0" width="100%" id="AutoNumber7" cellpadding="0">
<tr>
<td align="right"><i><font face="Arial" size="2">Total Points:</font></i></td>
<td align="right" width="50"><font face="Arial" size="2">
<%=FP_FieldVal(fp_rs,"TotalPOINTS")%></font></td>
</tr>
<tr>
<td align="right"><i><font face="Arial" size="2">Goal:</font></i></td>
<td align="right" width="50">
<%=FP_FieldVal(fp_rs,"entrantGOAL")%></td>
</tr>
<tr>
<td align="right"><i><font face="Arial" size="2">Points Left
For The Day:</font></i></td>
<td align="right" width="50"><font face="Arial" size="2">
<%=FP_FieldVal(fp_rs,"TotalPOINTS")-FP_FieldVal(fp_rs,"entrantGOAL")%></font></td>
</tr>
</table>
</td>
</tr>
<!--#include file="../_fpclass/fpdbrgn2.inc"-->
</tbody>
</table>
</td>
</tr>
</table>
Sorry to keep pestering but I am certain we are almost there... 
< Message edited by Nathan Goulette -- 2/10/2004 13:19:22 >
| |
|
|
|
Nathan Goulette
Posts: 274
Joined: 1/12/2003
From: Phoenix, AZ
Status: offline
|
RE: Adding (SUM) DB Entries Together - 2/11/2004 18:11:14
Thanks for the help Duane!!!
| |
|
|
|
Nathan Goulette
Posts: 274
Joined: 1/12/2003
From: Phoenix, AZ
Status: offline
|
RE: Adding (SUM) DB Entries Together - 6/8/2004 20:00:03
Hey Duane...
This is still all working properly but I would like to add another DB field to the mix in this and change the sort based on that field.
Do you have any recolection of what we did here or would you need a referesher?
The way it is working now is that the daily entries are being sorted alphbetically and when they add itens throughout the day, the get listed out of order. We would like to have them sorted by the "meal time" field I would like to add to the db(ie, breakfast, morning snack, lunch, afternoon snack, dinner, evening snack and late night snack). When they enter a new item, they would check (radio button) which meal time it is and display the results in that order. Currently, they are adding numbers prior to the entry (1 for breakfast, 1.5 for morning snack, 2 for lunch, etc...) so that they are sorting in the right order but I would like to remove the ugly looking numbers from all the entries.
I can provide all of the most recent code that I have running on here or if you want I can email it fer ya.
I have tried to do it myself but the QRY string is very confusing to me and when I try to add something to the mix, it errors out.
Thanks.
Nate
| |
|
|
|
BeTheBall
Posts: 6493
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Adding (SUM) DB Entries Together - 6/8/2004 22:31:54
I would add a new table to the database. Call it tblMeals. It should have two fields, MealID and then the titles, i.e., breakfast, morning snack, lunch etc. Then, in the Detail table add a new field called MealID (datatype numeric). You can then add the MealID to the current records if you wish. You will also need to add a MealID field to the form you use to insert data. I would probably go with a radio button field and assign the various MealIDs to each button.
Once done the above SQL would need to become:
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
Does that help?
_____________________________
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.
| |
|
|
|
Nathan Goulette
Posts: 274
Joined: 1/12/2003
From: Phoenix, AZ
Status: offline
|
RE: Adding (SUM) DB Entries Together - 6/9/2004 0:11:29
I made all the DB updates you described and this is the result error.
quote:
Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] The specified field 'MealID' could refer to more than one table listed in the FROM clause of your SQL statement.
Number: -2147467259 (0x80004005)
Source: Microsoft OLE DB Provider for ODBC Drivers
Thoughts?
Nate
| |
|
|
|
BeTheBall
Posts: 6493
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Adding (SUM) DB Entries Together - 6/9/2004 0:25:06
ORDER BY Detail.MealID
_____________________________
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.
| |
|
|
|
Nathan Goulette
Posts: 274
Joined: 1/12/2003
From: Phoenix, AZ
Status: offline
|
RE: Adding (SUM) DB Entries Together - 6/9/2004 3:11:47
Ok, different error.quote:
Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'MealID' as part of an aggregate function.
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers
Here is the codefor the query that I have as of current: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 Detail.MealID DESC Thanks
Nate
| |
|
|
|
DesiMcK
Posts: 447
Joined: 4/26/2004
From: Essex, UK
Status: offline
|
RE: Adding (SUM) DB Entries Together - 6/9/2004 3:58:34
Just an obeservation - you have
tblMeals.MealID and tblMeals.MealsID
Does the field have the 's' or not?
Desi
| |
|
|
|
BeTheBall
Posts: 6493
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Adding (SUM) DB Entries Together - 6/9/2004 9:17:25
My bad. Since we added fields to the SELECT portion, we need to add them to the GROUP BY clause as well.
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.entry, tblMeals.MealID, tblMeals.MealName DATE HAVING Detail.entryNAME='::entryNAME::' AND Detail.entryDATE='::entryDATE::' ORDER BY Detail.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.
| |
|
|
|
Nathan Goulette
Posts: 274
Joined: 1/12/2003
From: Phoenix, AZ
Status: offline
|
RE: Adding (SUM) DB Entries Together - 6/9/2004 12:20:36
Ok, it is still no go but the error is different yet again but I feel that this is very close...
Here is the current error:quote:
Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'tblMeals.MealName DATE'.
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers
Here is the code as I have it at this time: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.entry, tblMeals.MealID, tblMeals.MealName DATE HAVING Detail.entryNAME='::entryNAME::' AND Detail.entryDATE='::entryDATE::' ORDER BY Detail.MealID DESC
Thanks Again BeTheBall.
Nate
| |
|
|
|
Nathan Goulette
Posts: 274
Joined: 1/12/2003
From: Phoenix, AZ
Status: offline
|
RE: Adding (SUM) DB Entries Together - 6/9/2004 14:16:32
Here is the error that I am getting:quote:
Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'Detail.MealID' as part of an aggregate function.
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers
Here is my code as of current: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 Detail.MealID DESC
Thanks
Nate
| |
|
|
|
BeTheBall
Posts: 6493
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Adding (SUM) DB Entries Together - 6/9/2004 15:08:02
Try changing:
ORDER BY Detail.MealID DESC
to
ORDER BY Meals.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.
| |
|
 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 
ky 