inventory query

[C Parkins]

I have a macro with 3 queries I use to adjust my inventory. I have a problem
I just discovered with the first query. The first query looks at an excel
file from my barcode scanner and subtracts one from the qty. for each ID
number in the file. The problem is that the barcode file has several
instances of the same ID and I need to subtract one for each instance. I
assumed access processed the file sequentially and would subtract for each
occurence, apparently it only sees on ID number no matter how many are
actually there. I am using an update query and for the criteria I am using
QTY <=1; update QTY-1.

So I have file with numbers as follows for an example: 60280, 77585, 60280,
78881, 79564, 60280. So, when I run the query the field for qty of 60280
counts down by 1 instead of 3 Like I would desire.

Does anyone have a suggestion?

Thanks,

 

[C Parkins]

I made a mistake the query should be Qty >=1; then Qty-1

 

[Bob Barrows [MVP]]

I made a mistake the query should be Qty >=1; then Qty-1

Create a saved grouping query (call it TransactionCountPerID) with this sql:
Select ID, Count(*) As Transactions
from <spreadsheet>
group by id

Here are the steps for doing that:
1. Create a new query in Design View
2. Switch to SQL View using the View meno, or the toolbar button, or the
right-click context menu
3. Copy/Paste the above sql into the SQL View window
4. Fix the FROM clause (replace <spreadsheet> with the name of your linked
table
5. Save the query with the suggested name (File|Save, toolbar button, etc)



Then, in your update query, join to this saved query instead of to the
linked table and change the formula to QTY-TransactionCount

 

[C Parkins]

I found my problem, the query was setup on the table I was updating not the
file that the barcode data was in.

Thanks,

 

[C Parkins]

Thanks Bob,
the query works great, plus this may help in some future queries. I found
another method that works as well, But this will be great when I want to
calculate other fields.

Thanks again,

 

[C Parkins]

Bob,
Thanks for the help, I have one further problem. I am new to access and
learning as I go. I think I did all that your previous post said to do. When
I run the TransactionCountPerID I get the desired results. but when I create
the query to update the data I get an error that says the operation must use
an updatable query.
What did I do wrong? I think the problem is in the way I joined the queries,
Could you Help?

thanks

 

[Bob Barrows [MVP]]

Bob,
Thanks for the help, I have one further problem. I am new to access
and learning as I go. I think I did all that your previous post said
to do. When
I run the TransactionCountPerID I get the desired results. but when I
create the query to update the data I get an error that says the
operation must use an updatable query.
What did I do wrong? I think the problem is in the way I joined the
queries, Could you Help?

Darn! You've just reminded me that Access does not allow a grouping query to
be used in an update query ...
What you'll have to do is create a work table to store the results of the
grouping query. You can quickly do that by opening the saved query in Design
mode and selecting from the menu: Query|Make-table Query. It will prompt you
for the name of the table to create; something like wrkTransactionCountPerID
will do. Run the query. Then select from the menu: Query|Append Query.
Select the wrkTransactionCountPerID table from the combo. Access should fill
in the Append To row in the grid for you since the field names should match.
Save the query (don't run it yet).
Now, change the update query to join to wrkTransactionCountPerID instead of
TransactionCountPerID. It should work now.

Your process will need to be:
delete all records from wrkTransactionCountPerID
run TransactionCountPerID
run your update query

 

[C Parkins]

Hi again Bob,
Something is not right. When I go through the process I get an error that
Access cannot update the all the records in the update query. and the reason
is due to an type conversion failure. If I continue it just blanks the qty
for each of the records in the append query. I thought it might be a data
format error but all the formats are the same. Any ideas? I have another
method of getting the results that for me is easier but I like this format
that you have shown me and would like to adapt it in some other areas in the
future. So, even if I can do my updates my way I would still like to make
your way work.

Thanks,

 

[Bob Barrows [MVP]]

I can't answer definitively without knowing the datatypes of the fields
involved. What is the datatype of that QTY field (are we still talking about
the QTY field)?

It sounds as if you are going to need to go through a painstaking debugging
process:
Delete all but one of the records from the work table (it doesn't matter
which one you leave).
Run the update query - does the error still occur? If so, the problem is
with the datatype of the field in the work table.

If not, and the update query provides the correct result in the destination
table, then at least one of the records from the spreadsheet has a problem
and you need to identify which one(s) it is:

Delete all the records from the work table.
Run the Append query.
Delete half the records from the work table.
Run the update query.
If the error does not occur, refresh the records in the work table, delete
the records which just passed the test and try again.
If the error occurs, delete half of the remaining records. Repeat until you
isolate the problem record(s). Can you see the problem with them?

 

[Bob Barrows [MVP]]

PS. Data "format" is only used to control how data is displayed. It is not
relevant to this problem. Data types (Number - Long, Integer, Single Double,
Text, etc) is what you need to be concentrating on.