Excel Query error

P

Patti

Hi!

I've created an Excel query which pulls monthly sales.
The criteria fields are store, sales accounts, month and
year (the first two criteria are fixed; the last two have
the parameters entered when the query is run.

The fields returned are store, sales sub accounts, sales
amount and month. The query works fine, but it gives me
all of the sales detail -- 20,000 lines -- and I just want
the totals, so I clicked on the sales amount field and
selected "sum of". Now I get an error: "Too few
parameters. Expected 2" when I run the query. What am I
doing wrong?

Thanks!
 
J

Jamie Collins

...
I've created an Excel query which pulls monthly sales.
The criteria fields are store, sales accounts, month and
year (the first two criteria are fixed; the last two have
the parameters entered when the query is run.

The fields returned are store, sales sub accounts, sales
amount and month. The query works fine, but it gives me
all of the sales detail -- 20,000 lines -- and I just want
the totals, so I clicked on the sales amount field and
selected "sum of". Now I get an error: "Too few
parameters. Expected 2" when I run the query. What am I
doing wrong?
Click to expand...

Trusting a wizard to write code for you <g>. Type in the SQL yourself.
If you need a GUI tool to help you write the code, try using the one
in the MS Access UI.

From the info posted, your query should look something like
(untested):

SELECT
store,
[sales sub accounts],
SUM([sales amount]) As [sales amount],
Month
FROM
MyTable
WHERE
Store = 'MyStore'
AND [sales accounts] = 'MyValue'
AND month = [Enter Month]
AND year = [Enter year]
GROUP BY
store,
[sales sub accounts],
Month
;

Jamie.

--
 
P

Patti

-----Original Message-----
...
I've created an Excel query which pulls monthly sales.
The criteria fields are store, sales accounts, month and
year (the first two criteria are fixed; the last two have
the parameters entered when the query is run.

The fields returned are store, sales sub accounts, sales
amount and month. The query works fine, but it gives me
all of the sales detail -- 20,000 lines -- and I just want
the totals, so I clicked on the sales amount field and
selected "sum of". Now I get an error: "Too few
parameters. Expected 2" when I run the query. What am I
doing wrong?
Click to expand...

Trusting a wizard to write code for you <g>. Type in the SQL yourself.
If you need a GUI tool to help you write the code, try using the one
in the MS Access UI.

From the info posted, your query should look something like
(untested):

SELECT
store,
[sales sub accounts],
SUM([sales amount]) As [sales amount],
Month
FROM
MyTable
WHERE
Store = 'MyStore'
AND [sales accounts] = 'MyValue'
AND month = [Enter Month]
AND year = [Enter year]
GROUP BY
store,
[sales sub accounts],
Month
;

Jamie.

--
Click to expand...
Thanks Jamie, but I'm still getting the "parameters"
error. I read up a bit on Query, and it looks to me like
I can't do what I want. It looks like I have to run the
data first, pulling the criteria that I want, and then I
can ask for totals. Do you know any way around this?

Thanks again,

Patti
 
J

Jamie Collins

Thanks Jamie, but I'm still getting the "parameters"
error. I read up a bit on Query, and it looks to me like
I can't do what I want.
Click to expand...

I think it is possible, playing by MS Query's rules.

You didn't post your table schema or data so I'll have to guess e.g.

CREATE TABLE Patti
(
store VARCHAR(10),
[sales sub accounts] NUMERIC(12,5),
[sales amount] NUMERIC(12,5),
[sales accounts] NUMERIC(12,5),
[Month] INTEGER,
[Year] INTEGER
)
;

INSERT INTO Patti
(store, [sales sub accounts], [sales amount],
[sales accounts], [Month], [Year])
VALUES ('A', 150, 200, 250, 1, 1)
;

INSERT INTO Patti
(store, [sales sub accounts], [sales amount],
[sales accounts], [Month], [Year])
VALUES ('A', 33, 200, 250, 1, 1)
;

Then the following query works in MS Query using parameter values:

SELECT Patti.store, Sum(Patti.`sales sub accounts`), Patti.`sales
amount`, Patti.Month
FROM `C:\Mydatabase`.Patti Patti
WHERE (Patti.store='A') AND (Patti.`sales accounts`=250) AND
(Patti.Month=?) AND (Patti.Year=?)
GROUP BY Patti.store, Patti.`sales amount`, Patti.Month


Jamie.

--
 
P

Patti

-----Original Message-----
Thanks Jamie, but I'm still getting the "parameters"
error. I read up a bit on Query, and it looks to me like
I can't do what I want.
Click to expand...

I think it is possible, playing by MS Query's rules.

You didn't post your table schema or data so I'll have to guess e.g.

CREATE TABLE Patti
(
store VARCHAR(10),
[sales sub accounts] NUMERIC(12,5),
[sales amount] NUMERIC(12,5),
[sales accounts] NUMERIC(12,5),
[Month] INTEGER,
[Year] INTEGER
)
;

INSERT INTO Patti
(store, [sales sub accounts], [sales amount],
[sales accounts], [Month], [Year])
VALUES ('A', 150, 200, 250, 1, 1)
;

INSERT INTO Patti
(store, [sales sub accounts], [sales amount],
[sales accounts], [Month], [Year])
VALUES ('A', 33, 200, 250, 1, 1)
;

Then the following query works in MS Query using parameter values:

SELECT Patti.store, Sum(Patti.`sales sub accounts`), Patti.`sales
amount`, Patti.Month
FROM `C:\Mydatabase`.Patti Patti
WHERE (Patti.store='A') AND (Patti.`sales accounts`=250) AND
(Patti.Month=?) AND (Patti.Year=?)
GROUP BY Patti.store, Patti.`sales amount`, Patti.Month


Jamie.
Click to expand...
You are a god! It works perfectly! Thank you so much!
 
Top