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 |
#1
|
|||
|
|||
renaming tables for each customer, used in one report
I have a report that relies on a query of a table of customer specific data
(imported fr Excel) joined with a couple of other tables whose content is fixed. I will be generating this report for several different customers each day and so will have to import several different sets of excel files daily. WHen I built the db, I called the customer table "table1" and so all the code references are to that. So today I got 3 new spreadhseets of customer data ready for importing to Access. I am bringing these in as "table1CustA", "table1CustB" and "table1custC". The original table1 I have contains bogus data that I used just for db design purposes. So my plan was to simply delete table1, then rename table1CustA to table1 and run the report for CustA. When this is done I would rename table1 back to table1CustA, then rename table1CustB to table1 and run CustB's report, etc. Can I just do this w/o worrying about the order of how I rename the tables and all will come out OK? ALso, I need to keep all the customer tables in the db because any customer might come back later with an additional request or need to fix something and I don't really want to go through reimporting that data from excel. I have noticed that when I change the name of a table that a query uses, it seems to propagate through the query, so I'm concerned that when I change the name back from table1 to table1CustA, that might screw things up for the next customer. Thanks. -- Alan |
#2
|
|||
|
|||
renaming tables for each customer, used in one report
Alan
Having a table named for each Customer is precisely how you'd have to handle your situation ... if you were limited to using a spreadsheet! Copying/importing your "flat" Excel data "as is" directly into the relational model provided by Access pretty much guarantees that you'll "pain yourself into a corner". You won't get the easy use of Access' relationally-oriented features/functions if you insist on feeding it 'sheet data. Before you go any further, I urge you to turn off your computer and sit down with paper and pencil to map out the data structures and relationships. If "normalization" and "relational" are not terms you're familiar with, spend time learning about them before resuming your use of Access. Regards Jeff Boyce Microsoft Office/Access MVP "adgorn" wrote in message ... I have a report that relies on a query of a table of customer specific data (imported fr Excel) joined with a couple of other tables whose content is fixed. I will be generating this report for several different customers each day and so will have to import several different sets of excel files daily. WHen I built the db, I called the customer table "table1" and so all the code references are to that. So today I got 3 new spreadhseets of customer data ready for importing to Access. I am bringing these in as "table1CustA", "table1CustB" and "table1custC". The original table1 I have contains bogus data that I used just for db design purposes. So my plan was to simply delete table1, then rename table1CustA to table1 and run the report for CustA. When this is done I would rename table1 back to table1CustA, then rename table1CustB to table1 and run CustB's report, etc. Can I just do this w/o worrying about the order of how I rename the tables and all will come out OK? ALso, I need to keep all the customer tables in the db because any customer might come back later with an additional request or need to fix something and I don't really want to go through reimporting that data from excel. I have noticed that when I change the name of a table that a query uses, it seems to propagate through the query, so I'm concerned that when I change the name back from table1 to table1CustA, that might screw things up for the next customer. Thanks. -- Alan |
#3
|
|||
|
|||
renaming tables for each customer, used in one report
Everything is fully normalized.
Each customer is sending me a spreadsheet of their data (which is just a list of product codes they are using) for a one time (usually)report that I prepare and return to them. I guess I could keep appending this info to an ever lengthening "customer code table" and then have a parameter to limit to the customer I am generating a report for. I still have to do an import and now an append vs renaming the table. Also I really have no need to keep their old code list for more than a week or so. And periodically I'd have to cull all the old data out eventually. So my old method vs. this single table method seems to weigh in about equally. But I'll consider trying the latter. I think it will just be an empirical determination which is the easiest and least aggravating. -- Alan "Jeff Boyce" wrote: Alan Having a table named for each Customer is precisely how you'd have to handle your situation ... if you were limited to using a spreadsheet! Copying/importing your "flat" Excel data "as is" directly into the relational model provided by Access pretty much guarantees that you'll "pain yourself into a corner". You won't get the easy use of Access' relationally-oriented features/functions if you insist on feeding it 'sheet data. Before you go any further, I urge you to turn off your computer and sit down with paper and pencil to map out the data structures and relationships. If "normalization" and "relational" are not terms you're familiar with, spend time learning about them before resuming your use of Access. Regards Jeff Boyce Microsoft Office/Access MVP "adgorn" wrote in message ... I have a report that relies on a query of a table of customer specific data (imported fr Excel) joined with a couple of other tables whose content is fixed. I will be generating this report for several different customers each day and so will have to import several different sets of excel files daily. WHen I built the db, I called the customer table "table1" and so all the code references are to that. So today I got 3 new spreadhseets of customer data ready for importing to Access. I am bringing these in as "table1CustA", "table1CustB" and "table1custC". The original table1 I have contains bogus data that I used just for db design purposes. So my plan was to simply delete table1, then rename table1CustA to table1 and run the report for CustA. When this is done I would rename table1 back to table1CustA, then rename table1CustB to table1 and run CustB's report, etc. Can I just do this w/o worrying about the order of how I rename the tables and all will come out OK? ALso, I need to keep all the customer tables in the db because any customer might come back later with an additional request or need to fix something and I don't really want to go through reimporting that data from excel. I have noticed that when I change the name of a table that a query uses, it seems to propagate through the query, so I'm concerned that when I change the name back from table1 to table1CustA, that might screw things up for the next customer. Thanks. -- Alan |
#4
|
|||
|
|||
renaming tables for each customer, used in one report
To any whom I might offend, my apologies in advance.
When Jeff mentioned "fully normalized" he probably meant Normalized to Third Normal Form which is the most common level of rigor considered necessary for most relational database applications. As I understand what you are trying to do, it doesn't matter if your data is normalized. Just bear in mind that yours is a very special case for the use of Access. I believe that you're just using Access Reports to produce meaningful reports for your customers of their own data as massaged with your "other two files of fixed data". If I'm too far off the mark please post back with clarifications. One big question is "How do you send the Access Reports to your customers so that they can read them? Excel "reports" are no big deal. In the suggestions that follow I'll keep on breaking the relational rules. I just erased a couple of long paragraphs on a proposed Access design when it hit me that you may not be proficient in the design of Access. Not a put down but a consideration. If you're an Access novice it may be a better thing to stay in Excel all the way. Rather then speculate I'll look for your post back. Please tell us as much as you can about your application. In real-world terms, what is being done end-to-end? Also, what is your motivation for doing this thing in Access? HTH -- -Larry- -- "adgorn" wrote in message ... Everything is fully normalized. Each customer is sending me a spreadsheet of their data (which is just a list of product codes they are using) for a one time (usually)report that I prepare and return to them. I guess I could keep appending this info to an ever lengthening "customer code table" and then have a parameter to limit to the customer I am generating a report for. I still have to do an import and now an append vs renaming the table. Also I really have no need to keep their old code list for more than a week or so. And periodically I'd have to cull all the old data out eventually. So my old method vs. this single table method seems to weigh in about equally. But I'll consider trying the latter. I think it will just be an empirical determination which is the easiest and least aggravating. -- Alan "Jeff Boyce" wrote: Alan Having a table named for each Customer is precisely how you'd have to handle your situation ... if you were limited to using a spreadsheet! Copying/importing your "flat" Excel data "as is" directly into the relational model provided by Access pretty much guarantees that you'll "pain yourself into a corner". You won't get the easy use of Access' relationally-oriented features/functions if you insist on feeding it 'sheet data. Before you go any further, I urge you to turn off your computer and sit down with paper and pencil to map out the data structures and relationships. If "normalization" and "relational" are not terms you're familiar with, spend time learning about them before resuming your use of Access. Regards Jeff Boyce Microsoft Office/Access MVP "adgorn" wrote in message ... I have a report that relies on a query of a table of customer specific data (imported fr Excel) joined with a couple of other tables whose content is fixed. I will be generating this report for several different customers each day and so will have to import several different sets of excel files daily. WHen I built the db, I called the customer table "table1" and so all the code references are to that. So today I got 3 new spreadhseets of customer data ready for importing to Access. I am bringing these in as "table1CustA", "table1CustB" and "table1custC". The original table1 I have contains bogus data that I used just for db design purposes. So my plan was to simply delete table1, then rename table1CustA to table1 and run the report for CustA. When this is done I would rename table1 back to table1CustA, then rename table1CustB to table1 and run CustB's report, etc. Can I just do this w/o worrying about the order of how I rename the tables and all will come out OK? ALso, I need to keep all the customer tables in the db because any customer might come back later with an additional request or need to fix something and I don't really want to go through reimporting that data from excel. I have noticed that when I change the name of a table that a query uses, it seems to propagate through the query, so I'm concerned that when I change the name back from table1 to table1CustA, that might screw things up for the next customer. Thanks. -- Alan |
#5
|
|||
|
|||
renaming tables for each customer, used in one report
This is a pretty complex set of tables and queries to produce a custom report
for a hospital blood bank. From a hospital client we get a list of standard product codes and 3 modifiers to each code. This is what I have to import into Access. I then compare that info to a standardized list of codes that also has fields for descriptions (into which I have inserted line returns to make them readable in the report) and about 5 other attributes that are code specific. I then generate a report for the client that btw has conditional displays of data depending on what combinations of codes and their modifiers that are selected. Also, there is a bunch of concatenating of the data for the report display. I also have to include a bar code image for the code on the report so I figured out how to get those into the standard code table as OLE. Once the report is ready, I print to secure pdf and that's what I send back to the customer. I am simply looking for the most efficient way of managing bringing in the specific (and temporary) info from possibly hundreds of customers that come at me at a rate of 3-10 customers per day. -- Alan "Larry Daugherty" wrote: To any whom I might offend, my apologies in advance. When Jeff mentioned "fully normalized" he probably meant Normalized to Third Normal Form which is the most common level of rigor considered necessary for most relational database applications. As I understand what you are trying to do, it doesn't matter if your data is normalized. Just bear in mind that yours is a very special case for the use of Access. I believe that you're just using Access Reports to produce meaningful reports for your customers of their own data as massaged with your "other two files of fixed data". If I'm too far off the mark please post back with clarifications. One big question is "How do you send the Access Reports to your customers so that they can read them? Excel "reports" are no big deal. In the suggestions that follow I'll keep on breaking the relational rules. I just erased a couple of long paragraphs on a proposed Access design when it hit me that you may not be proficient in the design of Access. Not a put down but a consideration. If you're an Access novice it may be a better thing to stay in Excel all the way. Rather then speculate I'll look for your post back. Please tell us as much as you can about your application. In real-world terms, what is being done end-to-end? Also, what is your motivation for doing this thing in Access? HTH -- -Larry- -- "adgorn" wrote in message ... Everything is fully normalized. Each customer is sending me a spreadsheet of their data (which is just a list of product codes they are using) for a one time (usually)report that I prepare and return to them. I guess I could keep appending this info to an ever lengthening "customer code table" and then have a parameter to limit to the customer I am generating a report for. I still have to do an import and now an append vs renaming the table. Also I really have no need to keep their old code list for more than a week or so. And periodically I'd have to cull all the old data out eventually. So my old method vs. this single table method seems to weigh in about equally. But I'll consider trying the latter. I think it will just be an empirical determination which is the easiest and least aggravating. -- Alan "Jeff Boyce" wrote: Alan Having a table named for each Customer is precisely how you'd have to handle your situation ... if you were limited to using a spreadsheet! Copying/importing your "flat" Excel data "as is" directly into the relational model provided by Access pretty much guarantees that you'll "pain yourself into a corner". You won't get the easy use of Access' relationally-oriented features/functions if you insist on feeding it 'sheet data. Before you go any further, I urge you to turn off your computer and sit down with paper and pencil to map out the data structures and relationships. If "normalization" and "relational" are not terms you're familiar with, spend time learning about them before resuming your use of Access. Regards Jeff Boyce Microsoft Office/Access MVP "adgorn" wrote in message ... I have a report that relies on a query of a table of customer specific data (imported fr Excel) joined with a couple of other tables whose content is fixed. I will be generating this report for several different customers each day and so will have to import several different sets of excel files daily. WHen I built the db, I called the customer table "table1" and so all the code references are to that. So today I got 3 new spreadhseets of customer data ready for importing to Access. I am bringing these in as "table1CustA", "table1CustB" and "table1custC". The original table1 I have contains bogus data that I used just for db design purposes. So my plan was to simply delete table1, then rename table1CustA to table1 and run the report for CustA. When this is done I would rename table1 back to table1CustA, then rename table1CustB to table1 and run CustB's report, etc. Can I just do this w/o worrying about the order of how I rename the tables and all will come out OK? ALso, I need to keep all the customer tables in the db because any customer might come back later with an additional request or need to fix something and I don't really want to go through reimporting that data from excel. I have noticed that when I change the name of a table that a query uses, it seems to propagate through the query, so I'm concerned that when I change the name back from table1 to table1CustA, that might screw things up for the next customer. Thanks. -- Alan |
#6
|
|||
|
|||
renaming tables for each customer, used in one report
Alan
In Access, it all starts with the data. I'm finding it difficult to offer specific suggestions because I don't have a very specific idea of the data you are trying to do this from. Consider posting a "table structure" description, perhaps similar to (not meant as a sample of your situation): tblPerson PersonID FirstName LastName DOB tblClass ClassID ClassTitle trelRegistration RegistrationID PersonID ClassID RegistrationDate This is admittedly an over-simplified example. If you also have some real (but privacy-protected) data to show what you are working with, it might help clarify what you are seeking to do for those of us not intimately familiar with your situation. By the way, as Larry points out, you and I may not share the same definition of "normalization". Good luck! Regards Jeff Boyce Microsoft Office/Access MVP "adgorn" wrote in message ... Everything is fully normalized. Each customer is sending me a spreadsheet of their data (which is just a list of product codes they are using) for a one time (usually)report that I prepare and return to them. I guess I could keep appending this info to an ever lengthening "customer code table" and then have a parameter to limit to the customer I am generating a report for. I still have to do an import and now an append vs renaming the table. Also I really have no need to keep their old code list for more than a week or so. And periodically I'd have to cull all the old data out eventually. So my old method vs. this single table method seems to weigh in about equally. But I'll consider trying the latter. I think it will just be an empirical determination which is the easiest and least aggravating. -- Alan |
#7
|
|||
|
|||
renaming tables for each customer, used in one report
Hi Alan,
Thanks for your response. It gives me a much better idea of what's afoot and your motivation for posting. The implications of your issues and discussions as to trade-offs and how to address them are outside the scope and charter for these newsgroups. To attempt to address those issues here would lead to long drawn out threads of "20 Questions....", most of which would have nothing to do with Access, that could drag on indefinitely. If you would like to continue the discussion offline then decode my address and send along such information as you have on hand in an attached zip file. I'll honor the confidentiality of anything that may require it. HTH -- -Larry- -- "adgorn" wrote in message ... This is a pretty complex set of tables and queries to produce a custom report for a hospital blood bank. From a hospital client we get a list of standard product codes and 3 modifiers to each code. This is what I have to import into Access. I then compare that info to a standardized list of codes that also has fields for descriptions (into which I have inserted line returns to make them readable in the report) and about 5 other attributes that are code specific. I then generate a report for the client that btw has conditional displays of data depending on what combinations of codes and their modifiers that are selected. Also, there is a bunch of concatenating of the data for the report display. I also have to include a bar code image for the code on the report so I figured out how to get those into the standard code table as OLE. Once the report is ready, I print to secure pdf and that's what I send back to the customer. I am simply looking for the most efficient way of managing bringing in the specific (and temporary) info from possibly hundreds of customers that come at me at a rate of 3-10 customers per day. -- Alan "Larry Daugherty" wrote: To any whom I might offend, my apologies in advance. When Jeff mentioned "fully normalized" he probably meant Normalized to Third Normal Form which is the most common level of rigor considered necessary for most relational database applications. As I understand what you are trying to do, it doesn't matter if your data is normalized. Just bear in mind that yours is a very special case for the use of Access. I believe that you're just using Access Reports to produce meaningful reports for your customers of their own data as massaged with your "other two files of fixed data". If I'm too far off the mark please post back with clarifications. One big question is "How do you send the Access Reports to your customers so that they can read them? Excel "reports" are no big deal. In the suggestions that follow I'll keep on breaking the relational rules. I just erased a couple of long paragraphs on a proposed Access design when it hit me that you may not be proficient in the design of Access. Not a put down but a consideration. If you're an Access novice it may be a better thing to stay in Excel all the way. Rather then speculate I'll look for your post back. Please tell us as much as you can about your application. In real-world terms, what is being done end-to-end? Also, what is your motivation for doing this thing in Access? HTH -- -Larry- -- "adgorn" wrote in message ... Everything is fully normalized. Each customer is sending me a spreadsheet of their data (which is just a list of product codes they are using) for a one time (usually)report that I prepare and return to them. I guess I could keep appending this info to an ever lengthening "customer code table" and then have a parameter to limit to the customer I am generating a report for. I still have to do an import and now an append vs renaming the table. Also I really have no need to keep their old code list for more than a week or so. And periodically I'd have to cull all the old data out eventually. So my old method vs. this single table method seems to weigh in about equally. But I'll consider trying the latter. I think it will just be an empirical determination which is the easiest and least aggravating. -- Alan "Jeff Boyce" wrote: Alan Having a table named for each Customer is precisely how you'd have to handle your situation ... if you were limited to using a spreadsheet! Copying/importing your "flat" Excel data "as is" directly into the relational model provided by Access pretty much guarantees that you'll "pain yourself into a corner". You won't get the easy use of Access' relationally-oriented features/functions if you insist on feeding it 'sheet data. Before you go any further, I urge you to turn off your computer and sit down with paper and pencil to map out the data structures and relationships. If "normalization" and "relational" are not terms you're familiar with, spend time learning about them before resuming your use of Access. Regards Jeff Boyce Microsoft Office/Access MVP "adgorn" wrote in message ... I have a report that relies on a query of a table of customer specific data (imported fr Excel) joined with a couple of other tables whose content is fixed. I will be generating this report for several different customers each day and so will have to import several different sets of excel files daily. WHen I built the db, I called the customer table "table1" and so all the code references are to that. So today I got 3 new spreadhseets of customer data ready for importing to Access. I am bringing these in as "table1CustA", "table1CustB" and "table1custC". The original table1 I have contains bogus data that I used just for db design purposes. So my plan was to simply delete table1, then rename table1CustA to table1 and run the report for CustA. When this is done I would rename table1 back to table1CustA, then rename table1CustB to table1 and run CustB's report, etc. Can I just do this w/o worrying about the order of how I rename the tables and all will come out OK? ALso, I need to keep all the customer tables in the db because any customer might come back later with an additional request or need to fix something and I don't really want to go through reimporting that data from excel. I have noticed that when I change the name of a table that a query uses, it seems to propagate through the query, so I'm concerned that when I change the name back from table1 to table1CustA, that might screw things up for the next customer. Thanks. -- Alan |
Thread Tools | |
Display Modes | |
|
|