A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

renaming tables for each customer, used in one report



 
 
Thread Tools Display Modes
  #1  
Old February 11th, 2008, 11:00 PM posted to microsoft.public.access.tablesdbdesign
adgorn
external usenet poster
 
Posts: 25
Default 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  
Old February 12th, 2008, 12:04 AM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default 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  
Old February 12th, 2008, 01:52 AM posted to microsoft.public.access.tablesdbdesign
adgorn
external usenet poster
 
Posts: 25
Default 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  
Old February 12th, 2008, 08:09 AM posted to microsoft.public.access.tablesdbdesign
Larry Daugherty
external usenet poster
 
Posts: 1,012
Default 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  
Old February 12th, 2008, 04:12 PM posted to microsoft.public.access.tablesdbdesign
adgorn
external usenet poster
 
Posts: 25
Default 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  
Old February 12th, 2008, 05:26 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default 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  
Old February 13th, 2008, 08:21 AM posted to microsoft.public.access.tablesdbdesign
Larry Daugherty
external usenet poster
 
Posts: 1,012
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:18 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.