SUM of a field in a query

L

Leo

I have a query called qryDAILYMEDS. It has a field named EPN. This query is
build on a Union Query that was built on a table called tblPSD. Everything is
working fine. The union query was used as the basis due to the poor design of
the database.

Nevertheless, when the qry DAILYMEDS is run, in addition to listing
everything under the field named EPN is there any way to get the sum of the
all items that are listed? We are dealing with a number field in the original
table (Long Integer).

Thanks
Leo
 
D

Duane Hookom

Totals are generally displayed in forms or reports. If you want to get the
sum in your query, you can create a union query of your original query with a
similar totals query.
 
L

Leo

I am pretty sure that will not be possible. As I mentioned the original table
is not designed well. It has Date1 through Date23 fields along with EPN1
through EPN23 fields all in a row (each row as a single record for the entire
month - almost a spread sheet or "Committed Database") as a single record.
That's why the union query was created to reformat the data and then another
query (called qryEPN) based on that union query to list all the EPN for any
single date. The qryEPN has fields named EPN and TheDate. When TheDate is
entered by the user it lists all the EPN for that one particular date. What I
need is the total EPN of the listed EPNs for that day.

Is it possible to enter an expression in the qryEPN so that it can be done?

Thanks
Leo
 
D

Duane Hookom

What are the fields returned from qryEPN?
Why can't you create a query like:
SELECT *
FROM qryEPN
WHERE theDate=Forms!frmDate!txtDate;

Then create a union query based on the new, date-restricted query.
 
L

Leo

Well that is the problem. As I mentioned there are 23 dates (Date1 through
Date23) in each records and therefore the form. If there is a single frmDate
there wont be a problem.

So is there a way to get the Sum of field EPN on the qryDailyMeds which is a
query based on a UnionQry based on a tblPSD?


Thanks
Leo
 
D

Duane Hookom

I thought you had a union query that normalized your table structure. If not,
please state what you have done regarding your un-normalized structure.
 
L

Leo

I didn't know that you have to normalize the union query or it could be
normalized!

Here is the union query

SELECT [Account_Number] As [ID], [Date1] As [Date], [EPO1] As [EPOGEN],
[MED1] As [MEDICATIONS]
FROM [Schd]
UNION ALL

SELECT [Account_Number], [Date2], [EPO2], [MED2]
FROM [Schd]
UNION ALL

SELECT [Account_Number], [Date4], [EPO4], [MED4]
FROM [Schd]
UNION ALL

It goes like this for all the way to Date23,Epo23, Med23


Thanks
Leo
 
D

Duane Hookom

Do you or do you not want to display both detailed and summary records from
this union query based on a date or dates?

If this isn't what you want then what do you want?

--
Duane Hookom
Microsoft Access MVP


Leo said:
I didn't know that you have to normalize the union query or it could be
normalized!

Here is the union query

SELECT [Account_Number] As [ID], [Date1] As [Date], [EPO1] As [EPOGEN],
[MED1] As [MEDICATIONS]
FROM [Schd]
UNION ALL

SELECT [Account_Number], [Date2], [EPO2], [MED2]
FROM [Schd]
UNION ALL

SELECT [Account_Number], [Date4], [EPO4], [MED4]
FROM [Schd]
UNION ALL

It goes like this for all the way to Date23,Epo23, Med23


Thanks
Leo

Duane Hookom said:
I thought you had a union query that normalized your table structure. If not,
please state what you have done regarding your un-normalized structure.
Click to expand...
Click to expand...
 
L

Leo

Yes I do want to display both detailed and summary records from
this union query based on a date or dates?

Thanks
Leo

Duane Hookom said:
Do you or do you not want to display both detailed and summary records from
this union query based on a date or dates?

If this isn't what you want then what do you want?

--
Duane Hookom
Microsoft Access MVP


Leo said:
I didn't know that you have to normalize the union query or it could be
normalized!

Here is the union query

SELECT [Account_Number] As [ID], [Date1] As [Date], [EPO1] As [EPOGEN],
[MED1] As [MEDICATIONS]
FROM [Schd]
UNION ALL

SELECT [Account_Number], [Date2], [EPO2], [MED2]
FROM [Schd]
UNION ALL

SELECT [Account_Number], [Date4], [EPO4], [MED4]
FROM [Schd]
UNION ALL

It goes like this for all the way to Date23,Epo23, Med23


Thanks
Leo

Duane Hookom said:
I thought you had a union query that normalized your table structure. If not,
please state what you have done regarding your un-normalized structure.

--
Duane Hookom
Microsoft Access MVP


:

Well that is the problem. As I mentioned there are 23 dates (Date1 through
Date23) in each records and therefore the form. If there is a single frmDate
there wont be a problem.

So is there a way to get the Sum of field EPN on the qryDailyMeds which is a
query based on a UnionQry based on a tblPSD?


Thanks
Leo


:

What are the fields returned from qryEPN?
Why can't you create a query like:
SELECT *
FROM qryEPN
WHERE theDate=Forms!frmDate!txtDate;

Then create a union query based on the new, date-restricted query.

--
Duane Hookom
Microsoft Access MVP


:

I am pretty sure that will not be possible. As I mentioned the original table
is not designed well. It has Date1 through Date23 fields along with EPN1
through EPN23 fields all in a row (each row as a single record for the entire
month - almost a spread sheet or "Committed Database") as a single record.
That's why the union query was created to reformat the data and then another
query (called qryEPN) based on that union query to list all the EPN for any
single date. The qryEPN has fields named EPN and TheDate. When TheDate is
entered by the user it lists all the EPN for that one particular date. What I
need is the total EPN of the listed EPNs for that day.

Is it possible to enter an expression in the qryEPN so that it can be done?

Thanks
Leo

:

Totals are generally displayed in forms or reports. If you want to get the
sum in your query, you can create a union query of your original query with a
similar totals query.
--
Duane Hookom
Microsoft Access MVP


:

I have a query called qryDAILYMEDS. It has a field named EPN. This query is
build on a Union Query that was built on a table called tblPSD. Everything is
working fine. The union query was used as the basis due to the poor design of
the database.

Nevertheless, when the qry DAILYMEDS is run, in addition to listing
everything under the field named EPN is there any way to get the sum of the
all items that are listed? We are dealing with a number field in the original
table (Long Integer).

Thanks
Leo
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

What is your union query's name? What dates do you want to display details of
and which records do you want to sum? What do you want to aggregate in the
totals part of the query?

--
Duane Hookom
Microsoft Access MVP


Leo said:
Yes I do want to display both detailed and summary records from
this union query based on a date or dates?

Thanks
Leo

Duane Hookom said:
Do you or do you not want to display both detailed and summary records from
this union query based on a date or dates?

If this isn't what you want then what do you want?

--
Duane Hookom
Microsoft Access MVP


Leo said:
I didn't know that you have to normalize the union query or it could be
normalized!

Here is the union query

SELECT [Account_Number] As [ID], [Date1] As [Date], [EPO1] As [EPOGEN],
[MED1] As [MEDICATIONS]
FROM [Schd]
UNION ALL

SELECT [Account_Number], [Date2], [EPO2], [MED2]
FROM [Schd]
UNION ALL

SELECT [Account_Number], [Date4], [EPO4], [MED4]
FROM [Schd]
UNION ALL

It goes like this for all the way to Date23,Epo23, Med23


Thanks
Leo

:

I thought you had a union query that normalized your table structure. If not,
please state what you have done regarding your un-normalized structure.

--
Duane Hookom
Microsoft Access MVP


:

Well that is the problem. As I mentioned there are 23 dates (Date1 through
Date23) in each records and therefore the form. If there is a single frmDate
there wont be a problem.

So is there a way to get the Sum of field EPN on the qryDailyMeds which is a
query based on a UnionQry based on a tblPSD?


Thanks
Leo


:

What are the fields returned from qryEPN?
Why can't you create a query like:
SELECT *
FROM qryEPN
WHERE theDate=Forms!frmDate!txtDate;

Then create a union query based on the new, date-restricted query.

--
Duane Hookom
Microsoft Access MVP


:

I am pretty sure that will not be possible. As I mentioned the original table
is not designed well. It has Date1 through Date23 fields along with EPN1
through EPN23 fields all in a row (each row as a single record for the entire
month - almost a spread sheet or "Committed Database") as a single record.
That's why the union query was created to reformat the data and then another
query (called qryEPN) based on that union query to list all the EPN for any
single date. The qryEPN has fields named EPN and TheDate. When TheDate is
entered by the user it lists all the EPN for that one particular date. What I
need is the total EPN of the listed EPNs for that day.

Is it possible to enter an expression in the qryEPN so that it can be done?

Thanks
Leo

:

Totals are generally displayed in forms or reports. If you want to get the
sum in your query, you can create a union query of your original query with a
similar totals query.
--
Duane Hookom
Microsoft Access MVP


:

I have a query called qryDAILYMEDS. It has a field named EPN. This query is
build on a Union Query that was built on a table called tblPSD. Everything is
working fine. The union query was used as the basis due to the poor design of
the database.

Nevertheless, when the qry DAILYMEDS is run, in addition to listing
everything under the field named EPN is there any way to get the sum of the
all items that are listed? We are dealing with a number field in the original
table (Long Integer).

Thanks
Leo
Click to expand...
Click to expand...
Click to expand...
 
L

Leo

The union query name: SchdUn
Date will be based on user input (Only One date)
I need it list all the EPN that will be administered to various clients on
that particular date. I also need the sum of all the EPN that is being
administered on that day.

Thanks
Leo




Duane Hookom said:
What is your union query's name? What dates do you want to display details of
and which records do you want to sum? What do you want to aggregate in the
totals part of the query?

--
Duane Hookom
Microsoft Access MVP


Leo said:
Yes I do want to display both detailed and summary records from
this union query based on a date or dates?

Thanks
Leo

Duane Hookom said:
Do you or do you not want to display both detailed and summary records from
this union query based on a date or dates?

If this isn't what you want then what do you want?

--
Duane Hookom
Microsoft Access MVP


:

I didn't know that you have to normalize the union query or it could be
normalized!

Here is the union query

SELECT [Account_Number] As [ID], [Date1] As [Date], [EPO1] As [EPOGEN],
[MED1] As [MEDICATIONS]
FROM [Schd]
UNION ALL

SELECT [Account_Number], [Date2], [EPO2], [MED2]
FROM [Schd]
UNION ALL

SELECT [Account_Number], [Date4], [EPO4], [MED4]
FROM [Schd]
UNION ALL

It goes like this for all the way to Date23,Epo23, Med23


Thanks
Leo

:

I thought you had a union query that normalized your table structure. If not,
please state what you have done regarding your un-normalized structure.

--
Duane Hookom
Microsoft Access MVP


:

Well that is the problem. As I mentioned there are 23 dates (Date1 through
Date23) in each records and therefore the form. If there is a single frmDate
there wont be a problem.

So is there a way to get the Sum of field EPN on the qryDailyMeds which is a
query based on a UnionQry based on a tblPSD?


Thanks
Leo


:

What are the fields returned from qryEPN?
Why can't you create a query like:
SELECT *
FROM qryEPN
WHERE theDate=Forms!frmDate!txtDate;

Then create a union query based on the new, date-restricted query.

--
Duane Hookom
Microsoft Access MVP


:

I am pretty sure that will not be possible. As I mentioned the original table
is not designed well. It has Date1 through Date23 fields along with EPN1
through EPN23 fields all in a row (each row as a single record for the entire
month - almost a spread sheet or "Committed Database") as a single record.
That's why the union query was created to reformat the data and then another
query (called qryEPN) based on that union query to list all the EPN for any
single date. The qryEPN has fields named EPN and TheDate. When TheDate is
entered by the user it lists all the EPN for that one particular date. What I
need is the total EPN of the listed EPNs for that day.

Is it possible to enter an expression in the qryEPN so that it can be done?

Thanks
Leo

:

Totals are generally displayed in forms or reports. If you want to get the
sum in your query, you can create a union query of your original query with a
similar totals query.
--
Duane Hookom
Microsoft Access MVP


:

I have a query called qryDAILYMEDS. It has a field named EPN. This query is
build on a Union Query that was built on a table called tblPSD. Everything is
working fine. The union query was used as the basis due to the poor design of
the database.

Nevertheless, when the qry DAILYMEDS is run, in addition to listing
everything under the field named EPN is there any way to get the sum of the
all items that are listed? We are dealing with a number field in the original
table (Long Integer).

Thanks
Leo
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

Assuming you allow your users to enter the date into a text box [txtDate] on
form [frmDate]:

SELECT [ID], [Date], [EPOGEN], [MEDICATIONS]
FROM [SchdUn]
WHERE [Date] = Forms!frmDate!txtDate
UNION ALL
SELECT Null, [Date], Sum(EPOGEN), Null
FROM [SchdUn]
WHERE [Date] = Forms!frmDate!txtDate
GROUP BY [Date];

--
Duane Hookom
Microsoft Access MVP


Leo said:
The union query name: SchdUn
Date will be based on user input (Only One date)
I need it list all the EPN that will be administered to various clients on
that particular date. I also need the sum of all the EPN that is being
administered on that day.

Thanks
Leo




Duane Hookom said:
What is your union query's name? What dates do you want to display details of
and which records do you want to sum? What do you want to aggregate in the
totals part of the query?

--
Duane Hookom
Microsoft Access MVP


Leo said:
Yes I do want to display both detailed and summary records from
this union query based on a date or dates?

Thanks
Leo

:

Do you or do you not want to display both detailed and summary records from
this union query based on a date or dates?

If this isn't what you want then what do you want?

--
Duane Hookom
Microsoft Access MVP


:

I didn't know that you have to normalize the union query or it could be
normalized!

Here is the union query

SELECT [Account_Number] As [ID], [Date1] As [Date], [EPO1] As [EPOGEN],
[MED1] As [MEDICATIONS]
FROM [Schd]
UNION ALL

SELECT [Account_Number], [Date2], [EPO2], [MED2]
FROM [Schd]
UNION ALL

SELECT [Account_Number], [Date4], [EPO4], [MED4]
FROM [Schd]
UNION ALL

It goes like this for all the way to Date23,Epo23, Med23


Thanks
Leo

:

I thought you had a union query that normalized your table structure. If not,
please state what you have done regarding your un-normalized structure.

--
Duane Hookom
Microsoft Access MVP


:

Well that is the problem. As I mentioned there are 23 dates (Date1 through
Date23) in each records and therefore the form. If there is a single frmDate
there wont be a problem.

So is there a way to get the Sum of field EPN on the qryDailyMeds which is a
query based on a UnionQry based on a tblPSD?


Thanks
Leo


:

What are the fields returned from qryEPN?
Why can't you create a query like:
SELECT *
FROM qryEPN
WHERE theDate=Forms!frmDate!txtDate;

Then create a union query based on the new, date-restricted query.

--
Duane Hookom
Microsoft Access MVP


:

I am pretty sure that will not be possible. As I mentioned the original table
is not designed well. It has Date1 through Date23 fields along with EPN1
through EPN23 fields all in a row (each row as a single record for the entire
month - almost a spread sheet or "Committed Database") as a single record.
That's why the union query was created to reformat the data and then another
query (called qryEPN) based on that union query to list all the EPN for any
single date. The qryEPN has fields named EPN and TheDate. When TheDate is
entered by the user it lists all the EPN for that one particular date. What I
need is the total EPN of the listed EPNs for that day.

Is it possible to enter an expression in the qryEPN so that it can be done?

Thanks
Leo

:

Totals are generally displayed in forms or reports. If you want to get the
sum in your query, you can create a union query of your original query with a
similar totals query.
--
Duane Hookom
Microsoft Access MVP


:

I have a query called qryDAILYMEDS. It has a field named EPN. This query is
build on a Union Query that was built on a table called tblPSD. Everything is
working fine. The union query was used as the basis due to the poor design of
the database.

Nevertheless, when the qry DAILYMEDS is run, in addition to listing
everything under the field named EPN is there any way to get the sum of the
all items that are listed? We are dealing with a number field in the original
table (Long Integer).

Thanks
Leo
Click to expand...
Click to expand...
Click to expand...
 
L

Leo

I greatly appreciate your patience with me. Will try this and let you know. I
guess your first suggestion to use a report may be the easiest one.

Thanks again
Leo

Duane Hookom said:
Assuming you allow your users to enter the date into a text box [txtDate] on
form [frmDate]:

SELECT [ID], [Date], [EPOGEN], [MEDICATIONS]
FROM [SchdUn]
WHERE [Date] = Forms!frmDate!txtDate
UNION ALL
SELECT Null, [Date], Sum(EPOGEN), Null
FROM [SchdUn]
WHERE [Date] = Forms!frmDate!txtDate
GROUP BY [Date];

--
Duane Hookom
Microsoft Access MVP


Leo said:
The union query name: SchdUn
Date will be based on user input (Only One date)
I need it list all the EPN that will be administered to various clients on
that particular date. I also need the sum of all the EPN that is being
administered on that day.

Thanks
Leo




Duane Hookom said:
What is your union query's name? What dates do you want to display details of
and which records do you want to sum? What do you want to aggregate in the
totals part of the query?

--
Duane Hookom
Microsoft Access MVP


:

Yes I do want to display both detailed and summary records from
this union query based on a date or dates?

Thanks
Leo

:

Do you or do you not want to display both detailed and summary records from
this union query based on a date or dates?

If this isn't what you want then what do you want?

--
Duane Hookom
Microsoft Access MVP


:

I didn't know that you have to normalize the union query or it could be
normalized!

Here is the union query

SELECT [Account_Number] As [ID], [Date1] As [Date], [EPO1] As [EPOGEN],
[MED1] As [MEDICATIONS]
FROM [Schd]
UNION ALL

SELECT [Account_Number], [Date2], [EPO2], [MED2]
FROM [Schd]
UNION ALL

SELECT [Account_Number], [Date4], [EPO4], [MED4]
FROM [Schd]
UNION ALL

It goes like this for all the way to Date23,Epo23, Med23


Thanks
Leo

:

I thought you had a union query that normalized your table structure. If not,
please state what you have done regarding your un-normalized structure.

--
Duane Hookom
Microsoft Access MVP


:

Well that is the problem. As I mentioned there are 23 dates (Date1 through
Date23) in each records and therefore the form. If there is a single frmDate
there wont be a problem.

So is there a way to get the Sum of field EPN on the qryDailyMeds which is a
query based on a UnionQry based on a tblPSD?


Thanks
Leo


:

What are the fields returned from qryEPN?
Why can't you create a query like:
SELECT *
FROM qryEPN
WHERE theDate=Forms!frmDate!txtDate;

Then create a union query based on the new, date-restricted query.

--
Duane Hookom
Microsoft Access MVP


:

I am pretty sure that will not be possible. As I mentioned the original table
is not designed well. It has Date1 through Date23 fields along with EPN1
through EPN23 fields all in a row (each row as a single record for the entire
month - almost a spread sheet or "Committed Database") as a single record.
That's why the union query was created to reformat the data and then another
query (called qryEPN) based on that union query to list all the EPN for any
single date. The qryEPN has fields named EPN and TheDate. When TheDate is
entered by the user it lists all the EPN for that one particular date. What I
need is the total EPN of the listed EPNs for that day.

Is it possible to enter an expression in the qryEPN so that it can be done?

Thanks
Leo

:

Totals are generally displayed in forms or reports. If you want to get the
sum in your query, you can create a union query of your original query with a
similar totals query.
--
Duane Hookom
Microsoft Access MVP


:

I have a query called qryDAILYMEDS. It has a field named EPN. This query is
build on a Union Query that was built on a table called tblPSD. Everything is
working fine. The union query was used as the basis due to the poor design of
the database.

Nevertheless, when the qry DAILYMEDS is run, in addition to listing
everything under the field named EPN is there any way to get the sum of the
all items that are listed? We are dealing with a number field in the original
table (Long Integer).

Thanks
Leo
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SUM in a UNION query 2
dynamic calculated field in query 3
Union Query and Field Alias 7
Access Field won't show as currency in the form, shows just a number (but works fine in the query) 1
How to assign table name to be value of a field in a UNION query? 2
Union query? 7
SELECT/UNION query not working 13
dsum sytax error 5

Top