hi
i have a table with several fields
Field1(area), Field2(Quantity ), Field3(cost), field4(date)
area Quantity cost date
office 10 50 6/4/04
gym 50 90 9/4/04
office 100 2000 9/4//04
what i need to do is return the total for an area over a date range
ie
area Quantity cost
office 110 2050
gym 50 90
the user will select the date range from a combobox
i have the following code
strSQL = "SELECT DISTINCT ([DataTable].[Area]),
Sum([DataTable].Quantity) AS [SumOfTotal Quantity],
Sum([DataTable].TotalPrice) AS [SumOfTotal Price] " & _
"FROM datatable " & _
"WHERE DataTable.Dates Between" &
SQLDate(Me.cboS tartDate.Value) & "And" & SQLDate(cboEndD ate.Value)
this code works fine until i include the date range
How do i adapt this code to return the distinct area along with the
total quantity and total cost for the area
thanks in advance
kevin
1  2277 
Hi Kevin
Same as my reply to your last post, use GROUP BY DataTable.Area only
Stewart
"Kevin Carter" <kc******@ford. com> wrote in message
news:2a******** *************** ***@posting.goo gle.com... hi
i have a table with several fields
Field1(area), Field2(Quantity ), Field3(cost), field4(date)
area Quantity cost date
office 10 50 6/4/04
gym 50 90 9/4/04
office 100 2000 9/4//04
what i need to do is return the total for an area over a date range
ie
area Quantity cost
office 110 2050
gym 50 90
the user will select the date range from a combobox
i have the following code
strSQL = "SELECT DISTINCT ([DataTable].[Area]),
Sum([DataTable].Quantity) AS [SumOfTotal Quantity],
Sum([DataTable].TotalPrice) AS [SumOfTotal Price] " & _
"FROM datatable " & _
"WHERE DataTable.Dates Between" &
SQLDate(Me.cboS tartDate.Value) & "And" & SQLDate(cboEndD ate.Value)
this code works fine until i include the date range
How do i adapt this code to return the distinct area along with the
total quantity and total cost for the area
thanks in advance
kevin This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: ben |
last post by:
This is a PHP / MySQL kind of question. I am making a script which
simply pulls information from a database and displays it on screen. BUT
there will be entries where fields could be the same, and I want to
eliminate the duplicate entries when it displays it on screen.
I would use,
SELECT DISTINCT 'address1' FROM orders
....which does work, but only gives me one field. I also need to get a
|
by: Martin Feuersteiner |
last post by:
Dear Group
I'm having trouble with the clause below. I would like to select only
records with a distinct TransactionDate but somehow it still lists
duplicates. I need to select the TransactionDate once as smalldatetime and
once as varchar as I'm populating a drop-down with Text/Value pairs. So I
can't just use 'SELECT DISTINCT TransactionDate FROM...'
I'm grateful for any hints.
|
by: mfyahya |
last post by:
I have two tables, both containing an 'authors' column. Is there a way
to get a unique list of authors from the two tables?
I tried SELECT DISTINCT `authors` from `table1`, `table2`;
but I got an "Column 'authors' in field list is ambiguous" error.
Is there also a query to return only the count of distinct authors from
the two tables?
Thanks for any help.
|
by: Johnson, Shaunn |
last post by:
Howdy:
Can someone tell what the difference (and why
you would use it) is between the following:
select distinct on (col_1, col_2),
col_1,
col_2,
col_3
|
by: mathilda |
last post by:
My boss has been adamant that SELECT DISTINCT is a faster query than
SELECT all other factors being equal. I disagree. We are linking an
Access front end to a SQL Server back end and normally are only
returning one record. She states that with disctinct the query stops
as soon as it finds a matching record. Both of us are relative novices
in database theory (obviously).
Can someone help settle this?
| | |
by: nfrodsham |
last post by:
In Microsoft's help literature, it states:
"You can filter out non-unique rows by using the DISTINCT option of an
aggregate function"
I am trying to do this in Access 2003 with the COUNT aggregate
function, but there is no reference, at least that I can find anywhere,
of how to do this. I have multiple lines fields for which I would like
to do a "count distinct", but for simplicity, I am showing an example
of only one field. Here is...
|
by: orekinbck |
last post by:
Hi There
Our test database has duplicate data:
COMPANYID COMPANYNAME
1 Grupple Group
2 Grupple Group
5 Grupple Group
3 Yada Inc
4 Yada Inc
|
by: Bob Stearns |
last post by:
I am getting unwanted duplicate rows in my result set, so I added the
DISTINCT keyword to my outermost SELECT. My working query then returned
the following message:
DB2 SQL error: SQLCODE: -214, SQLSTATE: 42822, SQLERRMC: CASE...;ORDER BY;2
Message: An expression in the ORDER BY clause in the following
position, or starting with "CASE..." in the "ORDER BY" clause is not
valid. Reason code = "2".
More exceptions ... DB2 SQL error:...
|
by: monomaniac21 |
last post by:
hi!
is it possible to do the aforementioned query - selecting only distinct
in 1 col but retrieving all other cols at the same time.
regards
marc
|
by: Techhead |
last post by:
I need to run a SELECT DISTINCT query across
multiple fields, but I need to add another field that is NON-DISTINCT
to my record set.
Here is my query:
SELECT DISTINCT lastname, firstname, middleinitial, address1,
address2, city, state, zip, age, gender
FROM gpresults
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| | |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
