Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

Append Query

I have 2 tables - both contain Stock Item names - I wish to add new stock lines to one table and then append these to the other table- trouble is that I get duplicates each time I run the append query - my questions is how do I stop the same items appearing twice - thanks Dave
0
g3rcq
Asked:
g3rcq
  • 13
  • 12
1 Solution
 
BillystyxCommented:
an update query would be the way to go ...

Billystyx
0
 
BillystyxCommented:
UPDATE Table1 SET Table1.stockitem= "111"
WHERE (((Table1.stockitem)="222"));

Billystyx
0
 
dannywarehamCommented:
You could also do an unmatched append query.
The wizard will talk you through this when you select to create a new query.

:-)
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
g3rcqAuthor Commented:
OK - thanks both for the help Q to both of you for further advice

Billystyx can you tell me where I put your coding? is it in the criteria field any help and further explanation please


dannywareham - I cannot find how the wizard helps me as it does not ask anything about unmatched - so how do I get to the unmatched wizard Q

regards Dave
0
 
BillystyxCommented:
You can paste this sql directly in the sql view screen of the query editor, but it won't give you any proper query until you adjust the values to suit your tables and fields.

In the query editor, set the table to the one you want to update, and select update query from the query menu at the top. The format should make sense and you can edit it all inside the query editor, without having to use the sql view at all.

Billystyx
0
 
g3rcqAuthor Commented:
ok I will try it - thanks for the immediate reply - regards Dave
0
 
g3rcqAuthor Commented:
I have tried the coding below - can you see any problems - as I cannot get it to work - as you can see I have substitued my table names - regards Dave

UPDATE [Stock Name File used as list for drop down], [Stock In and Out] SET [Stock Name File used as list for drop down].[Stock Name] = "111"
WHERE ((([Stock In and Out].[Stock Name])="222"));
0
 
BillystyxCommented:
which is the table you are adding records to, and which is the table you want the first table's records added to?
I am guessing now you want to add some records to one table, and then append records to the second, or update existing records if the data is different in the second table than in the first. Is this correct?
Billystyx
0
 
g3rcqAuthor Commented:
Yes I wish to add stock items to a table (no probs) which is the basis of the drop down in Stock Name Files used as list for drop down- this table then updates the Stock In and Stock Out table with the new stock items. Once I know how to do this I will wish to update 2 other tables as well but I am going one step at a time at the moment.

Dave
0
 
BillystyxCommented:
so it is an append, you don't want the same values in twice do you?
So do an append query - it won't add records where the pk is duplicated.

What you need to do is add an id field to the stockname files table, set it as a primary key with no duplicates.
Now add an ID field to the second table - stockin and out, with the same name, but this time as a number. Not as a primary key but with it set to no duplicates allowed.

Then just create a simpe append query. If there is a conflict of IDs (ie you are trying to add one to stock in and out where the id field is the same, it will not do it. It will append the other new records, but not the duplicate.

Billystyx
0
 
BillystyxCommented:
You can see the append query in the query editor go to query->append query, and it will ask you which table to append to.

Or you can do with sql like
insert into stockinandout select * from stocknamefiles

Billystyx
0
 
g3rcqAuthor Commented:
OK I have tried this on a completely new tables and after the first data is appended I get the error message conflict of data types - is this correct? it appears to work if I say proceed anyway - but I do not want the conflict error message appearing each time  - Dave
0
 
BillystyxCommented:
This means that the tables have different datatypes for the same fields. Which is not a good idea but will work if it doesn't have any problem values, for example between text and number.
You need to go into the appending to table and check all the datattypes and then see which ones don't match with those in the first table and change them.

Billystyx
0
 
g3rcqAuthor Commented:
OK well I am now geting the following ERROR MESSAGE

Access set 0 fields to null due to type conversions failure and it didnt add 4 records to the table due to key violations, 0 records due to lock violations and 0 records due to validation rule vilolations -

 if I carry on any way it appears to work - any comments Dave
0
 
BillystyxCommented:
Yes that is fine. If you are doing it through code then
docmd.SetWarnings false
...before the sql and

docmd.SetWarnings true

should stop the message appearing. But it is doing exactly what you want it to do  - not allowing duplicate records.

Billystyx
0
 
g3rcqAuthor Commented:
OK - thanks but need help on the following - I have it working at the moment with the error message

1st Q I have had to change the Additional ID field on both tables to an AutoNumber otherwise I had to manually put a number in this field - or I got the message Null value not allowed - is this a problem OR have I not followed your idea correctly with regard to the additional NO DUPLICATES filed?

The sql to remove the warning messages is causing me a problem - I have gone into the append query and gone to sql view - is this where you intended me to put the DoCmd.SetWarnings as it does not like it and my sql experience is almost zero.

So where do I put the sql code

Thanks for being patient on this one but we are almost there I am sure -

Best regards Dave
0
 
BillystyxCommented:
docmd.setwarning is vba code, soyou would need to execute this code in vba - under the loadevent of a form for example. (Depends on where and when you need it to happen).

try this
'not sure if you need the warnings here though
docmd.setwarnings false
strSQL="insert into stockinandout select * from stocknamefiles;"
currentdb.execute(strSQL)
docmd.setwarnings false

Is there some other field of each form where the id fields match up - are the same on each table? If so use that id field and and don't add a new.
If they run in order on both tables but don't have any other id field, then set them to autonumber, and then change the 'many' table to number once it has add the autonumber, and not a pk.

This may be the trickiest part...
How do you know they are duplciates by looking at the table? What fields tell you this?

Billystyx
0
 
g3rcqAuthor Commented:
OK about vba code

I am only working using tables at the moment - I have only just started on the Stock Control Prog and am trying to get the basic concept working first with stock in and out and updating my other tables -

I have three tables which are Current Stock - Previous Stock - Stock in & out

I have qures and macros working which when I put stock into alread entered stock names all the tables are updated - no problem

I then came to entering new stock names into the file Stock In & Out by way of a drop down based on a stock names etc table - so far so good

Problem is that when I try and update a new name into the other tables the data does not go accross unless I put the name into the table first - this brings us to the present situation of updating the other tables and getting rid of duplicates.

Now what I have done so far may be the incorrect approach but its working and I understand what I have done.

All the tables have a common field called Stock Name - I think this should help (your latest comment)

I am wondering - hence my explanation above that maybe you think I am further in than just tables

I feel that this is now a 250 point value question and have increased accordingly as I really do need the help

Dave

I then moved to the point of entering new stock items and found that my

0
 
BillystyxCommented:
can you elaborate on this?
>>unless I put the name into the table first - this brings us to the present situation of updating the other tables and getting rid of duplicates

as far as the vba code goes - paste it into a new module (goto modules->new)
put this in there

function myFunction
docmd.setwarnings false
strSQL="insert into stockinandout select * from stocknamefiles;"
currentdb.execute(strSQL)
docmd.setwarnings false
end function

you can run this code by going to the immediate window whilst in the module (view->immediate window)
and typing
?myFunction

and then pressing return
You will need to replace the names of the tab;les with the correct names first though

Billystyx
0
 
g3rcqAuthor Commented:
OK b4 elaboration - I must admit that some of what you are telling me is not familier to me and I do not understand all - OK nor for the elaboration.

I have 3 tables CURRENT STOCK
0
 
g3rcqAuthor Commented:
SORRY

PREVIOUS STOCK
STOCK IN STOCK OUT

I have 3 queries which update the 1st 2 tables from the stock in stock out table - the last query sets the stock in stock out back to zero in readiness for the next increase or decrease in stock.

In readiness for when I put all this into a forem I have a marco which runs all 3 queries - so thats all woking fine.

Now for the elaboration bit - when I put a NEW stock NAME  in the stock in stock out table my macro (3 queries) does not include the new name or any data attached on that line.

The only way I can get it to work correctly is by manually putting the new stock name into the 1st 2 tables -

This is where the append query Q started - I need to append any new names in the Stock in Stock out table to the 1st 2 tables when they are there I can run my update stock macro and job done.

But I only want to append the new stock names and this is where we started.

Modules OK - had a read up on Modules and have a brief understanding of what they do - but as yet I have not used them -so if I put your code into a module you have told me how to run it - but how do I run it automatically? is it similar to a macro?

Oh by the way are you in the UK - I am in Wisbech Cambs UK - sheers Dave
 
0
 
BillystyxCommented:
Yes, I am in the UK. Perhaps if you post the db or relevant part of it I could look at it to see what you mean. If you cannot post, you can send it to my email(please include the ee link though), but you must also send the db sample to anyone else who wishes to help too.

Billystyx
0
 
g3rcqAuthor Commented:
OK - last night I sent it to your email address as an attachement - Q did you get it - Q how do I send an ee link? -Dave

Yes I will also sent to others if I get offers of help Dave
0
 
g3rcqAuthor Commented:
OK - last night I sent it to your email address as an attachement - Q did you get it - Q how do I send an ee link? -Dave

Yes I will also sent to others if I get offers of help Dave
0
 
BillystyxCommented:
I have made a few changes here, but I'm not sure if it is what you are after. I have set up some relationships between thentables, I also added a form to illustrate what can be done - It has a save button (for adding new records only), the data is updated to the current stock table, and saved directly into the stock in and out table.
I could not see a reason for the lookup table on it s own, but if it is necessary it is easy enough to change.

By going into form1 and adding a new record and then clicking on the save record button, you will see that it saves the record to the stockin and out table, and to the current stock table.

You cannot add duplicate records to the stockinandout table, so you will not end up with duplicate data in either of the other either, because the records cascade updates and deletes from the stockinandout table.

Take a look at it and if it is not what you are looking for please explain what is wrong, or (at worst) what is right.

The code I placed on the save button was as follows:

On Error GoTo Err_Command7_Click


    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
mySQLstr = "insert into [Current Stock]([Stock Name],StockID) select [Stock In And Out].[Stock Name],[Stock In And Out].StockID from [Stock In And Out];"
CurrentDb.Execute mySQLstr
Exit_Command7_Click:
    Exit Sub

Err_Command7_Click:
    MsgBox Err.Description
    Resume Exit_Command7_Click

Billystyx
Hope we are getting somewhere:)
0
 
BillystyxCommented:
On further investigation, I think to do this you need t rearrange your db.

2 tables:
tblStockItems
StockItem - text
StockID - primaryKey and an autonumber
Supplier - text
Price - currency

tblCurrentStock
StockItem - text
Qty - Number
StockID - number

and 4 forms
Stock
frmNew
frmSold
frmCurrentStock

stock is for viewing stock you have, and adding new stock
it would also provide buttons to get to the other forms

here is the module for stock

Private Sub Command4_Click()
On Error GoTo Err_Command4_Click


    DoCmd.GoToRecord , , acNewRec'for new records

Exit_Command4_Click:
    Exit Sub

Err_Command4_Click:
    MsgBox Err.Description
    Resume Exit_Command4_Click
   
End Sub
Private Sub Command5_Click()
On Error GoTo Err_Command5_Click


    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70'for saving the new record
Combo6.SetFocus'this clears the combo box for searching
Combo6.Requery
Combo6.Text = ""
Exit_Command5_Click:
    Exit Sub

Err_Command5_Click:
    MsgBox Err.Description
    Resume Exit_Command5_Click
   
End Sub
Private Sub Combo6_AfterUpdate()'this find a record on your form based on the value you select in the combo box
    ' Find the record that matches the control.
    Dim rs As Object
On Error Resume Next
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[StockID] = " & str(Me![Combo6])
    Me.Bookmark = rs.Bookmark
End Sub

Private Sub Command8_Click()'this is for adding new stock
DoCmd.OpenForm "frmNew", , , "StockID=" & Me.StockID.Value
End Sub

Private Sub Command9_Click()'this is for removing stock that has been sold
DoCmd.OpenForm "frmSold", , , "StockID=" & Me.StockID.Value
End Sub

in the new stock form, this module

Private Sub Command2_Click()
On Error GoTo Err_Command2_Click


    DoCmd.Close

Exit_Command2_Click:
    Exit Sub

Err_Command2_Click:
    MsgBox Err.Description
    Resume Exit_Command2_Click
   
End Sub

Private Sub Form_Load()
StockID = Forms!frmStock1.StockID
StockItem = Forms!frmStock1.StockItem

End Sub



Private Sub Text4_LostFocus()
If Not IsNull(Qty.Value) Then
Qty.Value = Text4.Text + Qty.Value'this is the main part, which adds the new stock to the current stock for that stockid
Else
Qty.Value = Text4.Text
End If
End Sub

and in sotck sold

Private Sub Command2_Click()
On Error GoTo Err_Command2_Click


    DoCmd.Close

Exit_Command2_Click:
    Exit Sub

Err_Command2_Click:
    MsgBox Err.Description
    Resume Exit_Command2_Click
   
End Sub

Private Sub Form_Load()
StockID = Forms!frmStock1.StockID
StockItem = Forms!frmStock1.StockItem

End Sub

Private Sub Text4_LostFocus()
If Not IsNull(Qty.Value) And (Qty.Value - Text4.Text) > 0 Then'if this is not the first stock qty and subtracting the qty from the current qty doesn't give you less than you stared with then subtract the qty you add from the current stock
Qty.Value = Qty.Value - Text4.Text'sub
Else
MsgBox "You can't sell stock you don't have"
End If
End Sub

Each of these 2 forms has a recordsource of the tblCurrentStock table, and the first table has a recordsource of tblStockItems

This way, you should be able to keep track of incoming and outgoing stock from an easy to use form based db.

Does this make sense?
ps, I can upload this to my webspace if it will help, but I can't do it till this evening:)
Billystyx

0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 13
  • 12
Tackle projects and never again get stuck behind a technical roadblock.
Join Now