renaming tables for each customer, used in one report

  • Thread starter Thread starter adgorn
  • Start date Start date
A

adgorn

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

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

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
 
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
 
Back
Top