Help Needed With Append Query?

[Guest]

Hi

I have created two tables so far in Access 2007 - raised stock (new stock)
and current stock.
I would like to enter new stock details in the raised stock table, then this
will be automatically added to the current stock table by pressing a button.
I have created an append query, where I have entered each field from the
current stock table (the table I want to update) and append to the matching
field in the raised stock table.
However, when I run the query it doesn't affect any tables.

Please can you help with what Im doing wrong?

Thanks for all your help,
Louisa Holt

 

[Guest]

Maybe, if you post the SQL of the append query.

 

[Guest]

This is the SQL view from my append query.

UPDATE tblRaisedStock INNER JOIN (tblPriceAlterations INNER JOIN (tblDockets
INNER JOIN (tblCreditsOut INNER JOIN (tblCreditsIn INNER JOIN tblCurrentStock
ON tblCreditsIn.ID = tblCurrentStock.ID) ON tblCreditsOut.ID =
tblCurrentStock.ID) ON tblDockets.ID = tblCurrentStock.ID) ON
tblPriceAlterations.ID = tblCurrentStock.ID) ON tblRaisedStock.ID =
tblCurrentStock.ID SET tblCurrentStock.[Date Entered] = [tblRaisedStock.[Date
Entered], tblCurrentStock.Make = [tblRaisedStock.[Make],
tblCurrentStock.[Number] = [tblRaisedStock.[Number], tblCurrentStock.Colour =
[tblRaisedStock.[Colour], tblCurrentStock.[Size] = [tblRaisedStock.[Size],
tblCurrentStock.Quantity = [tblRaisedStock.[Quantity], tblCurrentStock.[Cost
Price] = [tblRaisedStock.[Cost Price], tblCurrentStock.[Retail Price] =
[tblRaisedStock.[Retail Price], tblCurrentStock.[Discount Price] =
[tblRaisedStock.[Discount Price], tblCurrentStock.Shop =
[tblRaisedStock.[Shop], tblCurrentStock.Gender = [tblRaisedStock.[Gender],
tblCurrentStock.Type = [tblRaisedStock.[Type], tblCurrentStock.[Y/S] =
[tblRaisedStock.[Y/S], tblCurrentStock.Season = [tblRaisedStock.[Season];

 

[Guest]

What you posted is NOT an append query. An append query has INSERT INTO ans
SELECT. It adds records to a table.

Your query is an update query. In using an inner join both tables must
matching data.
--
KARL DEWEY
Build a little - Test a little

This is the SQL view from my append query.

UPDATE tblRaisedStock INNER JOIN (tblPriceAlterations INNER JOIN (tblDockets
INNER JOIN (tblCreditsOut INNER JOIN (tblCreditsIn INNER JOIN tblCurrentStock
ON tblCreditsIn.ID = tblCurrentStock.ID) ON tblCreditsOut.ID =
tblCurrentStock.ID) ON tblDockets.ID = tblCurrentStock.ID) ON
tblPriceAlterations.ID = tblCurrentStock.ID) ON tblRaisedStock.ID =
tblCurrentStock.ID SET tblCurrentStock.[Date Entered] = [tblRaisedStock.[Date
Entered], tblCurrentStock.Make = [tblRaisedStock.[Make],
tblCurrentStock.[Number] = [tblRaisedStock.[Number], tblCurrentStock.Colour =
[tblRaisedStock.[Colour], tblCurrentStock.[Size] = [tblRaisedStock.[Size],
tblCurrentStock.Quantity = [tblRaisedStock.[Quantity], tblCurrentStock.[Cost
Price] = [tblRaisedStock.[Cost Price], tblCurrentStock.[Retail Price] =
[tblRaisedStock.[Retail Price], tblCurrentStock.[Discount Price] =
[tblRaisedStock.[Discount Price], tblCurrentStock.Shop =
[tblRaisedStock.[Shop], tblCurrentStock.Gender = [tblRaisedStock.[Gender],
tblCurrentStock.Type = [tblRaisedStock.[Type], tblCurrentStock.[Y/S] =
[tblRaisedStock.[Y/S], tblCurrentStock.Season = [tblRaisedStock.[Season];

Maybe, if you post the SQL of the append query.

 

[Guest]

Sorry this is the append query but using different tables, as described before.

INSERT INTO tblCurrentStock ( [Date Entered], Make, [Number], Colour,
[Size], Quantity, Shop )
SELECT tblCurrentStock.[Date Entered], tblCurrentStock.Make,
tblCurrentStock.Number, tblCurrentStock.Colour, tblCurrentStock.Size,
tblCurrentStock.Quantity, tblCurrentStock.Shop
FROM tblDockets INNER JOIN tblCurrentStock ON tblDockets.ID =
tblCurrentStock.ID;

Thanks for helping, Louisa

What you posted is NOT an append query. An append query has INSERT INTO ans
SELECT. It adds records to a table.

Your query is an update query. In using an inner join both tables must
matching data.
--
KARL DEWEY
Build a little - Test a little

This is the SQL view from my append query.

UPDATE tblRaisedStock INNER JOIN (tblPriceAlterations INNER JOIN (tblDockets
INNER JOIN (tblCreditsOut INNER JOIN (tblCreditsIn INNER JOIN tblCurrentStock
ON tblCreditsIn.ID = tblCurrentStock.ID) ON tblCreditsOut.ID =
tblCurrentStock.ID) ON tblDockets.ID = tblCurrentStock.ID) ON
tblPriceAlterations.ID = tblCurrentStock.ID) ON tblRaisedStock.ID =
tblCurrentStock.ID SET tblCurrentStock.[Date Entered] = [tblRaisedStock.[Date
Entered], tblCurrentStock.Make = [tblRaisedStock.[Make],
tblCurrentStock.[Number] = [tblRaisedStock.[Number], tblCurrentStock.Colour =
[tblRaisedStock.[Colour], tblCurrentStock.[Size] = [tblRaisedStock.[Size],
tblCurrentStock.Quantity = [tblRaisedStock.[Quantity], tblCurrentStock.[Cost
Price] = [tblRaisedStock.[Cost Price], tblCurrentStock.[Retail Price] =
[tblRaisedStock.[Retail Price], tblCurrentStock.[Discount Price] =
[tblRaisedStock.[Discount Price], tblCurrentStock.Shop =
[tblRaisedStock.[Shop], tblCurrentStock.Gender = [tblRaisedStock.[Gender],
tblCurrentStock.Type = [tblRaisedStock.[Type], tblCurrentStock.[Y/S] =
[tblRaisedStock.[Y/S], tblCurrentStock.Season = [tblRaisedStock.[Season];

Maybe, if you post the SQL of the append query.
--
KARL DEWEY
Build a little - Test a little


:

Hi

I have created two tables so far in Access 2007 - raised stock (new stock)
and current stock.
I would like to enter new stock details in the raised stock table, then this
will be automatically added to the current stock table by pressing a button.
I have created an append query, where I have entered each field from the
current stock table (the table I want to update) and append to the matching
field in the raised stock table.
However, when I run the query it doesn't affect any tables.

Please can you help with what Im doing wrong?

Thanks for all your help,
Louisa Holt

 

[Guest]

The SQL statement that you posted will append records from tblCurrentStock TO
tblCurrentStock if tblDockets has a matching ID.

Why do you wish to duplicate your records?

If tblCurrentStock.ID is your primary key then no records will be appended.

 

[Guest]

Is this a better SQL statement:-

INSERT INTO tblCurrentStock
SELECT *
FROM tblDockets;

Will details from the dockets table go into the current stock table?
If I want this to happen will I have to change the primary key from ID to
another field, eg. make?

Thank you very much, Louisa

 

[Guest]

Will details from the dockets table go into the current stock table?
Not if the ID field as primary key already exist.

What is the purpose of the two tables? Why insert in one table then copy to
the other?