Showing the sum of all records for one field

[Guest]

I have created a table with a corresponding report to show the different type
of aircraft, quantity and corresponding notes that is to participate in a
particular training evolution. The table and report work fine to show the
individual amounts, for example:
AC_Designation Total_assigned Notes
AV-8B_Harrier 6 Radar-4, N.A.-2
C-130_Herc 4 None
H-1_Skidz 17 Cobra-11, Huey-6
H-46_Phrog 19 None

I have been attempting to show a total of the number of Fixed Wing
(Airplanes) and Rotary Wing (Helicopters) that are due to participate. I have
been trying to get it to look like this:

AC_Designation Total_assigned Notes Total
AV-8B_Harrier 6 Radar-4, N.A.-2 10
C-130_Herc 4 None 10
H-1_Skidz 17 Cobra-11, Huey-6 36
H-46_Phrog 19 None 36

Or something similar to that. I haven't even attempted to sort the results
between the different type of aircraft yet because I haven't been able to get
a total count of all of the aircraft yet. I tried using the following in the
already setup query:
Total: ([Total_assigned])
All it did was return the total for each individual aircraft under the
'total' column:

AC_Designation Total_assigned Notes Total
AV-8B_Harrier 6 Radar-4, N.A.-2 6
C-130_Herc 4 None 4

H-1_Skidz 17 Cobra-11, Huey-6 17
H-46_Phrog 19 None

What am I missing and what can I do to alleviate this problem? I look
forward to your response.

 

[MGFoster]

I have created a table with a corresponding report to show the different type
of aircraft, quantity and corresponding notes that is to participate in a
particular training evolution. The table and report work fine to show the
individual amounts, for example:
AC_Designation Total_assigned Notes
AV-8B_Harrier 6 Radar-4, N.A.-2
C-130_Herc 4 None
H-1_Skidz 17 Cobra-11, Huey-6
H-46_Phrog 19 None

I have been attempting to show a total of the number of Fixed Wing
(Airplanes) and Rotary Wing (Helicopters) that are due to participate. I have
been trying to get it to look like this:

AC_Designation Total_assigned Notes Total
AV-8B_Harrier 6 Radar-4, N.A.-2 10
C-130_Herc 4 None 10
H-1_Skidz 17 Cobra-11, Huey-6 36
H-46_Phrog 19 None 36

Or something similar to that. I haven't even attempted to sort the results
between the different type of aircraft yet because I haven't been able to get
a total count of all of the aircraft yet. I tried using the following in the
already setup query:
Total: ([Total_assigned])
All it did was return the total for each individual aircraft under the
'total' column:

AC_Designation Total_assigned Notes Total
AV-8B_Harrier 6 Radar-4, N.A.-2 6
C-130_Herc 4 None 4

H-1_Skidz 17 Cobra-11, Huey-6 17
H-46_Phrog 19 None

What am I missing and what can I do to alleviate this problem? I look
forward to your response.

Make sure the query is a summary query (click the epsilon key on the
toolbar [if you're unfamiliar w/ Greek letters it looks a capital E]).
Then use the SUM() function to total a column:

Total: Sum(Total_Assigned)

 

[Edward G]

Well jm3099, here's my thinking on this problem:
First off, it looks from the way you have presented this as though you are
attempting
to use Access as though it were Excel. You begin by stating "I have created
a table"
and you lay out a set of columns with your data. Now, maybe I am
misunderstanding this,
but it looks as though you have a field in your table called Total_assigned.
This isn't the kind
of info one normally expects to see in a table. In a query perhaps, set up
to produce totals, but not in a
table. So, there is every reason to believe that your database is improperly
constructed which is something
you should endeavor to fix, if you want this database to be useful.
That said, if you want to get totals for Fixed Wing and Rotary Wing
aircraft, you will need to have
a field in your table called something like Wing_Type that will accept
entries of this sort. And you will
need a query that includes Wing_Type and Total_assigned. And you will need
to make this a Totals
query, with Wing_Type set to "GroupBy" and Total_assigned set to "Sum". This
query can then be
added to the underlying query in your report, the Sum field can be added to
the QBE grid of the underlying
query and that will in turn make this Sum available as a field in the
report's field list.
I hope that is helpful. I suspect you will have more questions. Please try
to keep the thread intact.

Ed

I have created a table with a corresponding report to show the different type
of aircraft, quantity and corresponding notes that is to participate in a
particular training evolution. The table and report work fine to show the
individual amounts, for example:
AC_Designation Total_assigned Notes
AV-8B_Harrier 6 Radar-4, N.A.-2
C-130_Herc 4 None
H-1_Skidz 17 Cobra-11, Huey-6
H-46_Phrog 19 None

I have been attempting to show a total of the number of Fixed Wing
(Airplanes) and Rotary Wing (Helicopters) that are due to participate. I have
been trying to get it to look like this:

AC_Designation Total_assigned Notes Total
AV-8B_Harrier 6 Radar-4, N.A.-2 10
C-130_Herc 4 None 10
H-1_Skidz 17 Cobra-11, Huey-6 36
H-46_Phrog 19 None 36

Or something similar to that. I haven't even attempted to sort the results
between the different type of aircraft yet because I haven't been able to get
a total count of all of the aircraft yet. I tried using the following in the
already setup query:
Total: ([Total_assigned])
All it did was return the total for each individual aircraft under the
'total' column:

AC_Designation Total_assigned Notes Total
AV-8B_Harrier 6 Radar-4, N.A.-2 6
C-130_Herc 4 None 4

H-1_Skidz 17 Cobra-11, Huey-6 17
H-46_Phrog 19 None

What am I missing and what can I do to alleviate this problem? I look
forward to your response.

 

[Guest]

Thank you for your input. Unfortunately, after taking your advice I still
came out with the same previously erroneous output that is:

AC_Designation Total_assigned Notes Total
AV-8B_Harrier 6 Radar-4, N.A.-2 6
C-130_Herc 4 None 4

H-1_Skidz 17 Cobra-11, Huey-6 17
H-46_Phrog 19 None

Any other ideas? I would appreciate your input. Thanks.

I have created a table with a corresponding report to show the different type
of aircraft, quantity and corresponding notes that is to participate in a
particular training evolution. The table and report work fine to show the
individual amounts, for example:
AC_Designation Total_assigned Notes
AV-8B_Harrier 6 Radar-4, N.A.-2
C-130_Herc 4 None
H-1_Skidz 17 Cobra-11, Huey-6
H-46_Phrog 19 None

I have been attempting to show a total of the number of Fixed Wing
(Airplanes) and Rotary Wing (Helicopters) that are due to participate. I have
been trying to get it to look like this:

AC_Designation Total_assigned Notes Total
AV-8B_Harrier 6 Radar-4, N.A.-2 10
C-130_Herc 4 None 10
H-1_Skidz 17 Cobra-11, Huey-6 36
H-46_Phrog 19 None 36

Or something similar to that. I haven't even attempted to sort the results
between the different type of aircraft yet because I haven't been able to get
a total count of all of the aircraft yet. I tried using the following in the
already setup query:
Total: ([Total_assigned])
All it did was return the total for each individual aircraft under the
'total' column:

AC_Designation Total_assigned Notes Total
AV-8B_Harrier 6 Radar-4, N.A.-2 6
C-130_Herc 4 None 4

H-1_Skidz 17 Cobra-11, Huey-6 17
H-46_Phrog 19 None

What am I missing and what can I do to alleviate this problem? I look
forward to your response.

Make sure the query is a summary query (click the epsilon key on the
toolbar [if you're unfamiliar w/ Greek letters it looks a capital E]).
Then use the SUM() function to total a column:

Total: Sum(Total_Assigned)

 

[John Spencer (MVP)]

Do you have a field that tells the database taht a particular aircraft is
fixed-wing or rotary wing? If not, then you have a difficult problem ahead.

Assuming a WingType field that is a text field and a fairly small table, you can
use the VBA DSum function.

SELECT Ac_Designation, Total_Assigned, Notes,
DSum("Total_Assigned","YourTable","WingType=""" & [YourTable].[WingType] & """")
as WingTotal
FROM YourTable

You can also use two queries or a sub-query to get the values you want. Post
back with details.

Thank you for your input. Unfortunately, after taking your advice I still
came out with the same previously erroneous output that is:

AC_Designation Total_assigned Notes Total
AV-8B_Harrier 6 Radar-4, N.A.-2 6
C-130_Herc 4 None 4

H-1_Skidz 17 Cobra-11, Huey-6 17
H-46_Phrog 19 None

Any other ideas? I would appreciate your input. Thanks.

I have created a table with a corresponding report to show the different type
of aircraft, quantity and corresponding notes that is to participate in a
particular training evolution. The table and report work fine to show the
individual amounts, for example:
AC_Designation Total_assigned Notes
AV-8B_Harrier 6 Radar-4, N.A.-2
C-130_Herc 4 None
H-1_Skidz 17 Cobra-11, Huey-6
H-46_Phrog 19 None

I have been attempting to show a total of the number of Fixed Wing
(Airplanes) and Rotary Wing (Helicopters) that are due to participate. I have
been trying to get it to look like this:

AC_Designation Total_assigned Notes Total
AV-8B_Harrier 6 Radar-4, N.A.-2 10
C-130_Herc 4 None 10
H-1_Skidz 17 Cobra-11, Huey-6 36
H-46_Phrog 19 None 36

Or something similar to that. I haven't even attempted to sort the results
between the different type of aircraft yet because I haven't been able to get
a total count of all of the aircraft yet. I tried using the following in the
already setup query:
Total: ([Total_assigned])
All it did was return the total for each individual aircraft under the
'total' column:

AC_Designation Total_assigned Notes Total
AV-8B_Harrier 6 Radar-4, N.A.-2 6
C-130_Herc 4 None 4

H-1_Skidz 17 Cobra-11, Huey-6 17
H-46_Phrog 19 None

What am I missing and what can I do to alleviate this problem? I look
forward to your response.

Make sure the query is a summary query (click the epsilon key on the
toolbar [if you're unfamiliar w/ Greek letters it looks a capital E]).
Then use the SUM() function to total a column:

Total: Sum(Total_Assigned)

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You need to show the SQL of the query and, possibly, the structure of
your source tables, and, some raw data examples of each table.

I'm not sure, but it looks like you want a running total of
"Total_assigned." Is that correct? If so, that can probably be
created, but we need to know more about your data structure. You'd need
something(s) that indicates a record's chronological or "something"
order. I.e., can we tell, just by looking at a record, if it comes
before or after another record. Those indicators in the record will be
used to create a running total query.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQcjhqIechKqOuFEgEQKtzwCgrH5xkAyCmqN7EwVJ+1oVeaAEMeEAoMgg
zEehg3mFOycwjx0p+1x2Bln9
=m/Qg
-----END PGP SIGNATURE-----

 

[Guest]

Thank you for your response. In what fashion could I setup two queries or a
sub-query? Frankly, I am quite new to fashioning queries and am need of a
good resource to study or read. Any suggestions? Also, after not getting
anything to work, I played around with the report based on the query after
reading a bit on grouping. The query still wouldn't work as I want it to but
by creating an unbound text box with =SUM(Total_assigned) and placing it in
the same area as the "group" (there is only one group by default, I assume
because I haven't specified any groups yet) I was able to get a total of all
the aircraft that appeared on the report. This was exactly the result that I
was trying to accomplish. However, I assumed with the power of a query, that
it could do it just as well. Is there a better way to do this?

Do you have a field that tells the database taht a particular aircraft is
fixed-wing or rotary wing? If not, then you have a difficult problem ahead.

Assuming a WingType field that is a text field and a fairly small table, you can
use the VBA DSum function.

SELECT Ac_Designation, Total_Assigned, Notes,
DSum("Total_Assigned","YourTable","WingType=""" & [YourTable].[WingType] & """")
as WingTotal
FROM YourTable

You can also use two queries or a sub-query to get the values you want. Post
back with details.

Thank you for your input. Unfortunately, after taking your advice I still
came out with the same previously erroneous output that is:

AC_Designation Total_assigned Notes Total
AV-8B_Harrier 6 Radar-4, N.A.-2 6
C-130_Herc 4 None 4

H-1_Skidz 17 Cobra-11, Huey-6 17
H-46_Phrog 19 None

Any other ideas? I would appreciate your input. Thanks.

jm3099 wrote:
I have created a table with a corresponding report to show the different type
of aircraft, quantity and corresponding notes that is to participate in a
particular training evolution. The table and report work fine to show the
individual amounts, for example:
AC_Designation Total_assigned Notes
AV-8B_Harrier 6 Radar-4, N.A.-2
C-130_Herc 4 None
H-1_Skidz 17 Cobra-11, Huey-6
H-46_Phrog 19 None

I have been attempting to show a total of the number of Fixed Wing
(Airplanes) and Rotary Wing (Helicopters) that are due to participate. I have
been trying to get it to look like this:

AC_Designation Total_assigned Notes Total
AV-8B_Harrier 6 Radar-4, N.A.-2 10
C-130_Herc 4 None 10
H-1_Skidz 17 Cobra-11, Huey-6 36
H-46_Phrog 19 None 36

Or something similar to that. I haven't even attempted to sort the results
between the different type of aircraft yet because I haven't been able to get
a total count of all of the aircraft yet. I tried using the following in the
already setup query:
Total: ([Total_assigned])
All it did was return the total for each individual aircraft under the
'total' column:

AC_Designation Total_assigned Notes Total
AV-8B_Harrier 6 Radar-4, N.A.-2 6
C-130_Herc 4 None 4

H-1_Skidz 17 Cobra-11, Huey-6 17
H-46_Phrog 19 None

What am I missing and what can I do to alleviate this problem? I look
forward to your response.


Make sure the query is a summary query (click the epsilon key on the
toolbar [if you're unfamiliar w/ Greek letters it looks a capital E]).
Then use the SUM() function to total a column:

Total: Sum(Total_Assigned)