final count query

[mcnewsxp]

i have 3 count columns - count1 count2 count3
count 2 & 3 will only be populated if there is a variance in the previous on
hand count.
in the end i want to populate a forth column called finalcount.
i know how to do this with VB code, but how would i do this with an update
query?

tia,
mcnewsxp

 

[Tom van Stiphout]

On Thu, 16 Oct 2008 09:49:41 -0400, "mcnewsxp"

Bad database design leads to difficult queries.

If you persist in this course of action I see a complicated statement
with nested IIf and IsNull in your future.

-Tom.
Microsoft Access MVP

 

[mcnewsxp]

your reply helps me how?

On Thu, 16 Oct 2008 09:49:41 -0400, "mcnewsxp"

Bad database design leads to difficult queries.

If you persist in this course of action I see a complicated statement
with nested IIf and IsNull in your future.

-Tom.
Microsoft Access MVP

 

[mcnewsxp]

what's the better design?

On Thu, 16 Oct 2008 09:49:41 -0400, "mcnewsxp"

Bad database design leads to difficult queries.

If you persist in this course of action I see a complicated statement
with nested IIf and IsNull in your future.

-Tom.
Microsoft Access MVP

 

[Tom van Stiphout]

On Thu, 16 Oct 2008 10:46:20 -0400, "mcnewsxp"

The combination of IIf and IsNull can test the values in the countX
columns.

-Tom.

 

[Tom van Stiphout]

On Thu, 16 Oct 2008 11:20:01 -0400, "mcnewsxp"

Inventory management is a complex problem. Here is one link to start
thinking about the problem:
http://allenbrowne.com/AppInventory.html

Your countX columns are a violation of an important database design
rule called First Normal Form (e.g.
http://support.microsoft.com/kb/283878)

Storing a calculated value like FinalCount is not a violation of the
Normal Forms, but is still a practice that should be weighed
carefully. Often it is a balance between performance and the
additional complexity of keeping the calculated field updated.

-Tom.

 

[mcnewsxp]

excellent link. thanks.

my task was to create something "simple" that can used to verify the
semi-annual warehouse floor counts for medium sized comapny. this company
mantians 3 spreadsheets that will be imported into the access app. my
"boss" says he wants to be able to adapt the app to other locations etc so
he didn't want a lot of complicated queries or VB code to muddle through.
anyway - i kept it is bare bones and easy to understand as i could.