|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
|
Thread Tools | Display Modes |
|
#11
May 29th, 2009, 07:42 PM
posted to microsoft.public.access.reports
|
|||
|
|||
Have query append or make new record
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 "Clifford Bass" wrote: Hi Caleb, Unique, as in what will identify exactly and only one row of data. So if ShipToName and SKU do not identify exactly and only one row in your MailMerger, what field(s) do(es)? Until you can identify those field(s) you will not be able to accomplish your goal. That is why you got more updates than there were rows in the table. Because the rows were not uniquely identified, some or all of the rows were updated multiple times. An append query will never update existing rows, only add new ones. The opposite of that is that an update query will only update existing rows, never add new ones. This is why you need to do both. I think the unique-row issue will also solve the append query. You will need to change the where clause of the append query again, once you identify the appropriate field(s). If you are not able to identify them, post the definitions, including primary keys, of the tables involved in the process. Clifford Bass "Caleb" wrote: So the update query still only returns Purchased and NextPurchase and when it updates it say its updating more records then there are in the table? The append query appends a ton of records and also doesn't put the values in the same records. But the Club and Price fields are correct... What do you mean by unique? because I dont think joining the ShipToName and SKU fields in the Update query will make the orders unique considering the DailyOrders table has all the same and more records as the MailMerger and both of those fields can have the same values in multiple records. Also I added this the the Append query: WHERE (((Exists (select * from MailMerger where MailMerger.ShipToName = DailyOrders.ShipToName and MailMerger.SKU = DailyOrders.SKU))=False)); 
|
|
#12
May 29th, 2009, 08:13 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Have query append or make new record
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 "Caleb" wrote: 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
|
|
#13
May 29th, 2009, 08:34 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Have query append or make new record
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 "Clifford Bass" wrote: 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 "Caleb" wrote: 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
|
|
#14
May 29th, 2009, 09:41 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Have query append or make new record
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" wrote: 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 "Clifford Bass" wrote: 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 "Caleb" wrote: 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
|
|
#15
May 29th, 2009, 09:54 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Have query append or make new record
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" wrote: 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" wrote: 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 "Clifford Bass" wrote: 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 "Caleb" wrote: 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
|
|
#16
May 30th, 2009, 12:07 AM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Have query append or make new record
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 "Caleb" wrote: 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
|
|
#17
June 1st, 2009, 03:40 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Have query append or make new record
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... "Clifford Bass" wrote: 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 "Caleb" wrote: 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
|
|
#18
June 1st, 2009, 05:16 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Have query append or make new record
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 "Caleb" wrote: 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...
|
|
#19
June 1st, 2009, 05:26 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Have query append or make new record
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. "Clifford Bass" wrote: 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 "Caleb" wrote: 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...
|
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
