How to run update query many time for each item number?? 2

[afekri]

Hi,

I have a table include Item numbers and names ,...
and also another table which is Quantity in and Quantity Out and Quantity in Stock.

I have a Update query which will recalculate the quantity in stock for one item number which i put in criteria.

I have to run this update query one by one for all the item numbers which is in the first table..

How can I do?? Please help me..

Thanks for any idea..
Ali

 

[LyndonOHRC]

You have a [quantity in stock] column in the [Item numbers and names] table that you are updating using the [quantity in stock] column in the [Quantity in and Quantity Out] table?

Including the sql you are running now would surely get you some help completing your work.

Lyndon

---People Remember about 10% of what you say ---They never forget how you made them feel. Covey

 

[MichaelRed]

Normalization?

Why would you keep QtyInStock as persistent (table) data? Is it not readily computed?




MichaelRed

 

[afekri]

ok Thanks for your replies,

Yes,

Table a: 1) item number 2) name 3) quantity in stock
Table B: 1) item number 2) Quantity In 3)quantity out

in some case which the quantity in stck shows wrong bacuase of any reason, user must recalculate it.

so I want by one click and command, for each item number the calculation to be repeat.

I have the calculation on query. when i put one item number in this query criteria. works well and quantity in stock will correct.

So how I can repeat to run the same query for each item number automaticly just by push once the command button??

Thanks for your help
Ali

 

[PHV]

What is the SQL code of your actual query ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MichaelRed]

'QtyInStock' would 'normally' be adjusted on the basis of (a manual) inventory. the 'QtyIn' & 'QtyOut' are not usually necessary except when associated with receiving and sales for tracking transactions. in either instance, op could just enter the 'QtyInStock' and have your app make the adjustment via whatever process you use for reconcillation.

Wholesale and retail processes arfe generally somewhat different for namagement of inventory, however it generally is not adviseable to track (e.g. maintain a persistient record of) transactions in an MS Access database.

my experience in several applications is that the transaction logging (recordkeeping) quickly becomes the largest single recordset in the db and in most instances becomes the choke point for most/all processing. if you must maintain this, it should be in seperate database and only referenced when absoloutly necessary (e.g. for analysis of specific issues).



MichaelRed

 

[afekri]

Thanks for your advise,

Yes you are right but in our database we need these tracking and also we have no problem in calculation of large records.

I attached the sample of job which I'm doing one by one in a form by one click on the footer. it's take time to change the record and click the button and again this job for the next record.

I need just to automate this job.

Thanks
Ali

 

[LyndonOHRC]

Ali,
You need to cut and paste your sql code into this thread not your entire database!

Lyndon

---People Remember about 10% of what you say ---They never forget how you made them feel. Covey

 

[afekri]

Ok, But my recalculation is 7 query, That's why I attached the database.

But I don't think that you need the sql code, to reply me what I asked!!!!

Let me make simple my question:

If I want to have a VBA code to repeat the following sql only:

INSERT INTO [tempInventory items] ( [Item Number], Location )
SELECT [Inventory items].[Item Number], [Inventory items].Location
FROM Inventory INNER JOIN [Inventory items] ON Inventory.[Item Number] = [Inventory items].[Item Number]
WHERE ((([Inventory items].[Item Number])=[Forms]![Inventory Review]![Item Number]) AND (([Inventory items].Location)=[Forms]![Inventory Review]![Location]));


1)How many times the code to be repeat??
the record's quantity of a specific table
2)on that specific table's record has [Item Numebr] and [Location] which the above sql criteria need it.
3) on each repeat of the sql line, the value of [item number] and [location] of that record will be placed to the sql criteria.

I hope my explain to be well.

Thanks
Ali

 

[lespaul]

Ali,
The reason you are having so much difficulty getting the assistance you want is because you are asking us to show you how to do something that none of us would do. NONE of us would have a calculated field in a database.

Since you are insisting on setting up this database this way we will do all we can to help you, but you will have to accommodate our requests for information.

If I were you I would create a public function 'UpdateQty' which takes in the item number and the number to adjust the quantity by as parameters and do the update in the function. You are going to have to call this function from every place in your database that changes the quantity of an item.

Good luck

Leslie

Come join me at New Mexico Linux Fest!

 

[afekri]

Yes, I do this on the form when I'm on the record of one of the item number and I can do recalculate by that item number just. I can't do this calculation in vb. Just I could make it in query.

because I need to repeat for all the item number with one click, I have to make command in vb....

I just needed to recalculate all the item number in same way, (By run the query) just repeat it for all the item number automatically in vb.

I don't think that to be imposible!!!

Thanks
Ali

 

[lameid]

Generally, if you want to run an action query that effects more records, you modify the criteria or where clause to be appropriately less restrictive.

When you say you want to run it for all items, I am thinking, remove the criteria for items.

 

[afekri]

Thanks,

I think My question became complicated.

I make it simple:

table AAA: include 10 records with two fields "Item" and "location".
Form BBB: include 2 text box with record source of table AAA
Query BBB: SELECT AAA.Item, AAA.Location
FROM AAA
WHERE (((AAA.Item)=[Forms]![AAA]![Item]) AND ((AAA.Location)=[Forms]![AAA]![Location]));

""the query is just for sample"""

how to make an event to start from record 1 in the form and run the query in each record up to last record...

Thanks for your help
Ali

 

[lameid]

Why is SQL like the following a problem then?

SELECT AAA.Item, AAA.Location
FROM AAA

 

[afekri]

Thanks, you are right I corrected lke this:


I think My question became complicated.

I make it simple:

table AAA: include 10 records with two fields "Item" and "location".
Form CCC: include 2 text box with record source of table AAA
Query BBB: INSERT INTO DDD ( Item, Location, Quantity )
SELECT AAA.Item, AAA.Location, AAA.Quantity
FROM AAA
WHERE (((AAA.Item)=[Forms]![CCC]![Item]) AND ((AAA.Location)=[Forms]![CCC]![Location]));


""the query is just for sample"""

how to make an event to start from record 1 in the form and run the query in each record up to last record...

Thanks for your help
Ali

 

[lameid]

You have over simplified it even in your original posts which is why people are asking you for specific information.

I would guess that you could not write an update query based on an aggregate sub query, so you wrote a procedure using a recordset to perform the update. If this is the case, you should be able to use a recordset to loop through all the records the form is based on and run your code appropriately. This is a lot of code that is much slower than simply calculating the result (MichaelRed's post).

If you don't really use a recordset then I'm 99% sure you can just kill the criteria in your query and eveything will be fine. It could matter but you haven't said why, posted your VBA/SQL code so it is really hard to say what you can do with certainty.

 

[afekri]

Ok, If I want to write all my calculation here, would be high comfused.

Just renew my question please, and forget every thing.


Let me ask you only:

How to repeat the line : msgbox "hello"

in depend of table AAA record's quantity.

It means if the table AAA have 12 records, by the specific procedure I need to run 12 time msgbox "hello"

If I know it how to do, I will complete my job..



Thanks
Ali

 

[lameid]

No one here would move through the records of a form to perform any task, except for maybe testing form events and I am betting everyone would test some other way than that. As I mentioned before, if I wanted to use code for each record, I would use a recordset (assuming SQL is inappropriate).

Again, what is your code (SQL or VBA)?

 

[lespaul]

what you need to do is write a query that gives you the records you want:

SELECT * FROM TableName

and then using VBA loop through the recordset (the results from the query) and do your calculation.

rs.MoveFirst
Do Until rs.EOF
                    
 //some process you need to do on every record in query
rs.MoveNext
Loop

Leslie

Come join me at New Mexico Linux Fest!

 

[afekri]

Thanks lespaul, I think you are helping me very closed which I want to know.

Ok, If you have seen in my attached file, I have to run the queries for all the item numbers on the records one by one.

So it's good idea that I can use recordset But when recordset is changing the record, how query can get the information of same record???

Can you help me more?/
Thanks