Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 99
  • Last Modified:

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

0
Dustin Stanley
Asked:
Dustin Stanley
  • 11
  • 8
3 Solutions
 
Antonio Salva RipollCommented:
Hi.

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

Your sentences take data from different sources, and you must implement the way to choose one or other.

To check if you have a record is very simple. You can use the Count clause for the table you want to check.

SELECT Count(*) As MyCount FROM [TableName] WHERE [Criteria]

Open in new window


If you are more specific in the questions, I can help you more.
0
 
Gustav BrockCIOCommented:
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
0
 
Dustin StanleyEntrepreneurAuthor Commented:
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

0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Gustav BrockCIOCommented:
Yes, say, a checkbox or an option group as "flag".

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

Nothing wrong with that. In fact, it can be done:

Combined "update or append" query

/gustav
0
 
Dustin StanleyEntrepreneurAuthor Commented:
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.
db89.jpg
0
 
Gustav BrockCIOCommented:
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
0
 
Dustin StanleyEntrepreneurAuthor Commented:
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.
0
 
Gustav BrockCIOCommented:
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
0
 
Dustin StanleyEntrepreneurAuthor Commented:
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.
0
 
Dustin StanleyEntrepreneurAuthor Commented:
This might help.
db102.jpg
0
 
Gustav BrockCIOCommented:
I would add a record with the location if it doesn't exist. It's the only option.

/gustav
0
 
Dustin StanleyEntrepreneurAuthor Commented:
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.
0
 
Gustav BrockCIOCommented:
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
0
 
Dustin StanleyEntrepreneurAuthor Commented:
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?
0
 
Gustav BrockCIOCommented:
The append query can see whatever value you pass to it, so if you pass the values of the comboboxes, it will know about the locations.

/gustav
0
 
Dustin StanleyEntrepreneurAuthor Commented:
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.
0
 
Gustav BrockCIOCommented:
This expert suggested creating a Gigs project.
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
0
 
Dustin StanleyEntrepreneurAuthor Commented:
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.
0
 
Dustin StanleyEntrepreneurAuthor Commented:
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.
0
 
Dustin StanleyEntrepreneurAuthor Commented:
Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 11
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now