Solved

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

Posted on 2016-10-29
20
74 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 50

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50

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 50

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 50

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
 
LVL 50

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 50

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 50

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 50

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

729 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