Have query append or make new record

Hi I need to know what I need to do to make my query do something like this:

IIf([MailMerger]![ShipToName] = [MailMergerClub]![ShipToName], (append only
the fields [Club] and [ClubPrice] to current record), (append all fields to
new record)

However I cant figure the code for what I put in parenthesis. Both
MailMerger and MailMergerClub have the same fields. There is already alot
going on to get this far.

I start with an orders table filled with yesterdays orders. I need to pull
out certain records based on product ordered, orderids and approve status.
After that I need 4 new fields: Purchased, NextPurchase, Club and ClubPrice.
I do all but the Club and Club Price with a Make Table query (Purchased,
NextPurchase, Club and ClubPrice are all based on the records in my Product
SKU table, where not all records have an entry. I cant do the clubs at the
same time as the Purchased fields because the criteria (Like "*") messes
things up when placed in two seperate fields) Here is the SQL for my Make
Table query:

SELECT DailyOrders.OrderDate, DailyOrders.ProductsOrdered,
DailyOrders.OrderId, DailyOrders.ShipToName, DailyOrders.ShipToAddress1,
DailyOrders.ShipToAddress2, DailyOrders.ShipToCity, DailyOrders.ShipToState,
DailyOrders.ShipToZip, DailyOrders.ShipToCountry, DailyOrders.Email,
DailyOrders.Phone, DailyOrders.HowHeard, [Product SKU].Purchased, [Product
SKU].NextPurchase, [Product SKU].Club, [Product SKU].ClubPrice AS Price INTO
MailMerger
FROM [Product SKU], DailyOrders
WHERE (((DailyOrders.ProductsOrdered)"Fico Fixer Book - $97 Value - Free"
And (DailyOrders.ProductsOrdered)"Fico Fixer Book - $97 Value - FREE To The
First 70 Subscribers Only") AND ((DailyOrders.OrderId)1000000000) AND
((DailyOrders.ShipToName) Not Like "*Richard*Geller*") AND (([Product
SKU].Purchased) Like "*") AND ((DailyOrders.SKU)=[Product SKU]![sku]) AND
((DailyOrders.ApproveStatus) Like "*Accepted*"))
ORDER BY DailyOrders.ShipToName;

After that I run an Append query to append the Club fields. The only
difference between the Make Table Query and the Append Query in the criteria
(Like "*") is in the Club field. Right now my Append query simply appends all
the records into the table, causing duplicates. Thats where I want the query
to check ShipToName and if its the same, to simple Append to that record
instead of makeing a new record. If it helps here is the SQL for my Append
query (The only differences are that its an Append query and the Like "*" is
in the Club field):

INSERT INTO MailMerger ( OrderDate, ProductsOrdered, OrderId, ShipToName,
ShipToAddress1, ShipToAddress2, ShipToCity, ShipToState, ShipToZip,
ShipToCountry, Email, Phone, HowHeard, Purchased, NextPurchase, Club, Price )
SELECT DailyOrders.OrderDate, DailyOrders.ProductsOrdered,
DailyOrders.OrderId, DailyOrders.ShipToName, DailyOrders.ShipToAddress1,
DailyOrders.ShipToAddress2, DailyOrders.ShipToCity, DailyOrders.ShipToState,
DailyOrders.ShipToZip, DailyOrders.ShipToCountry, DailyOrders.Email,
DailyOrders.Phone, DailyOrders.HowHeard, [Product SKU].Purchased, [Product
SKU].NextPurchase, [Product SKU].Club, [Product SKU].ClubPrice AS Price
FROM [Product SKU], DailyOrders
WHERE (((DailyOrders.ProductsOrdered)"Fico Fixer Book - $97 Value - Free"
And (DailyOrders.ProductsOrdered)"Fico Fixer Book - $97 Value - FREE To The
First 70 Subscribers Only") AND ((DailyOrders.OrderId)1000000000) AND
((DailyOrders.ShipToName) Not Like "*Richard*Geller*") AND (([Product
SKU].Club) Like "*") AND ((DailyOrders.SKU)=[Product SKU]![sku]) AND
((DailyOrders.ApproveStatus) Like "*Accepted*"))
ORDER BY DailyOrders.ShipToName;

I know this is a little confusing but I tried to make it clear what I need
help with, if there are any questions Id be glad to help fill you in better.
Thanks in advance,
Caleb

Hi Caleb,

You are headed in the right direction. What you need to do is run an
update query first and then an append query. The append query will use a
subquery to avoid adding rows that already exist. Here is a simple example:

update tblToBeUpdated inner join tblSource on tblSource.LinkingField =
tblToBeUpdated.LinkingField
set tblToBeUpdated.Field1 = tblSource.Field2, tblToBeUpdate.Field3 =
tblSource.Field4;

insert into tblToBeUpdated as A (LinkingField, Field1, Field3)
select LinkingField, Field2, Field3
from tblSource as B
where not exists (select * from tblToBeUpdated as C where C.LinkingField =
B.LinkingField);

The table aliases (A, B, C) may not be needed, but I included them for
clarity.

Hope that helps,

Clifford Bass

"Caleb" wrote:

Hi I need to know what I need to do to make my query do something like this:

IIf([MailMerger]![ShipToName] = [MailMergerClub]![ShipToName], (append only
the fields [Club] and [ClubPrice] to current record), (append all fields to
new record)

However I cant figure the code for what I put in parenthesis. Both
MailMerger and MailMergerClub have the same fields. There is already alot
going on to get this far.

I start with an orders table filled with yesterdays orders. I need to pull
out certain records based on product ordered, orderids and approve status.
After that I need 4 new fields: Purchased, NextPurchase, Club and ClubPrice.
I do all but the Club and Club Price with a Make Table query (Purchased,
NextPurchase, Club and ClubPrice are all based on the records in my Product
SKU table, where not all records have an entry. I cant do the clubs at the
same time as the Purchased fields because the criteria (Like "*") messes
things up when placed in two seperate fields) Here is the SQL for my Make
Table query:

SELECT DailyOrders.OrderDate, DailyOrders.ProductsOrdered,
DailyOrders.OrderId, DailyOrders.ShipToName, DailyOrders.ShipToAddress1,
DailyOrders.ShipToAddress2, DailyOrders.ShipToCity, DailyOrders.ShipToState,
DailyOrders.ShipToZip, DailyOrders.ShipToCountry, DailyOrders.Email,
DailyOrders.Phone, DailyOrders.HowHeard, [Product SKU].Purchased, [Product
SKU].NextPurchase, [Product SKU].Club, [Product SKU].ClubPrice AS Price INTO
MailMerger
FROM [Product SKU], DailyOrders
WHERE (((DailyOrders.ProductsOrdered)"Fico Fixer Book - $97 Value - Free"
And (DailyOrders.ProductsOrdered)"Fico Fixer Book - $97 Value - FREE To The
First 70 Subscribers Only") AND ((DailyOrders.OrderId)1000000000) AND
((DailyOrders.ShipToName) Not Like "*Richard*Geller*") AND (([Product
SKU].Purchased) Like "*") AND ((DailyOrders.SKU)=[Product SKU]![sku]) AND
((DailyOrders.ApproveStatus) Like "*Accepted*"))
ORDER BY DailyOrders.ShipToName;

After that I run an Append query to append the Club fields. The only
difference between the Make Table Query and the Append Query in the criteria
(Like "*") is in the Club field. Right now my Append query simply appends all
the records into the table, causing duplicates. Thats where I want the query
to check ShipToName and if its the same, to simple Append to that record
instead of makeing a new record. If it helps here is the SQL for my Append
query (The only differences are that its an Append query and the Like "*" is
in the Club field):

INSERT INTO MailMerger ( OrderDate, ProductsOrdered, OrderId, ShipToName,
ShipToAddress1, ShipToAddress2, ShipToCity, ShipToState, ShipToZip,
ShipToCountry, Email, Phone, HowHeard, Purchased, NextPurchase, Club, Price )
SELECT DailyOrders.OrderDate, DailyOrders.ProductsOrdered,
DailyOrders.OrderId, DailyOrders.ShipToName, DailyOrders.ShipToAddress1,
DailyOrders.ShipToAddress2, DailyOrders.ShipToCity, DailyOrders.ShipToState,
DailyOrders.ShipToZip, DailyOrders.ShipToCountry, DailyOrders.Email,
DailyOrders.Phone, DailyOrders.HowHeard, [Product SKU].Purchased, [Product
SKU].NextPurchase, [Product SKU].Club, [Product SKU].ClubPrice AS Price
FROM [Product SKU], DailyOrders
WHERE (((DailyOrders.ProductsOrdered)"Fico Fixer Book - $97 Value - Free"
And (DailyOrders.ProductsOrdered)"Fico Fixer Book - $97 Value - FREE To The
First 70 Subscribers Only") AND ((DailyOrders.OrderId)1000000000) AND
((DailyOrders.ShipToName) Not Like "*Richard*Geller*") AND (([Product
SKU].Club) Like "*") AND ((DailyOrders.SKU)=[Product SKU]![sku]) AND
((DailyOrders.ApproveStatus) Like "*Accepted*"))
ORDER BY DailyOrders.ShipToName;

I know this is a little confusing but I tried to make it clear what I need
help with, if there are any questions Id be glad to help fill you in better.
Thanks in advance,
Caleb

Thank you so much for replying, Ive posted this a couple times here waiting
for someone to try and help. Im still trying to figure out if what you
recommended will work for me but I just wanted to say thank you. Ill post
again letting you know if I got it. Thanks again
caleb

"Clifford Bass" wrote:

Hi Caleb,

You are headed in the right direction. What you need to do is run an
update query first and then an append query. The append query will use a
subquery to avoid adding rows that already exist. Here is a simple example:

update tblToBeUpdated inner join tblSource on tblSource.LinkingField =
tblToBeUpdated.LinkingField
set tblToBeUpdated.Field1 = tblSource.Field2, tblToBeUpdate.Field3 =
tblSource.Field4;

insert into tblToBeUpdated as A (LinkingField, Field1, Field3)
select LinkingField, Field2, Field3
from tblSource as B
where not exists (select * from tblToBeUpdated as C where C.LinkingField =
B.LinkingField);

The table aliases (A, B, C) may not be needed, but I included them for
clarity.

Hope that helps,

Clifford Bass

"Caleb" wrote:

Hi I need to know what I need to do to make my query do something like this:

IIf([MailMerger]![ShipToName] = [MailMergerClub]![ShipToName], (append only
the fields [Club] and [ClubPrice] to current record), (append all fields to
new record)

However I cant figure the code for what I put in parenthesis. Both
MailMerger and MailMergerClub have the same fields. There is already alot
going on to get this far.

I start with an orders table filled with yesterdays orders. I need to pull
out certain records based on product ordered, orderids and approve status.
After that I need 4 new fields: Purchased, NextPurchase, Club and ClubPrice.
I do all but the Club and Club Price with a Make Table query (Purchased,
NextPurchase, Club and ClubPrice are all based on the records in my Product
SKU table, where not all records have an entry. I cant do the clubs at the
same time as the Purchased fields because the criteria (Like "*") messes
things up when placed in two seperate fields) Here is the SQL for my Make
Table query:

SELECT DailyOrders.OrderDate, DailyOrders.ProductsOrdered,
DailyOrders.OrderId, DailyOrders.ShipToName, DailyOrders.ShipToAddress1,
DailyOrders.ShipToAddress2, DailyOrders.ShipToCity, DailyOrders.ShipToState,
DailyOrders.ShipToZip, DailyOrders.ShipToCountry, DailyOrders.Email,
DailyOrders.Phone, DailyOrders.HowHeard, [Product SKU].Purchased, [Product
SKU].NextPurchase, [Product SKU].Club, [Product SKU].ClubPrice AS Price INTO
MailMerger
FROM [Product SKU], DailyOrders
WHERE (((DailyOrders.ProductsOrdered)"Fico Fixer Book - $97 Value - Free"
And (DailyOrders.ProductsOrdered)"Fico Fixer Book - $97 Value - FREE To The
First 70 Subscribers Only") AND ((DailyOrders.OrderId)1000000000) AND
((DailyOrders.ShipToName) Not Like "*Richard*Geller*") AND (([Product
SKU].Purchased) Like "*") AND ((DailyOrders.SKU)=[Product SKU]![sku]) AND
((DailyOrders.ApproveStatus) Like "*Accepted*"))
ORDER BY DailyOrders.ShipToName;

After that I run an Append query to append the Club fields. The only
difference between the Make Table Query and the Append Query in the criteria
(Like "*") is in the Club field. Right now my Append query simply appends all
the records into the table, causing duplicates. Thats where I want the query
to check ShipToName and if its the same, to simple Append to that record
instead of makeing a new record. If it helps here is the SQL for my Append
query (The only differences are that its an Append query and the Like "*" is
in the Club field):

INSERT INTO MailMerger ( OrderDate, ProductsOrdered, OrderId, ShipToName,
ShipToAddress1, ShipToAddress2, ShipToCity, ShipToState, ShipToZip,
ShipToCountry, Email, Phone, HowHeard, Purchased, NextPurchase, Club, Price )
SELECT DailyOrders.OrderDate, DailyOrders.ProductsOrdered,
DailyOrders.OrderId, DailyOrders.ShipToName, DailyOrders.ShipToAddress1,
DailyOrders.ShipToAddress2, DailyOrders.ShipToCity, DailyOrders.ShipToState,
DailyOrders.ShipToZip, DailyOrders.ShipToCountry, DailyOrders.Email,
DailyOrders.Phone, DailyOrders.HowHeard, [Product SKU].Purchased, [Product
SKU].NextPurchase, [Product SKU].Club, [Product SKU].ClubPrice AS Price
FROM [Product SKU], DailyOrders
WHERE (((DailyOrders.ProductsOrdered)"Fico Fixer Book - $97 Value - Free"
And (DailyOrders.ProductsOrdered)"Fico Fixer Book - $97 Value - FREE To The
First 70 Subscribers Only") AND ((DailyOrders.OrderId)1000000000) AND
((DailyOrders.ShipToName) Not Like "*Richard*Geller*") AND (([Product
SKU].Club) Like "*") AND ((DailyOrders.SKU)=[Product SKU]![sku]) AND
((DailyOrders.ApproveStatus) Like "*Accepted*"))
ORDER BY DailyOrders.ShipToName;

I know this is a little confusing but I tried to make it clear what I need
help with, if there are any questions Id be glad to help fill you in better.
Thanks in advance,
Caleb

Hi Caleb,

Glad to help, and you are welcome! If you run into trouble, post
whatever you come up with and then I or someone else can take a closer look.

Clifford Bass

"Caleb" wrote:

Thank you so much for replying, Ive posted this a couple times here waiting
for someone to try and help. Im still trying to figure out if what you
recommended will work for me but I just wanted to say thank you. Ill post
again letting you know if I got it. Thanks again
caleb

So heres where Im at now, I made the two queries you recommended heres the
SQL for the Update Query:

UPDATE (MailMerger INNER JOIN DailyOrders ON (MailMerger.ShipToName =
DailyOrders.ShipToName) AND (MailMerger.SKU = DailyOrders.SKU)) INNER JOIN
[Product SKU] ON (DailyOrders.SKU = [Product SKU].sku) AND (MailMerger.SKU =
[Product SKU].sku) SET MailMerger.Purchased = [Product SKU].[Purchased],
MailMerger.NextPurchase = [Product SKU].[NextPurchase], MailMerger.Club =
[Product SKU].[Club], MailMerger.Price = [Product SKU].[ClubPrice];

and for the Append Query:

INSERT INTO MailMerger ( OrderDate, ProductsOrdered, OrderId, ShipToName,
ShipToAddress1, ShipToAddress2, ShipToCity, ShipToState, ShipToZip,
ShipToCountry, Email, Phone, HowHeard, Purchased, NextPurchase, Club, Price )
SELECT DailyOrders.OrderDate, DailyOrders.ProductsOrdered,
DailyOrders.OrderId, DailyOrders.ShipToName, DailyOrders.ShipToAddress1,
DailyOrders.ShipToAddress2, DailyOrders.ShipToCity, DailyOrders.ShipToState,
DailyOrders.ShipToZip, DailyOrders.ShipToCountry, DailyOrders.Email,
DailyOrders.Phone, DailyOrders.HowHeard, [Product SKU].Purchased, [Product
SKU].NextPurchase, [Product SKU].Club, [Product SKU].ClubPrice AS Price
FROM ([Product SKU] INNER JOIN DailyOrders ON [Product SKU].sku =
DailyOrders.SKU) INNER JOIN MailMerger ON (MailMerger.SKU = DailyOrders.SKU)
AND ([Product SKU].sku = MailMerger.SKU)
WHERE (((Exists (select * from MailMerger where MailMerger.ShipToName =
DailyOrders.ShipToName))=False));

The update query is working for the Purchased and NextPurchased fields, but
not for the Club and ClubPrice fields for some reason, all those fields come
from the same seperate table so I dont see why Purchased would work but nor
Club.

As for the Append Query, it confuses me Im not sure what its supposed to do
but it just appends new records and they still don't include Club and
ClubPrice... But the Purchased and NextPurchase work, oh and it appends
duplicates instead of adding to current records.

"Clifford Bass" wrote:

Hi Caleb,

Glad to help, and you are welcome! If you run into trouble, post
whatever you come up with and then I or someone else can take a closer look.

Clifford Bass

"Caleb" wrote:

Thank you so much for replying, Ive posted this a couple times here waiting
for someone to try and help. Im still trying to figure out if what you
recommended will work for me but I just wanted to say thank you. Ill post
again letting you know if I got it. Thanks again
caleb

Hi Caleb,

Working on copies of each query, change them back into just select
queries. What values do you see in the Club and ClubPrice columns? The
correct values or nulls? Also, are you getting multiple rows for the same
ShipToName? Or only one row?

Is ShipToName the primary key of the MailMerger table? If not, what is
the primary key?

Clifford Bass

"Caleb" wrote:

So heres where Im at now, I made the two queries you recommended heres the
SQL for the Update Query:

UPDATE (MailMerger INNER JOIN DailyOrders ON (MailMerger.ShipToName =
DailyOrders.ShipToName) AND (MailMerger.SKU = DailyOrders.SKU)) INNER JOIN
[Product SKU] ON (DailyOrders.SKU = [Product SKU].sku) AND (MailMerger.SKU =
[Product SKU].sku) SET MailMerger.Purchased = [Product SKU].[Purchased],
MailMerger.NextPurchase = [Product SKU].[NextPurchase], MailMerger.Club =
[Product SKU].[Club], MailMerger.Price = [Product SKU].[ClubPrice];

and for the Append Query:

INSERT INTO MailMerger ( OrderDate, ProductsOrdered, OrderId, ShipToName,
ShipToAddress1, ShipToAddress2, ShipToCity, ShipToState, ShipToZip,
ShipToCountry, Email, Phone, HowHeard, Purchased, NextPurchase, Club, Price )
SELECT DailyOrders.OrderDate, DailyOrders.ProductsOrdered,
DailyOrders.OrderId, DailyOrders.ShipToName, DailyOrders.ShipToAddress1,
DailyOrders.ShipToAddress2, DailyOrders.ShipToCity, DailyOrders.ShipToState,
DailyOrders.ShipToZip, DailyOrders.ShipToCountry, DailyOrders.Email,
DailyOrders.Phone, DailyOrders.HowHeard, [Product SKU].Purchased, [Product
SKU].NextPurchase, [Product SKU].Club, [Product SKU].ClubPrice AS Price
FROM ([Product SKU] INNER JOIN DailyOrders ON [Product SKU].sku =
DailyOrders.SKU) INNER JOIN MailMerger ON (MailMerger.SKU = DailyOrders.SKU)
AND ([Product SKU].sku = MailMerger.SKU)
WHERE (((Exists (select * from MailMerger where MailMerger.ShipToName =
DailyOrders.ShipToName))=False));

The update query is working for the Purchased and NextPurchased fields, but
not for the Club and ClubPrice fields for some reason, all those fields come
from the same seperate table so I dont see why Purchased would work but nor
Club.

As for the Append Query, it confuses me Im not sure what its supposed to do
but it just appends new records and they still don't include Club and
ClubPrice... But the Purchased and NextPurchase work, oh and it appends
duplicates instead of adding to current records.

After changing the Update query to Select, I got no values back for the Club
and ClubPrice columns. The only record I got two rows for was one that had
two seperate Purchased and NextPurchase entries so thats okay. As for the
Append query, when I changed it to select, it only found two records, both
the exact same. No entries in Club and ClubPrice, the same two records it
appends.

ShipToName is not the primary key for MailMerger because its not unique.
DailyOrders, (the table source) is a list of products ordered, so if one
person (ShipToName) orders multiple things, a new record is made for each
product.

"Clifford Bass" wrote:

Hi Caleb,

Working on copies of each query, change them back into just select
queries. What values do you see in the Club and ClubPrice columns? The
correct values or nulls? Also, are you getting multiple rows for the same
ShipToName? Or only one row?

Is ShipToName the primary key of the MailMerger table? If not, what is
the primary key?

Clifford Bass

"Caleb" wrote:

So heres where Im at now, I made the two queries you recommended heres the
SQL for the Update Query:

UPDATE (MailMerger INNER JOIN DailyOrders ON (MailMerger.ShipToName =
DailyOrders.ShipToName) AND (MailMerger.SKU = DailyOrders.SKU)) INNER JOIN
[Product SKU] ON (DailyOrders.SKU = [Product SKU].sku) AND (MailMerger.SKU =
[Product SKU].sku) SET MailMerger.Purchased = [Product SKU].[Purchased],
MailMerger.NextPurchase = [Product SKU].[NextPurchase], MailMerger.Club =
[Product SKU].[Club], MailMerger.Price = [Product SKU].[ClubPrice];

and for the Append Query:

INSERT INTO MailMerger ( OrderDate, ProductsOrdered, OrderId, ShipToName,
ShipToAddress1, ShipToAddress2, ShipToCity, ShipToState, ShipToZip,
ShipToCountry, Email, Phone, HowHeard, Purchased, NextPurchase, Club, Price )
SELECT DailyOrders.OrderDate, DailyOrders.ProductsOrdered,
DailyOrders.OrderId, DailyOrders.ShipToName, DailyOrders.ShipToAddress1,
DailyOrders.ShipToAddress2, DailyOrders.ShipToCity, DailyOrders.ShipToState,
DailyOrders.ShipToZip, DailyOrders.ShipToCountry, DailyOrders.Email,
DailyOrders.Phone, DailyOrders.HowHeard, [Product SKU].Purchased, [Product
SKU].NextPurchase, [Product SKU].Club, [Product SKU].ClubPrice AS Price
FROM ([Product SKU] INNER JOIN DailyOrders ON [Product SKU].sku =
DailyOrders.SKU) INNER JOIN MailMerger ON (MailMerger.SKU = DailyOrders.SKU)
AND ([Product SKU].sku = MailMerger.SKU)
WHERE (((Exists (select * from MailMerger where MailMerger.ShipToName =
DailyOrders.ShipToName))=False));

The update query is working for the Purchased and NextPurchased fields, but
not for the Club and ClubPrice fields for some reason, all those fields come
from the same seperate table so I dont see why Purchased would work but nor
Club.

As for the Append Query, it confuses me Im not sure what its supposed to do
but it just appends new records and they still don't include Club and
ClubPrice... But the Purchased and NextPurchase work, oh and it appends
duplicates instead of adding to current records.

Hi Caleb,

So for the update query, that you do not get values in Club and
ClubPrice in the select version is the reason they do not update. You will
need to figure out how to get the correct Club and ClubPrice values to show
in the select version of the query; then either turn it into the update query
or change the update query appropriately.

For the append query, that also will need to be adjusted in a similar
fashion so that it provides the Club and ClubPrice values. In looking closer
at the update query I see, belatedly, that you are joining on both the
ShipToName and the SKU. Is this what makes the rows in MailMerger unique?
If so, you will need to add that condition also into the exists clause.

WHERE (((Exists (select * from MailMerger where MailMerger.ShipToName =
DailyOrders.ShipToName and MailMerger.SKU = DailyOrders.SKU))=False));

Hopefully that should eliminate the duplicate issue. As I look closer
to the append query I see you are doing a join to MailMerger. Remove that
table entirely from the main select; it is not needed. It should only show
in the subquery. I am thinking that is the reason for not getting new
records. By doing that join you were telling it to only show records already
in MailMerger and then the subquery was telling it to show only records that
sort-of were not in MailMerger. Oh, the append needs to append the SKU also.

Hopefully that should do the trick,

Clifford Bass

"Caleb" wrote:

After changing the Update query to Select, I got no values back for the Club
and ClubPrice columns. The only record I got two rows for was one that had
two seperate Purchased and NextPurchase entries so thats okay. As for the
Append query, when I changed it to select, it only found two records, both
the exact same. No entries in Club and ClubPrice, the same two records it
appends.

ShipToName is not the primary key for MailMerger because its not unique.
DailyOrders, (the table source) is a list of products ordered, so if one
person (ShipToName) orders multiple things, a new record is made for each
product.

So the update query still only returns Purchased and NextPurchase and when it
updates it say its updating more records then there are in the table? The
append query appends a ton of records and also doesn't put the values in the
same records. But the Club and Price fields are correct...

What do you mean by unique? because I dont think joining the ShipToName and
SKU fields in the Update query will make the orders unique considering the
DailyOrders table has all the same and more records as the MailMerger and
both of those fields can have the same values in multiple records.

Also I added this the the Append query:

WHERE (((Exists (select * from MailMerger where MailMerger.ShipToName =
DailyOrders.ShipToName and MailMerger.SKU = DailyOrders.SKU))=False));

"Clifford Bass" wrote:

Hi Caleb,

So for the update query, that you do not get values in Club and
ClubPrice in the select version is the reason they do not update. You will
need to figure out how to get the correct Club and ClubPrice values to show
in the select version of the query; then either turn it into the update query
or change the update query appropriately.

For the append query, that also will need to be adjusted in a similar
fashion so that it provides the Club and ClubPrice values. In looking closer
at the update query I see, belatedly, that you are joining on both the
ShipToName and the SKU. Is this what makes the rows in MailMerger unique?
If so, you will need to add that condition also into the exists clause.

WHERE (((Exists (select * from MailMerger where MailMerger.ShipToName =
DailyOrders.ShipToName and MailMerger.SKU = DailyOrders.SKU))=False));

Hopefully that should eliminate the duplicate issue. As I look closer
to the append query I see you are doing a join to MailMerger. Remove that
table entirely from the main select; it is not needed. It should only show
in the subquery. I am thinking that is the reason for not getting new
records. By doing that join you were telling it to only show records already
in MailMerger and then the subquery was telling it to show only records that
sort-of were not in MailMerger. Oh, the append needs to append the SKU also.

Hopefully that should do the trick,

Clifford Bass

"Caleb" wrote:

After changing the Update query to Select, I got no values back for the Club
and ClubPrice columns. The only record I got two rows for was one that had
two seperate Purchased and NextPurchase entries so thats okay. As for the
Append query, when I changed it to select, it only found two records, both
the exact same. No entries in Club and ClubPrice, the same two records it
appends.

ShipToName is not the primary key for MailMerger because its not unique.
DailyOrders, (the table source) is a list of products ordered, so if one
person (ShipToName) orders multiple things, a new record is made for each
product.

Hi Caleb,

Unique, as in what will identify exactly and only one row of data. So
if ShipToName and SKU do not identify exactly and only one row in your
MailMerger, what field(s) do(es)? Until you can identify those field(s) you
will not be able to accomplish your goal. That is why you got more updates
than there were rows in the table. Because the rows were not uniquely
identified, some or all of the rows were updated multiple times. An append
query will never update existing rows, only add new ones. The opposite of
that is that an update query will only update existing rows, never add new
ones. This is why you need to do both. I think the unique-row issue will
also solve the append query. You will need to change the where clause of the
append query again, once you identify the appropriate field(s).

If you are not able to identify them, post the definitions, including
primary keys, of the tables involved in the process.

Clifford Bass

"Caleb" wrote:

So the update query still only returns Purchased and NextPurchase and when it
updates it say its updating more records then there are in the table? The
append query appends a ton of records and also doesn't put the values in the
same records. But the Club and Price fields are correct...

What do you mean by unique? because I dont think joining the ShipToName and
SKU fields in the Update query will make the orders unique considering the
DailyOrders table has all the same and more records as the MailMerger and
both of those fields can have the same values in multiple records.

Also I added this the the Append query:

WHERE (((Exists (select * from MailMerger where MailMerger.ShipToName =
DailyOrders.ShipToName and MailMerger.SKU = DailyOrders.SKU))=False));