Record selection Qiestion!

1. Senior Member
Join Date
Dec 1969
Posts
236

## Record selection Qiestion!

I have the following date in MS acess<BR><BR>FAIRID VALID_FR VALID _TO PRICE SUN FRI SAT<BR>1 12/05/2001 12/20/2001 258 0 0 1<BR>2 12/05/2001 12/20/2001 198 1 0 0<BR><BR>Now I need to select a record - say record with FAIRID 1 and Sum of all the Price (all prices of all recs)<BR>Can i do this and if I can HOW ?<BR>THANKS in advance!

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## RE: Record selection Qiestion!

Do you mean that, in your table, *many* records might have FAIRID=1 ???<BR><BR>If so:<BR><BR>SELECT Sum(PRICE) As TOTALPRICE FROM table WHERE FAIRID=1<BR><BR>If you want the sum of *all* prices, whatever their FAIRID is, then why did you mention "...with FAIRID 1..." ??? Anyway, if that is what you want:<BR><BR>SELECT Sum(PRICE) As TOTALPRICE FROM table<BR><BR>

3. Senior Member
Join Date
Dec 1969
Posts
236

## RE: Record selection Qiestion!

How about if I need to select multiple records with fairid = 1 and sum of all the records in the table??<BR>THANKS!<BR>I have <BR>"SELECT AIRLINE,RESCLASS,OW,CHILD_OW,CHILD_RT,INF_RT,Sum(R T) as RT1 FROM Fares WHERE (FROMCITIES=&#039;" + fromcode + "&#039;) AND (TOCITY =&#039;" + tocode + "&#039;) AND ((RT&#060;&#062;0) or (OW&#060;&#062;0)) and (VALID_FR &#060;= " & sysdatequery & ") and (VALID_TO &#062;= " & sysdatequery & ") AND AP &#060; " & APdays &""_<BR> & " AND (MON = " & MyDay& " OR TUE=" & Myday& " OR WED = "& Myday &" OR THU =" & Myday & " OR FRI=" &Myday& " OR SAT = "&MYday&" OR SUN = " & Myday & " ) AND "& depquery&" &#062;= F_Travel AND "&retquery&" &#060;= L_Travel GROUP BY RT" <BR>The above is giving me error <BR>Error Type:<BR>Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)<BR>[Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression &#039;AIRLINE&#039; as part of an aggregate function.<BR>any help would be greatly appreciated <BR>

4. Senior Member
Join Date
Dec 1969
Posts
96,118

## Can't be done with single query...

&#062; How about if I need to select multiple records with fairid = 1 <BR>&#062; AND sum of all the records in the table??<BR><BR>As you have found out from the error message you got.<BR><BR>You could use a UNION, but I don&#039;t think that&#039;s the best choice in this case.<BR><BR>Probably just two separate queries. If the DB you are using supports it, you could put two queries into a single SQL string and get back two recordsets from one query. <BR><BR>Basically, with SQL, you can&#039;t get back *both* detail records and any kind of aggregate result in the same query. When you see queries such as:<BR><BR>SELECT Airline, Sum(RT) AS RtSum<BR>FROM ...<BR>GROUP BY Airline<BR><BR>that only works because you are selecting the fields you are also grouping by.<BR><BR><BR>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•