Link to home
Start Free TrialLog in
Avatar of Dustin Stanley
Dustin Stanley

asked on

Ms Access Combine Two Update SQL Queries into one and Add Record If Not Existing.

I have two Update Queries I need to combine into one action and If a record doesn't already exist for the addition query I need it to add a record for me. Thank you!


Addition Query:
UPDATE (Products INNER JOIN ProdMovements ON Products.ProductID = ProdMovements.ProductID) INNER JOIN ProdLocations ON Products.ProductID = ProdLocations.ProductID SET ProdLocations.QtyLoc = [qtyloc]+[Forms]![frmStockTransfer]![QtyMove]
WHERE (([ProdLocations].[LocID]=[Forms]![frmStockTransfer]![LocID]));

Open in new window



Subtraction Query:
UPDATE (Products INNER JOIN ProdMovements ON Products.ProductID = ProdMovements.ProductID) INNER JOIN ProdLocations ON Products.ProductID = ProdLocations.ProductID SET ProdLocations.QtyLoc = [qtyloc]-[Forms]![frmStockTransfer]![QtyMove]
WHERE (((ProdLocations.LocID)=[Forms]![frmStockTransfer]![ProdLocLocID]));

Open in new window

SOLUTION
Avatar of Antonio Salva Ripoll
Antonio Salva Ripoll
Flag of Spain image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Gustav Brock
To combine the two queries, have an additional parameter/textbox named, say, AddOrSubtract which you set to either 1 or -1 before calling the query:

UPDATE (Products INNER JOIN ProdMovements ON Products.ProductID = ProdMovements.ProductID) INNER JOIN ProdLocations ON Products.ProductID = ProdLocations.ProductID SET ProdLocations.QtyLoc = [qtyloc]+[Forms]![frmStockTransfer]![AddOrSubtract] * [Forms]![frmStockTransfer]![QtyMove]
WHERE (([ProdLocations].[LocID]=[Forms]![frmStockTransfer]![LocID]));

Open in new window

/gustav
Avatar of Dustin Stanley
Dustin Stanley

ASKER

have an additional parameter/textbox named, say, AddOrSubtract which you set to either 1 or -1
gustav.

So you are saying a flag field on the form along side the other fields?

I'm no expert but I was assuming (Never should do that) that there would be a cleaner way to combine them into one.

It's not good idea to join two action queries into one.
Antonio.

This code below is currently how I am running the two queries. Is this the safer bet? It does work but like I said just thinking of ways to shorten, speedup, and cleanup codes.

Private Sub Command32_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryStockTransferDeductFrom"
DoCmd.OpenQuery "qryStockTransferAddTo"
DoCmd.SetWarnings True

End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Ok I see and that is a good thread. I will try some of those suggestions. All though with the flag Idea my form is all on one line. As in each line say: ProductID, Qty,To Location, From Location. The two queries I am using. One deducts the Qty from the From Location and the other query adds the Qty to the To Location. So a flag in that row would not work. At least in my eyes.
User generated image
From what you show, a flag is not needed. Quantity must always be positive, and "From" and "To" will determine if the quantity should be added or subtracted at the chosen location.

/gustav
Ok so basically the way I have the queries now is good? Also how can I add a new record if there is not an already existing record when I add to the TO Location? All the FROM Locations will already exist. But sometimes there will be new TO Locations.
That shouldn't be possible. How can you move a quantity to a non-existing location (into the middle of nowhere)?

So make sure the relevant locations are created first.

/gustav
All the Locations already exist in a table called Locations. Locations hold all the location names. I have another table called ProdLocations. ProdLocations is the table where I insert the ProductID, LocID,and QtyLoc (Quantity in location). So in ProdLocations it will sometimes not have an existing record for that exact productID in that exact LocID where the product is being moved TO. Basically sometimes with the TO Location I will be updating a record and sometimes I might have to ADD a record if it doesn't already exist. Thanks.
This might help.
User generated image
I would add a record with the location if it doesn't exist. It's the only option.

/gustav
So you mean just manually add the ProductID and LocID to the ProdLocations Table before the updates?

I trust you as in the only option but (This is not anywhere close to real code sorry) can't you be like:

       If record already exists for ProductID & LocID in Prodlocations = True Then
         Call UpdateAddQuery
   Else
         Call SomeKindOfAppendQuery (Makes the record automatically. Set at 0 quantity before calling the UpdateAddQuery)
       Call UpdateAddQuery
End If


Just what I'm thinking but not sure how to put that one together.
That could be done, but then you couldn't limit the comboboxes for locations to existing values only, meaning that anything - including errors - could be entered for a location. This somehow would defeat the purpose of a combobox.

But it is a matter of preferences.

/gustav
I understand that an append query adds new records. Anything you put into the query it adds. Errors that would be horrible. The part I don't get is I can't choose from the combo boxes. The comboxes do allow the user to choose a location without error (atleast not adding a fictional location). But why can't the append query see the name location in the form combobox and use that?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Ok thank you. Could you give me a example code to go off of please for the scenerio I gave above. Im not very good at just writing them up. I usually look over code and alter and test from there.  Thanks for the help.
Ah, that would be specific to your application. Also, have in mind please, that people here advise and help but cannot be expected to write your application.

You could open a "Gig" however ...

/gustav
No I don't want an exact code. I understand though and I don't want you to work for me as I like doing it. More like hints in a direction.  I  am pretty sure I know how to all of it but this part.

If record already exists for ProductID & LocID in Prodlocations = True Then


Checking Tables and such is kinda confusing to me now.

I know how to do the queries and call them just not how to check before calling.

I really appreciate the help and understand it is just advice. There is a lot of knowledge here.
Also a Gig would be more of a code given to me and I would still then be trying to tear it apart learning how it works. Thanks for the suggestion.
Thank you!