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  

Linking DataBases



 
 
Thread Tools Display Modes
  #11  
Old September 26th, 2008, 12:38 AM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Linking DataBases

Mauricio

You may be running up against the difference between Excel (*a spreadsheet*)
and Access (*a relational database*).

In Excel you might just add another column to get another piece of data in
the spreadsheet.

In Access, doing the same thing (adding another field in a table) would NOT
be the correct way to handle the situation. Among other reasons, you'd have
to modify your queries, your forms, your reports, your code, ... every time!
What a maintenance nightmare!

Perhaps if you post a bit more information about just what it is that you're
importing/linking, folks here could offer more specific suggestions.

For example, if you are working with "monthly" values (and just adding
another month in Excel), you'll need to rethink how you structure your data
in Access if you wish to get full use of the relationally-oriented
features/functions in Access.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Maurício Paiva" wrote in message
...
Hi Jeff.
in fact I had to create the table before creating the excel links.
and You are right... it worked fine.

The problem now is query doesn't allow the table to be updated... I have
to
run it everytime to reflect to table.

For example:
I added new fields to the spreadsheet and the link into excel was updated
but not the new table with all info.
How can I do this real-time?

Thanks a lot!

"Jeff Boyce" escreveu:

Did you first create the new table?

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Maurício Paiva" wrote in
message
...
Jeff,
the idea with the queries is great... however when I try to append I
can
see
only the other excel lilnks... it doesn't show me that new table a
create
with the fields only.
Is there any trick?

Maurício

"Jeff Boyce" escreveu:

Mauricio

You have four linked 'tables' in Access.

Create a new table (empty) that is defined as needed.

Create a query that returns all the records from one of your four
linked
tables. Open that query in design view and modify it to be an append
query.
Append field (values) from your linked table to your permanent table.

Modify the query to "point" at a different linked table and append
that
table's records.

Repeat until finished.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Maurício Paiva" wrote in
message
...
Yes Jeff.
Actually I meant these 4 linked tables into one.
Can you pls instruct me how to create a query?

I tried some other functions but I always receive a message saying
that
tables are linked from excel and some actions could not be
performed.

Tks,
Maurício

"Jeff Boyce" escreveu:

Mauricio

When you say "merge these 4 tables", do you mean actually put the
data
from
each of the four sources into a totally new table?

An alternative to this would be to use a query to collect all of
the
data
in
the same place, and use that as the source for the form.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Maurício Paiva" wrote in
message
...
Hi!
I have 4 spreadsheets that are filled by 4 different users.
I am not an expert in Access but I created a Database and linked
all
of
these 4 spreadsheets into table section. (I Have: CA1, CA2, CA3
and
CA4)
Now I am trying unsuccessfully to merge these 4 tables in order
to
create
only one form.
Can anybody help me with this?

Thanks a lot.
Maurício











  #12  
Old September 26th, 2008, 02:27 PM posted to microsoft.public.access.tablesdbdesign
Maurício Paiva
external usenet poster
 
Posts: 8
Default Linking DataBases

Hi Jeff, good morning.
I apologize to give you this trouble.

We have 2 teams (pricing and sales) and they want to make sure
whenever an user updates its individual spreadsheet that it will reflect to
DB in access or any other suggestion.
There are specific fields in the spreadsheet that must not be seen by sales
team.

Just to give you an idea... the pricing team (4 people) will feed their
individual spreadsheet and the sales team will check for prices anytime a
customer asks for.

I am an infrastructure guy in the company and we hv no developers in here...
that's why I am lost with this case.

Was I clear? Could I clarify the idea?

Thank you,
MaurÃ*cio

"Jeff Boyce" escreveu:

Mauricio

You may be running up against the difference between Excel (*a spreadsheet*)
and Access (*a relational database*).

In Excel you might just add another column to get another piece of data in
the spreadsheet.

In Access, doing the same thing (adding another field in a table) would NOT
be the correct way to handle the situation. Among other reasons, you'd have
to modify your queries, your forms, your reports, your code, ... every time!
What a maintenance nightmare!

Perhaps if you post a bit more information about just what it is that you're
importing/linking, folks here could offer more specific suggestions.

For example, if you are working with "monthly" values (and just adding
another month in Excel), you'll need to rethink how you structure your data
in Access if you wish to get full use of the relationally-oriented
features/functions in Access.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"MaurÃ*cio Paiva" wrote in message
...
Hi Jeff.
in fact I had to create the table before creating the excel links.
and You are right... it worked fine.

The problem now is query doesn't allow the table to be updated... I have
to
run it everytime to reflect to table.

For example:
I added new fields to the spreadsheet and the link into excel was updated
but not the new table with all info.
How can I do this real-time?

Thanks a lot!

"Jeff Boyce" escreveu:

Did you first create the new table?

Regards

Jeff Boyce
Microsoft Office/Access MVP


"MaurÃ*cio Paiva" wrote in
message
...
Jeff,
the idea with the queries is great... however when I try to append I
can
see
only the other excel lilnks... it doesn't show me that new table a
create
with the fields only.
Is there any trick?

MaurÃ*cio

"Jeff Boyce" escreveu:

Mauricio

You have four linked 'tables' in Access.

Create a new table (empty) that is defined as needed.

Create a query that returns all the records from one of your four
linked
tables. Open that query in design view and modify it to be an append
query.
Append field (values) from your linked table to your permanent table.

Modify the query to "point" at a different linked table and append
that
table's records.

Repeat until finished.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"MaurÃ*cio Paiva" wrote in
message
...
Yes Jeff.
Actually I meant these 4 linked tables into one.
Can you pls instruct me how to create a query?

I tried some other functions but I always receive a message saying
that
tables are linked from excel and some actions could not be
performed.

Tks,
MaurÃ*cio

"Jeff Boyce" escreveu:

Mauricio

When you say "merge these 4 tables", do you mean actually put the
data
from
each of the four sources into a totally new table?

An alternative to this would be to use a query to collect all of
the
data
in
the same place, and use that as the source for the form.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP


"MaurÃ*cio Paiva" wrote in
message
...
Hi!
I have 4 spreadsheets that are filled by 4 different users.
I am not an expert in Access but I created a Database and linked
all
of
these 4 spreadsheets into table section. (I Have: CA1, CA2, CA3
and
CA4)
Now I am trying unsuccessfully to merge these 4 tables in order
to
create
only one form.
Can anybody help me with this?

Thanks a lot.
MaurÃ*cio












  #13  
Old September 26th, 2008, 03:44 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Linking DataBases

Maurício

I usually advise folks just starting out with Access that there are three
significant learning curves they'll need to be willing to take on if they
are to be successful using Access to develop an application.

1. relational database design -- if "normalization" and "relational" aren't
familiar terms, become familiar with them!
2. Access tricks and tips -- you'll need to learn the idiosynchracies of
how Access does things.
3. Graphical User Interface design -- what kind of design(s) work well for
users and what kinds work against users.

Oh yes, and you need experience as a developer!

If your situation doesn't allow the time or resources to handle all these
learning curves, you may need to consider hiring someone who has already
learned these.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP




"Maurício Paiva" wrote in message
...
Hi Jeff, good morning.
I apologize to give you this trouble.

We have 2 teams (pricing and sales) and they want to make sure
whenever an user updates its individual spreadsheet that it will reflect
to
DB in access or any other suggestion.
There are specific fields in the spreadsheet that must not be seen by
sales
team.

Just to give you an idea... the pricing team (4 people) will feed their
individual spreadsheet and the sales team will check for prices anytime a
customer asks for.

I am an infrastructure guy in the company and we hv no developers in
here...
that's why I am lost with this case.

Was I clear? Could I clarify the idea?

Thank you,
Maurício



  #14  
Old September 26th, 2008, 06:52 PM posted to microsoft.public.access.tablesdbdesign
Maurício Paiva
external usenet poster
 
Posts: 8
Default Linking DataBases

Hey Jeff.
Thanks a lot for your support.
We do need start thinking change our strategy... I will talk to staff and
see other available options or to hire someone who could help us with
development.

B.Rgds,
MaurÃ*cio

"Jeff Boyce" escreveu:

MaurÃ*cio

I usually advise folks just starting out with Access that there are three
significant learning curves they'll need to be willing to take on if they
are to be successful using Access to develop an application.

1. relational database design -- if "normalization" and "relational" aren't
familiar terms, become familiar with them!
2. Access tricks and tips -- you'll need to learn the idiosynchracies of
how Access does things.
3. Graphical User Interface design -- what kind of design(s) work well for
users and what kinds work against users.

Oh yes, and you need experience as a developer!

If your situation doesn't allow the time or resources to handle all these
learning curves, you may need to consider hiring someone who has already
learned these.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP




"MaurÃ*cio Paiva" wrote in message
...
Hi Jeff, good morning.
I apologize to give you this trouble.

We have 2 teams (pricing and sales) and they want to make sure
whenever an user updates its individual spreadsheet that it will reflect
to
DB in access or any other suggestion.
There are specific fields in the spreadsheet that must not be seen by
sales
team.

Just to give you an idea... the pricing team (4 people) will feed their
individual spreadsheet and the sales team will check for prices anytime a
customer asks for.

I am an infrastructure guy in the company and we hv no developers in
here...
that's why I am lost with this case.

Was I clear? Could I clarify the idea?

Thank you,
MaurÃ*cio




 




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 05:27 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.