Have query append or make new record

C

Caleb

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
 
C

Clifford Bass

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 said:
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
Click to expand...
 
C

Clifford Bass

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 said:
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
Click to expand...
 
C

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 said:
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 said:
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
Click to expand...
Click to expand...
 
C

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 said:
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 said:
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
Click to expand...
Click to expand...
 
C

Clifford Bass

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
 
C

Clifford Bass

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
 
C

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.
 
C

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.
 
C

Clifford Bass

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
 
C

Clifford Bass

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
 
C

Caleb

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 said:
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 said:
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.
Click to expand...
Click to expand...
 
C

Caleb

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 said:
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 said:
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.
Click to expand...
Click to expand...
 
C

Clifford Bass

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
 
C

Clifford Bass

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
 
C

Caleb

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));
 
C

Caleb

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));
 
C

Clifford Bass

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
 
C

Clifford Bass

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
 
C

Caleb

The final product Im looking for should have only 1 of each ShipToName so in
the end ShipToName will be unique... SKU obviously wont be.
Heres the definition for DailyOrders, Product SKU and MailMerger:

Purchased-Club - Work in Prog - Geller DB.mdb Friday, May 29, 2009
Table: DailyOrders Page: 1
Columns
Name Type Size
OrderId Long Integer 4
Email Text 255
FirstName Text 255
LastName Text 255
Phone Text 255
ApproveStatus Text 255
OrderDate Text 255
ProductsOrdered Text 255
SKU Text 255
price Currency 8
qty Text 255
Total Text 255
Discount Text 255
ShipToName Text 255
ShipToAddress1 Text 255
ShipToAddress2 Text 255
ShipToCity Text 255
ShipToState Text 255
ShipToZip Text 255
ShipToCountry Text 255
ShippingPrice Text 255
Comments Memo -
HowHeard Text 255
Referrerid Text 255
ReferrerName Text 255
Commission Text 255
GrandCommission Text 255
RefundStatus Text 255
Table Indexes
Name Number of Fields
OrderId 1
Fields:
OrderId Ascending
Referrerid 1
Fields:
Referrerid Ascending
SKU 1
Fields:
SKU Ascending

Purchased-Club - Work in Prog - GellerDB.mdb Friday, May 29, 2009
Table: MailMerger Page: 2
Columns
Name Type Size
OrderDate Text 255
ProductsOrdered Text 255
OrderId Long Integer 4
ShipToName Text 255
ShipToAddress1 Text 255
ShipToAddress2 Text 255
ShipToCity Text 255
ShipToState Text 255
ShipToZip Text 255
ShipToCountry Text 255
Email Text 255
Phone Text 255
HowHeard Text 255
sku Text 255
Purchased Text 255
NextPurchase Text 255
Club Text 255
Price Text 255

Purchased-Club - Work in Prog - Geller DB.mdb Friday, May 29, 2009
Table: Product SKU Page: 3
Columns
Name Type Size
id Text 255
sku Text 255
product Text 255
Ship Yes/No 1
price Text 255
shipping Text 255
weight Text 255
current inventory Text 255
recurring cycle Text 255
recurring start duration Text 255
recurring price Text 255
destination url Text 255
thank you url Text 255
clear cart url Text 255
autoresponder Text 255
shipping calculation Text 255
state tax Text 255
country tax Text 255
short description Text 255
long description Text 255
sale price Text 255
on sale Text 255
active Text 255
add to cart url Text 255
image Text 255
category Text 255
Purchased Text 255
NextPurchase Text 255
Club Text 255
ClubPrice Long Integer 4
Table Indexes
Name Number of Fields
id 1
Fields:
id Ascending
PrimaryKey 1
Fields:
sku Ascending
Ship 1
Fields:
Ship Ascending
sku 1
Fields:
sku Ascending
 
Top