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

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

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

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

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

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
 
C

Clifford Bass

Hi Caleb,

I will have to take a look at this over the weekend. Meanwhile, a
couple of questions:

Are any of the three indexes on DailyOrders unique? If not, again,
what will uniquely identify one and only one row?

MailMerger does not have any indexes listed. From what you state about
ShipToName, that should be made into the primary. However, that does not
make sense if for any one ShipToName you could have multiple products (skus).
Or even multiple orders on the same day. As I look at it, I am thinking
that its primary key should be made up of the three fields: ShipToName,
OrderID and sku.

Oh, one other question; how do you distinguish between
people/organizations with the same name such as two John Smiths or different
Walmart stores?

Clifford Bass
 
C

Caleb

None of the fields in DailyOrders are unique which I guess might be a
problem. The only way I can think of to identify a unique record is to base
it on the ShipToName AND the SKU unless someone orders two of the same
product then even that wouldnt be unique..

Now the reason one ShipToName would have multiple records is because its one
order, for example say you bought five different products at once. In
dailyOrders youd have five records, all with the same OrderId, ShipToName,
ShipToAddress, etc... but different ProductsOrdered, SKU, price, etc...

Now I didn't know you could set up a primary key that includes more than one
field? Maybe im misreading what you ment for MailMergers primary key.

To answer your last question, all the orders come from one place but if two
John Smiths both made seperate orders then all the following fields would be
defferent: OrderId, Email, Phone, ProductsOrdered(maybe), SKU(maybe), same
goes for price qty total and whatever. Then ShipToAddress, City, STate and
Zip obviously.

Did I help to answer your questions?
Thank you so much again for trying to help me out!
Caleb
 
C

Clifford Bass

Hi Caleb,

Probably for DailyOrders, you could use the combination of OrderID and
SKU as a unique/primary key. That would cover someone ordering the same
product in more than one order on any one day because the second order would
have a different OrderID. So that would sort of match my suggestion for
MailMerger for a way to uniquely identify any one particular row. And yes,
you did read it correctly; indexes can be made up of more than one field. To
create a multi-field index, you go into the index dialog and give the index a
name. Then you choose the first field. To add the next field, in the next
row do not enter a name, but choose the next field. And so on. It might
look like this for MailMerger:

PrimaryKey OrderID
sku

Whenever you specify an index name, it starts a new index and uses all
fields that follow until you specify another index name, or there are no more
rows. To set whether or not the index is unique, you change the value in the
lower part of the dialog while clicked on the line that holds the name of the
index. Likewise you can also specify if it is the primary key.

So, if you set the combination of OrderID and SKU fields to be the
primary key in each of the tables, you probably can modify your queries as
follows:

For the update query:

UPDATE (MailMerger INNER JOIN DailyOrders ON (MailMerger.OrderID =
DailyOrders.OrderID) AND (MailMerger.SKU = DailyOrders.SKU)) INNER JOIN
[Product SKU] ON (DailyOrders.SKU = [Product SKU].sku) SET ....

For the append query:

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

As the only table that would contain the Club and ClubPrice is the
Product SKU table, and your query clearly pulls those values from there, but
you are not getting any values in MailMerger, you may want to check your
actual data in the Product SKU table to see if it really does contain any
values.

My final question had a purpose, in that it would make sense to store
in the DailyOrders table only some sort of a customer ID instead of all of
the customer information. And you would have a customers table that would
hold all of the customers' information. Something like:

tblCustomers
CustomerID
CustomerName
CustomerAddress1
etc.

Your DailyOrders then would only contain a CustomerID; not all of the
other stuff such as ShipToName, ShipToAddress1, etc. This would also provide
for an easy way to distinguish customers with the same names from each other;
they would have different ID numbers. This is the same concept as having a
separate Products table, each with a unique SKU. If you have control over
the data and the database, it would be worth your while to make that shift.
Also, the DailyOrders table should be split out into two tables. One that
holds information specific to the order, except for the items ordered and one
that holds the items ordered, with information specific to just those items.

DailyOrders
OrderID
OrderDate
CustomerID
etc.

DailyOrderItems
OrderID
SKU
Quantity
etc.

And, just to add to your possibilities and maybe simplify things; you
may not even need a mail merger table. Just use a select query as the source
of the mail merger data.

Things to think about for you over the weekend :)

Clifford Bass
 
C

Caleb

Hi Clifford,

The hardest part about builing this database is the DailyOrders table has to
be the way it is because we download it as a .csv everyday and go from there.
So ive had to work with that table as a starting point. The structure you
recommended looks really good but Id have to build queries to seperate the
original data into that structure, do you think that would be more work than
its worth?

Thanks alot for your help and recommendations I probly wont mess with it
more untill monday but on monday ill try what you said about the indexes and
primary keys.
Ill post what I've accomplished (or not) monday, thanks so much you've been
very helpful.
Caleb

Clifford Bass said:
Hi Caleb,

Probably for DailyOrders, you could use the combination of OrderID and
SKU as a unique/primary key. That would cover someone ordering the same
product in more than one order on any one day because the second order would
have a different OrderID. So that would sort of match my suggestion for
MailMerger for a way to uniquely identify any one particular row. And yes,
you did read it correctly; indexes can be made up of more than one field. To
create a multi-field index, you go into the index dialog and give the index a
name. Then you choose the first field. To add the next field, in the next
row do not enter a name, but choose the next field. And so on. It might
look like this for MailMerger:

PrimaryKey OrderID
sku

Whenever you specify an index name, it starts a new index and uses all
fields that follow until you specify another index name, or there are no more
rows. To set whether or not the index is unique, you change the value in the
lower part of the dialog while clicked on the line that holds the name of the
index. Likewise you can also specify if it is the primary key.

So, if you set the combination of OrderID and SKU fields to be the
primary key in each of the tables, you probably can modify your queries as
follows:

For the update query:

UPDATE (MailMerger INNER JOIN DailyOrders ON (MailMerger.OrderID =
DailyOrders.OrderID) AND (MailMerger.SKU = DailyOrders.SKU)) INNER JOIN
[Product SKU] ON (DailyOrders.SKU = [Product SKU].sku) SET ....

For the append query:

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

As the only table that would contain the Club and ClubPrice is the
Product SKU table, and your query clearly pulls those values from there, but
you are not getting any values in MailMerger, you may want to check your
actual data in the Product SKU table to see if it really does contain any
values.

My final question had a purpose, in that it would make sense to store
in the DailyOrders table only some sort of a customer ID instead of all of
the customer information. And you would have a customers table that would
hold all of the customers' information. Something like:

tblCustomers
CustomerID
CustomerName
CustomerAddress1
etc.

Your DailyOrders then would only contain a CustomerID; not all of the
other stuff such as ShipToName, ShipToAddress1, etc. This would also provide
for an easy way to distinguish customers with the same names from each other;
they would have different ID numbers. This is the same concept as having a
separate Products table, each with a unique SKU. If you have control over
the data and the database, it would be worth your while to make that shift.
Also, the DailyOrders table should be split out into two tables. One that
holds information specific to the order, except for the items ordered and one
that holds the items ordered, with information specific to just those items.

DailyOrders
OrderID
OrderDate
CustomerID
etc.

DailyOrderItems
OrderID
SKU
Quantity
etc.

And, just to add to your possibilities and maybe simplify things; you
may not even need a mail merger table. Just use a select query as the source
of the mail merger data.

Things to think about for you over the weekend :)

Clifford Bass

Caleb said:
None of the fields in DailyOrders are unique which I guess might be a
problem. The only way I can think of to identify a unique record is to base
it on the ShipToName AND the SKU unless someone orders two of the same
product then even that wouldnt be unique..

Now the reason one ShipToName would have multiple records is because its one
order, for example say you bought five different products at once. In
dailyOrders youd have five records, all with the same OrderId, ShipToName,
ShipToAddress, etc... but different ProductsOrdered, SKU, price, etc...

Now I didn't know you could set up a primary key that includes more than one
field? Maybe im misreading what you ment for MailMergers primary key.

To answer your last question, all the orders come from one place but if two
John Smiths both made seperate orders then all the following fields would be
defferent: OrderId, Email, Phone, ProductsOrdered(maybe), SKU(maybe), same
goes for price qty total and whatever. Then ShipToAddress, City, STate and
Zip obviously.

Did I help to answer your questions?
Thank you so much again for trying to help me out!
Caleb
Click to expand...
Click to expand...
 
C

Clifford Bass

Hi Caleb,

I am glad to hear that it is helpful.

I wondered if the data was coming to you in that way. I think that the
end goals would determine whether or not it is worth it do split it up. If
the only purpose is to send out e-mails and then delete the data, then I
probably would not go to the effort. Anything much more, including keeping
the data for other purposes and I would want to do it "properly". It would
pay off in the long run, and maybe even in the short run. Also, it may not
take a lot of effort to split the data up. An import into a work table, and
a link to the CSV file, and some append queries just might do the trick.

Good luck, and you are quite welcome!

Clifford Bass
 
C

Caleb

So this morning I tryed out this index/primary key thing and I can't make the
SKU and the OrderId an index/primary because there are duplicates, the
duplicates are orders where one person bought two of the same product in one
order. That gives them two identical records...
 
C

Clifford Bass

Hi Caleb,

Well, that is a pain! Is that common or rare? It seems that the
quantity should have been adjusted instead of having more than one row for
the same thing. Maybe you can consolidate the rows when that happens. When
the data comes in, how to you import it? The import process could be
modified to deal with that situation, maybe with the use of a summary query,
and then you should be able to make the indexes unique. There may be some
issues with the price that you would need to deal with if there are quantity
discounts.

Clifford Bass
 
C

Caleb

Yeah you'd think the qty would reflect the qty haha, I import it as a .csv
file. I don't think theres qty discounts but Ill have to look into it.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query to append a field to a record if another field matches 0
Query not returning the correct results 0
Query isn't returning all results 3
Return a field in a query to a new field based on a produck SKU 1
Append and make table query,prompting for yes or no from access 5
Open a new form based on current form and append new record query 1
Relationship or Append Query? 3

Top