Have query append or make new record

  • Thread starter Thread starter Caleb
  • Start date Start date
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
 
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
 
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
 
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
 
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
 
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
 
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
 
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...
 
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...
 
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
 
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
 
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...
 
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
 
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.
 
Back
Top