What I'm trying to do is select the most recent record for each customer who created an account between 2004-01-01 and 2007-01-01. I'm using MSQuery.
I have this so far: - SELECT OrderOrAdjust, CustomerID,OrderDate,PaymentStatus,PaymentMethod from bi_extract
-
WHERE CustomerFirstOrderDate >=2004/01/01 AND CustomerFirstOrderDate <2007/01/01 AND OrderOrAdjust='order' AND PaymentStatus='payment approved' AND (PaymentMethod='VISA' OR PaymentMethod='MasterCard' OR PaymentMethod='American Express' OR PaymentMethod='Cash' OR PaymentMethod='Purchase Order')
-
GROUP BY CustomerID
-
ORDER BY CustomerID asc, CustomerFirstOrderDate desc
and I get this error:
column'bi_extract.OrderOrAdjust' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause
What am I doing wrong?
JP
1 4018
Whenever you have a GROUP BY clause, then everything in the SELECT clause must reference only columns in this list, or be an aggregate.
For example: - SELECT MAX(OrderOrAdjust), CustomerID,MAX(OrderDate), MAX(PaymentStatus),MAX(PaymentMethod) from bi_extract
-
WHERE CustomerFirstOrderDate >=2004/01/01 AND CustomerFirstOrderDate <2007/01/01 AND OrderOrAdjust='order' AND PaymentStatus='payment approved' AND (PaymentMethod='VISA' OR PaymentMethod='MasterCard' OR PaymentMethod='American Express' OR PaymentMethod='Cash' OR PaymentMethod='Purchase Order')
-
GROUP BY CustomerID
-
ORDER BY CustomerID asc, CustomerFirstOrderDate desc
or: - SELECT OrderOrAdjust, CustomerID,MAX(OrderDate), PaymentStatus,PaymentMethod from bi_extract
-
WHERE CustomerFirstOrderDate >=2004/01/01 AND CustomerFirstOrderDate <2007/01/01 AND OrderOrAdjust='order' AND PaymentStatus='payment approved' AND (PaymentMethod='VISA' OR PaymentMethod='MasterCard' OR PaymentMethod='American Express' OR PaymentMethod='Cash' OR PaymentMethod='Purchase Order')
-
GROUP BY OrderOrAdjust, CustomerID,PaymentStatus,PaymentMethod
-
ORDER BY CustomerID asc, CustomerFirstOrderDate desc
What I'm trying to do is select the most recent record for each customer who created an account between 2004-01-01 and 2007-01-01. I'm using MSQuery.
I have this so far: - SELECT OrderOrAdjust, CustomerID,OrderDate,PaymentStatus,PaymentMethod from bi_extract
-
WHERE CustomerFirstOrderDate >=2004/01/01 AND CustomerFirstOrderDate <2007/01/01 AND OrderOrAdjust='order' AND PaymentStatus='payment approved' AND (PaymentMethod='VISA' OR PaymentMethod='MasterCard' OR PaymentMethod='American Express' OR PaymentMethod='Cash' OR PaymentMethod='Purchase Order')
-
GROUP BY CustomerID
-
ORDER BY CustomerID asc, CustomerFirstOrderDate desc
and I get this error:
column'bi_extract.OrderOrAdjust' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause
What am I doing wrong?
JP
Sign in to post your reply or Sign up for a free account.
Similar topics
by: mr_burns |
last post by:
hi,
how do i do a SELECT statment that will just query a table to find the
row with the maximum of a declared field. for example, if i have a
table of customers and i would like to get the value...
|
by: Rowan |
last post by:
Hi there, it has been a while since i have posted. I am in a
situation where I am stumped. I am learning to build a dts package
where I am connecting to a table in an AS400. This database is...
|
by: William Wisnieski |
last post by:
Hello Again,
I'm really stuck on this one.....so I'm going to try a different approach to
this problem.
I have a query by form that returns a record set in a datasheet. The user
double...
|
by: Ross A. Finlayson |
last post by:
Hi,
I'm scratching together an Access database. The development box is
Office 95, the deployment box Office 2003.
So anyways I am griping about forms and global variables. Say for
example...
|
by: GSteven |
last post by:
(as formerly posted to microsoft.public.access.forms with no result)
I've created a continuous form which is based on a straightforward table
(ex - customers - 100 records). On the form there is...
|
by: MN |
last post by:
Hello,
I have a customer table and another table that I need to prepopulate
with special customer IDs, unique and not sequential. Is there a way to
configure Access to assign the customer ID to...
|
by: liz82much |
last post by:
I need to select the most recent record within a date range for each combination of two ids. In other words, given the folowing table:
ID1 ID2 MyDate MyNum
1005 27 ...
|
by: radcaesar |
last post by:
Hi All,
I Have Three Tables,
Customer
NewTransaction
Transactions
The Customer Table Have details about customer and the Transactions table had details of the transactions with gross...
|
by: rehanmomin |
last post by:
BACKGROUND
I was trying the figure out a query for my sisters optometrist office. I have two tables, one that contains customers demographics and another table that contains patient's examination. ...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
| |