Solved

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

Posted on 2016-10-29
20
40 Views
Last Modified: 2016-11-01
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
Comment
Question by:Dustin Stanley
  • 11
  • 8
20 Comments
 
LVL 2

Assisted Solution

by:Antonio Salva Ripoll
Antonio Salva Ripoll earned 250 total points
ID: 41865750
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41865772
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
 

Author Comment

by:Dustin Stanley
ID: 41867491
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
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 250 total points
ID: 41867601
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
 

Author Comment

by:Dustin Stanley
ID: 41867761
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41868208
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
 

Author Comment

by:Dustin Stanley
ID: 41868355
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41868368
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
 

Author Comment

by:Dustin Stanley
ID: 41868395
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
 

Author Comment

by:Dustin Stanley
ID: 41868416
This might help.
db102.jpg
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41868425
I would add a record with the location if it doesn't exist. It's the only option.

/gustav
0
 

Author Comment

by:Dustin Stanley
ID: 41868501
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41868523
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
 

Author Comment

by:Dustin Stanley
ID: 41868528
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
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 250 total points
ID: 41868576
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
 

Author Comment

by:Dustin Stanley
ID: 41868588
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41868623
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
 

Author Comment

by:Dustin Stanley
ID: 41868768
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
 

Author Comment

by:Dustin Stanley
ID: 41868775
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
 

Author Closing Comment

by:Dustin Stanley
ID: 41869394
Thank you!
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now