sum and compare query

[mcnewsxp]

i have an inventory program that requires a variance report.
the count table needs to be compare to the previous on hand count.
the previous on hand count will contain an item number and a quantity.
the count table may contain more than one instanc eo f the item number in
different locations.
at least two counts are taken and sometimes three.
my variance report(s) need to sum the count for each item in the count table
in order to campare to the on hand table to see if an item should be
included in the variance report and included for further data entry.
should i just add a boolean flag in the count table, create a sum query that
can be used to find variance in the on hand table and set the flag to true
in each of the item's boolVariance flag?

thanks,
mcnewsxp

 

[mcnewsxp]

i have already gotten to the point you have described.
so now i need to view the individual items and locations where a variance
has occured so i can create recount worksheets. so i need to be able
identify any item that after it has been summed and compared to on hand qty
where a variance appears.

see replies in-line below...

i have an inventory program that requires a variance report.
the count table needs to be compare to the previous on hand count.
the previous on hand count will contain an item number and a quantity.
the count table may contain more than one instanc eo f the item number in
different locations.

Where is the "previous on hand count" located?

at least two counts are taken and sometimes three.

Are you talking about database action or physical inventory management?

my variance report(s) need to sum the count for each item in the count table
in order to campare to the on hand table to see if an item should be
included in the variance report and included for further data entry.

So, you have a table that lists the item and the "previous on hand count"?

should i just add a boolean flag in the count table, create a sum query that
can be used to find variance in the on hand table and set the flag to
true
in each of the item's boolVariance flag?

If I'm understand your description, you have zero-to-many records with
[Item] and [Count] in a table, and another table with [Item] and [Previous
On-Hand Count].

If you create a Totals query on the first table to tally up the total
count
of each [Item] (use GroupBy on [Item], Sum on [Count]), then create a
second
query to join the first to the 'previous' table, you could find out which
[Item]s have discrepant count/totals. In that second query, you could add
a
field that takes the difference, and in the selection criterion of that
(new) field, you could only accept those that are other than 0 (zero).

NOTE! Null <> blank <> 0 ... you'll probably want to be sure to handle
Nulls (check Nz() function).

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

thanks,
mcnewsxp

 

[Jeff Boyce]

see replies in-line below...

i have an inventory program that requires a variance report.
the count table needs to be compare to the previous on hand count.
the previous on hand count will contain an item number and a quantity.
the count table may contain more than one instanc eo f the item number in
different locations.

Where is the "previous on hand count" located?

at least two counts are taken and sometimes three.

Are you talking about database action or physical inventory management?

my variance report(s) need to sum the count for each item in the count table
in order to campare to the on hand table to see if an item should be
included in the variance report and included for further data entry.

So, you have a table that lists the item and the "previous on hand count"?

should i just add a boolean flag in the count table, create a sum query that
can be used to find variance in the on hand table and set the flag to true
in each of the item's boolVariance flag?

If I'm understand your description, you have zero-to-many records with
[Item] and [Count] in a table, and another table with [Item] and [Previous
On-Hand Count].

If you create a Totals query on the first table to tally up the total count
of each [Item] (use GroupBy on [Item], Sum on [Count]), then create a second
query to join the first to the 'previous' table, you could find out which
[Item]s have discrepant count/totals. In that second query, you could add a
field that takes the difference, and in the selection criterion of that
(new) field, you could only accept those that are other than 0 (zero).

NOTE! Null <> blank <> 0 ... you'll probably want to be sure to handle
Nulls (check Nz() function).

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Jeff Boyce]

If your queries are set up, as you indicated, to show you the rows where one
isn't equal to the other, just include those two fields in your query so
you can compare them.

Or do you mean something else by "compare"?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

i have already gotten to the point you have described.
so now i need to view the individual items and locations where a variance
has occured so i can create recount worksheets. so i need to be able
identify any item that after it has been summed and compared to on hand qty
where a variance appears.

see replies in-line below...

i have an inventory program that requires a variance report.
the count table needs to be compare to the previous on hand count.
the previous on hand count will contain an item number and a quantity.
the count table may contain more than one instanc eo f the item number in
different locations.

Where is the "previous on hand count" located?

at least two counts are taken and sometimes three.

Are you talking about database action or physical inventory management?

my variance report(s) need to sum the count for each item in the count table
in order to campare to the on hand table to see if an item should be
included in the variance report and included for further data entry.

So, you have a table that lists the item and the "previous on hand count"?

should i just add a boolean flag in the count table, create a sum query that
can be used to find variance in the on hand table and set the flag to
true
in each of the item's boolVariance flag?

If I'm understand your description, you have zero-to-many records with
[Item] and [Count] in a table, and another table with [Item] and [Previous
On-Hand Count].

If you create a Totals query on the first table to tally up the total
count
of each [Item] (use GroupBy on [Item], Sum on [Count]), then create a
second
query to join the first to the 'previous' table, you could find out which
[Item]s have discrepant count/totals. In that second query, you could add
a
field that takes the difference, and in the selection criterion of that
(new) field, you could only accept those that are other than 0 (zero).

NOTE! Null <> blank <> 0 ... you'll probably want to be sure to handle
Nulls (check Nz() function).

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

thanks,
mcnewsxp