How to add an additional summed up Quantity

[dohernan]

I have a Report that runs off the following Query-

SELECT PersonnelForms2009.[Form Type], Count(PersonnelForms2009.[Form Type])
AS QTY
FROM PersonnelForms2009
WHERE ((([Completed]) Between CVDate(#9/23/2009#) And CVDate(#9/29/2009#)))
GROUP BY PersonnelForms2009.[Form Type];


I have been asked to add an additional Form Type, "Change of Address" but I
won't get the Records/numbers the way I've been getting the others- I will
just be given the final tally for each week.

I am not sure how to plug in this final number and still have my
Query/Reports work.

There's a Pie Chart in my weekly report, and the Row Source looks like-

SELECT [Form Type],Sum([QTY]) AS [SumOfQTY] FROM [StatsQ9_29_09] GROUP BY
[Form Type];

I also have a Total Sum that adds up all Form Types for the week that has to
somehow pick up the new Changes of Address total-
=Sum([QTY])

Thanks for your help.

 

[Duane Hookom]

I would start by adding a yes/no field [CountIt] to your table of unique form
types that identifies if the records in PersonnelForms2009 are counted or
summed. This will make it easy to accomodate future changes in how forms are
recorded.

Then, your query might look like:

SELECT PersonnelForms2009.[Form Type], Sum(IIf([CountIt] = -1,1,
[FinalTally])) AS QTY
FROM PersonnelForms2009 INNER JOIN tblFormTypes ON PersonnelForms2009.[Form
Type] = tblFormTypes.[Form Type]
WHERE ((([Completed]) Between CVDate(#9/23/2009#) And CVDate(#9/29/2009#)))
GROUP BY PersonnelForms2009.[Form Type];

 

[dohernan]

Thanks.

Form Types is a Combo Box Value List, in the table it's Row source is just a
list.

I'm not sure how to to add 'Change of Address", Countit Yes/No to my table
as it stands.

Is it possible to leave the original table alone and just add the Change of
Address Sum in the Query / Report?

Thanks again.

 

[Duane Hookom]

I never use a value list since it requires changing structure/design rather
than added a record to a lookup table. You also didn't provide the name of
the field for the "tally". When you add more form types or change how these
are recorded, you will be stuck having to update expressions rather than
data.

If you don't have a table then you can try:

SELECT PersonnelForms2009.[Form Type],
Sum(IIf([Form Type] <> "Change Of Address",1, [FinalTally])) AS QTY
FROM PersonnelForms2009
WHERE ((([Completed]) Between CVDate(#9/23/2009#) And
CVDate(#9/29/2009#)))
GROUP BY PersonnelForms2009.[Form Type];

--
Duane Hookom
Microsoft Access MVP

Thanks.

Form Types is a Combo Box Value List, in the table it's Row source is just a
list.

I'm not sure how to to add 'Change of Address", Countit Yes/No to my table
as it stands.

Is it possible to leave the original table alone and just add the Change of
Address Sum in the Query / Report?

Thanks again.

I would start by adding a yes/no field [CountIt] to your table of unique form
types that identifies if the records in PersonnelForms2009 are counted or
summed. This will make it easy to accomodate future changes in how forms are
recorded.

Then, your query might look like:

SELECT PersonnelForms2009.[Form Type], Sum(IIf([CountIt] = -1,1,
[FinalTally])) AS QTY
FROM PersonnelForms2009 INNER JOIN tblFormTypes ON PersonnelForms2009.[Form
Type] = tblFormTypes.[Form Type]
WHERE ((([Completed]) Between CVDate(#9/23/2009#) And CVDate(#9/29/2009#)))
GROUP BY PersonnelForms2009.[Form Type];

 

[dohernan]

I had thought that we wouldn't be adding anymore Form Types, so the dropdown
list seemed to make sense.
I am an Access novice and this is the first time I've ever actually made
anything like this.

The Query you posted works, but I don't see how to add the 'Change of
Address' Total Sum #? When it asked for Final Tally I told it "6" -it didn't
show up, but it did correctly tell me that I've had 3 Housing reports in the
last week.


Every week someone is going to throw the number at me, like "6"
and somehow that's supposed to fit in with the more complicated Records I
have for everything else. Will I have to make a table that just holds that
one number and is somehow tied into the "Form Types" of the main table?
"Change of Address" doesn't exist in the PersonnelForms2009 table.

Thanks again.

 

[Duane Hookom]

You stated "I will just be given the final tally for each week". Where are
you storing this in your table? If you aren't storing it then how can you
tally it?

If you need to display all form types even if none have been entered for a
time period then you really need a table of Form Types. You should have this
regardless of anything else. It should be in your application and it should
be the Row Source of all combo boxes for Form Type on all of your
forms/screens.

--
Duane Hookom
Microsoft Access MVP

I had thought that we wouldn't be adding anymore Form Types, so the dropdown
list seemed to make sense.
I am an Access novice and this is the first time I've ever actually made
anything like this.

The Query you posted works, but I don't see how to add the 'Change of
Address' Total Sum #? When it asked for Final Tally I told it "6" -it didn't
show up, but it did correctly tell me that I've had 3 Housing reports in the
last week.


Every week someone is going to throw the number at me, like "6"
and somehow that's supposed to fit in with the more complicated Records I
have for everything else. Will I have to make a table that just holds that
one number and is somehow tied into the "Form Types" of the main table?
"Change of Address" doesn't exist in the PersonnelForms2009 table.

Thanks again.

I never use a value list since it requires changing structure/design rather
than added a record to a lookup table. You also didn't provide the name of
the field for the "tally". When you add more form types or change how these
are recorded, you will be stuck having to update expressions rather than
data.

If you don't have a table then you can try:

SELECT PersonnelForms2009.[Form Type],
Sum(IIf([Form Type] <> "Change Of Address",1, [FinalTally])) AS QTY
FROM PersonnelForms2009
WHERE ((([Completed]) Between CVDate(#9/23/2009#) And
CVDate(#9/29/2009#)))
GROUP BY PersonnelForms2009.[Form Type];

 

[dohernan]

I only want to store "Change of Address" and it's Tally in the weekly Query I
do.
That is the base for the weekly report. It's a new Query every week, and I
figured I could just add an extra line or 2 to it somehow.

I don't need to display all Form Types in the Report if there were none done
for the week.

The table I use Tracks when Forms were received, completed, who they were
for, their social etc. I usually use it in datasheet form ,looks like Excel,
and I have a Form based on it that has Form Types as a drop down list so I
can add in new Records easily. All of these forms I have a handle on.

The last type of Form, Change of Address, is not one I handle, so I'm not
keeping track of who, what where etc., and the person that does handle it
just gives me a weekly tally.

That's the problem, it doesn't fit in with my original table at all, yet I
somehow need to count it as something done by my office during the weekly
reports.

I will work on making a table of Form Types, but it's just a simple column?

 

[Duane Hookom]

Your table of form types only needs to be a single column.
I would probably create a new table for the change of address form tally. It
might have three fields:

[Form Type] "change of address"
[Completed] datetime
[Qty] Number

You could then create a union query of your PersonnelForms2009 and
tblOtherForms:

SELECT PersonnelForms2009.[Form Type],
Count(PersonnelForms2009.[Form Type]) AS QTY
FROM PersonnelForms2009
WHERE ((([Completed]) Between CVDate(#9/23/2009#) And CVDate(#9/29/2009#)))
GROUP BY PersonnelForms2009.[Form Type]
UNION ALL
SELECT tblOtherForms.[Form Type], Sum(Qty)
FROM tblOtherForms
WHERE ((([Completed]) Between CVDate(#9/23/2009#) And CVDate(#9/29/2009#)))
GROUP BY tblOtherForms.[Form Type];

 

[dohernan]

Thank you so very much!

I created the Table for the Form Types, I created the extra table for the
Change of Address Forms, and then I updated the Query.

It all works!