Transfer a reports result to a query

  • Thread starter Thread starter Garry
  • Start date Start date
G

Garry

Hi all

I have a sum in a report as follows :-

Control Source=Sum([No Records])

Running Sum = Over Group

My question is how can I transfer this to a query



Another thought, can I make a table from a reports output ?

Thanks, ps I might be barking up the wrong tree
 
You should be able to do most anything (datawise) in a query that you can do
in a report. Maybe you can explain exactly what you have, what you want, and
why.
 
The report wizard lets me sum by group, I can then cumulatively add the sum
of all groups as
for example :-

description Stock cumulative

small eggs 24 24
medium eggs 12 36
large eggs 10 46

How can I express this as a query

Hope I have explained, cheers


Duane Hookom said:
You should be able to do most anything (datawise) in a query that you can
do
in a report. Maybe you can explain exactly what you have, what you want,
and
why.
--
Duane Hookom
Microsoft Access MVP


Garry said:
Hi all

I have a sum in a report as follows :-

Control Source=Sum([No Records])

Running Sum = Over Group

My question is how can I transfer this to a query



Another thought, can I make a table from a reports output ?

Thanks, ps I might be barking up the wrong tree
Click to expand...
Click to expand...
 
You haven't described an order which is required if you want to create a
running sum. It looks like your current "order" is Description DESC.

Try something like:

SELECT N.Description, N.Stock ,
(SELECT Sum(Stock) FROM tblNoName N2 WHERE N2.Description >=N.Description)
As Cumulative
FROM tblNoName N
ORDER BY N.Description;

--
Duane Hookom
Microsoft Access MVP


Garry said:
The report wizard lets me sum by group, I can then cumulatively add the sum
of all groups as
for example :-

description Stock cumulative

small eggs 24 24
medium eggs 12 36
large eggs 10 46

How can I express this as a query

Hope I have explained, cheers


Duane Hookom said:
You should be able to do most anything (datawise) in a query that you can
do
in a report. Maybe you can explain exactly what you have, what you want,
and
why.
--
Duane Hookom
Microsoft Access MVP


Garry said:
Hi all

I have a sum in a report as follows :-

Control Source=Sum([No Records])

Running Sum = Over Group

My question is how can I transfer this to a query



Another thought, can I make a table from a reports output ?

Thanks, ps I might be barking up the wrong tree
Click to expand...
Click to expand...
Click to expand...
 
Hi Duane, Ordered on description from table Client Data

I basically want to add up all the entries for small eggs (3 in total,
12+6+6) which gives a stock value = 24
then all entries for medium (2 in total, 6+6) = 12

Then a running cumulative figure for all eggs

Thanks, Garry


Duane Hookom said:
You haven't described an order which is required if you want to create a
running sum. It looks like your current "order" is Description DESC.

Try something like:

SELECT N.Description, N.Stock ,
(SELECT Sum(Stock) FROM tblNoName N2 WHERE N2.Description >=N.Description)
As Cumulative
FROM tblNoName N
ORDER BY N.Description;

--
Duane Hookom
Microsoft Access MVP


Garry said:
The report wizard lets me sum by group, I can then cumulatively add the
sum
of all groups as
for example :-

description Stock cumulative

small eggs 24 24
medium eggs 12 36
large eggs 10 46

How can I express this as a query

Hope I have explained, cheers


Duane Hookom said:
You should be able to do most anything (datawise) in a query that you
can
do
in a report. Maybe you can explain exactly what you have, what you
want,
and
why.
--
Duane Hookom
Microsoft Access MVP


:

Hi all

I have a sum in a report as follows :-

Control Source=Sum([No Records])

Running Sum = Over Group

My question is how can I transfer this to a query



Another thought, can I make a table from a reports output ?

Thanks, ps I might be barking up the wrong tree
Click to expand...
Click to expand...
Click to expand...
 
Did you try what I suggested? If not, why? If you did, what were your
results? What was your SQL?

--
Duane Hookom
Microsoft Access MVP


Garry said:
Hi Duane, Ordered on description from table Client Data

I basically want to add up all the entries for small eggs (3 in total,
12+6+6) which gives a stock value = 24
then all entries for medium (2 in total, 6+6) = 12

Then a running cumulative figure for all eggs

Thanks, Garry


Duane Hookom said:
You haven't described an order which is required if you want to create a
running sum. It looks like your current "order" is Description DESC.

Try something like:

SELECT N.Description, N.Stock ,
(SELECT Sum(Stock) FROM tblNoName N2 WHERE N2.Description >=N.Description)
As Cumulative
FROM tblNoName N
ORDER BY N.Description;

--
Duane Hookom
Microsoft Access MVP


Garry said:
The report wizard lets me sum by group, I can then cumulatively add the
sum
of all groups as
for example :-

description Stock cumulative

small eggs 24 24
medium eggs 12 36
large eggs 10 46

How can I express this as a query

Hope I have explained, cheers


You should be able to do most anything (datawise) in a query that you
can
do
in a report. Maybe you can explain exactly what you have, what you
want,
and
why.
--
Duane Hookom
Microsoft Access MVP


:

Hi all

I have a sum in a report as follows :-

Control Source=Sum([No Records])

Running Sum = Over Group

My question is how can I transfer this to a query



Another thought, can I make a table from a reports output ?

Thanks, ps I might be barking up the wrong tree
Click to expand...
Click to expand...
Click to expand...
 
SQL

SELECT N.Description, Sum(N.stock) AS [total stock], (SELECT Sum(Stock) FROM
N WHERE N.Description >=N.Description) AS Cumulative FROM N GROUP BY
N.Description;

The result is displayed as :-

Description total stock Cumulative
large eggs 36 72
medium eggs 12 72
small eggs 24 72



I require :-

large eggs 36 36
medium eggs 12 48
small eggs 24 72

thanks for your time, regards, Garry
 
You didn't implement the SQL correctly. The following will always be true:
N.Description >=N.Description

Try alias the table name in the subquery like:

SELECT N.Description, Sum(N.stock) AS [total stock], (SELECT Sum(Stock) FROM
N N2 WHERE N2.Description >=N.Description) AS Cumulative FROM N GROUP BY
N.Description;
 
That works a treat, for future refference could you explain N2, cheers



Duane Hookom said:
You didn't implement the SQL correctly. The following will always be true:
N.Description >=N.Description

Try alias the table name in the subquery like:

SELECT N.Description, Sum(N.stock) AS [total stock], (SELECT Sum(Stock)
FROM
N N2 WHERE N2.Description >=N.Description) AS Cumulative FROM N GROUP BY
N.Description;

--
Duane Hookom
Microsoft Access MVP


Garry said:
SQL

SELECT N.Description, Sum(N.stock) AS [total stock], (SELECT Sum(Stock)
FROM
N WHERE N.Description >=N.Description) AS Cumulative FROM N GROUP BY
N.Description;

The result is displayed as :-

Description total stock Cumulative
large eggs 36 72
medium eggs 12 72
small eggs 24 72



I require :-

large eggs 36 36
medium eggs 12 48
small eggs 24 72

thanks for your time, regards, Garry
Click to expand...
Click to expand...
 
The N2 is just an alias for the table name. The query contains the same table
selected from twice. In order for the query to track which field comes from
which copy of the table, it is necessary to give at least one instance of the
table an alias.

--
Duane Hookom
Microsoft Access MVP


Garry said:
That works a treat, for future refference could you explain N2, cheers



Duane Hookom said:
You didn't implement the SQL correctly. The following will always be true:
N.Description >=N.Description

Try alias the table name in the subquery like:

SELECT N.Description, Sum(N.stock) AS [total stock], (SELECT Sum(Stock)
FROM
N N2 WHERE N2.Description >=N.Description) AS Cumulative FROM N GROUP BY
N.Description;

--
Duane Hookom
Microsoft Access MVP


Garry said:
SQL

SELECT N.Description, Sum(N.stock) AS [total stock], (SELECT Sum(Stock)
FROM
N WHERE N.Description >=N.Description) AS Cumulative FROM N GROUP BY
N.Description;

The result is displayed as :-

Description total stock Cumulative
large eggs 36 72
medium eggs 12 72
small eggs 24 72



I require :-

large eggs 36 36
medium eggs 12 48
small eggs 24 72

thanks for your time, regards, Garry
Click to expand...
Click to expand...
Click to expand...
 
Back
Top