Designing and Access Questions

I was always confused on the following and I hope that someone can actually
answer a couple of these questions for me. BTW all you Gurus on this forum
are absolutely fantastic! You guys are great! I've learned so much from all
your expertise advise. Anyways here are my questions:

1. How do I set-up relationships and what is considered a primary table?
2. How are the relationships in a table joined and what is the best way to
join tables? I know about the primary key, but for some reason, sometimes my
relationships do not work.
3. What is front end and back end?
4. How do you setup a database so that others can use it simultaneously?
5. When Excel spreadsheets are updated (ones that I will be using for
importing) what is the best way to automatically import the updated
information into the database?

If someone can provide these answers for me OMG I would truly appreciate it.
I am in the processing of actually designing one, have not started, but will
be tasked to start one.

Responses in-line.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Emine" wrote in message
...
I was always confused on the following and I hope that someone can actually
answer a couple of these questions for me. BTW all you Gurus on this
forum
are absolutely fantastic! You guys are great! I've learned so much from
all
your expertise advise. Anyways here are my questions:

1. How do I set-up relationships and what is considered a primary table?

Open the Relationships window.
In Access 2007, it's on the Database Tools tab of the ribbon.
In previous versions, it's on the Tools menu.

To create the relation, drag the field from one table, and drop it onto the
other.

Most relationships will be one-to-many. For example, one client has many
orders. Therefore the Clients table has a ClientID (primary key), and the
Orders table has a ClientID (foreign key, since a client can occur many
times in this table.) The primary table is the on on the ONE side of the
one-to-many relation -- the Clients table in this example.

2. How are the relationships in a table joined and what is the best way to
join tables? I know about the primary key, but for some reason, sometimes
my
relationships do not work.

If you have not yet done so, open the Northwind sample database, open the
Relationships window, and see how those relations work.

Here's a basic example of creating relationships:
Relationships between Tables (School Grades example)
at:
http://allenbrowne.com/casu-06.html

And another:
http://allenbrowne.com/casu-23.html

Here's a PDF on normalizing data:
http://allenbrowne.com/casu-23.html

And a bunch more links to read:
http://www.accessmvp.com/JConrad/acc...abaseDesign101

3. What is front end and back end?

See:
Split your MDB file into data and application
at:
http://allenbrowne.com/ser-01.html

The back end is the shared data file that contains only the tables. The
front end is the program that links to the tables. Each user has their own
copy of the front end, so they don't interfere with each other.
4. How do you setup a database so that others can use it simultaneously?

Access is multi-user, so can do this. But for best results and ease of
maintenance, split as described above.

5. When Excel spreadsheets are updated (ones that I will be using for
importing) what is the best way to automatically import the updated
information into the database?

That's a much bigger question, and involves quite a bit of work, because you
need to handle:
- new rows (appending records)
- removed rows (deleting records)
- altered rows (updating records)
and it includes ways to distinguish between these, and to synchronize any
multi-user conflicts (edits in both places.)

That's on top of the usual import issues where data comes from untyped
columns (in Excel someone can type "Not applicable" into a date column),
subtotals and inserted rows between data, cells that are blank in Excel, but
marked Required in your database, new values in lookup columns,
creating/deleting/updating records in related tables to respond to columns
in the non-normalized spreadsheet, and other issues.

"Emine" wrote in message
...
Anyways here are my questions:


Sounds like a homework assignment to me but ... you should find all you need
he

http://allenbrowne.com/tips.html

Keith.

Thank you so much Allen. You are fantastic!! I've read a lot of your post
and have so much information and some designs you've created on my PC at home.

OK now to your responses, I still just need a little more clairification on
some of your answers please:

Response to #3: Can you give me an example? I still dont know about the
spliting and unsure of what you mean. And what do you mean each user has
their own copy.
Do I need to set this up for multi users before I even being design the
database and where do I begin. I've never done that?

Response to #5: Is this almost an impossible feat? Can it be done? Is
there something I can read so that I can try and incorporate as part of my
database?

Just for thought: I know from past experience, most programmers do not like
using Access because it requires a lot of twigging and lots of VBA, so they
opt to use Crystal. But I'm not a programmer and will Question #5 really
give me a hard time?

Again, THANK YOU-THANK-YOU!!!




"Allen Browne" wrote:

Responses in-line.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Emine" wrote in message
...
I was always confused on the following and I hope that someone can actually
answer a couple of these questions for me. BTW all you Gurus on this
forum
are absolutely fantastic! You guys are great! I've learned so much from
all
your expertise advise. Anyways here are my questions:

1. How do I set-up relationships and what is considered a primary table?

Open the Relationships window.
In Access 2007, it's on the Database Tools tab of the ribbon.
In previous versions, it's on the Tools menu.

To create the relation, drag the field from one table, and drop it onto the
other.

Most relationships will be one-to-many. For example, one client has many
orders. Therefore the Clients table has a ClientID (primary key), and the
Orders table has a ClientID (foreign key, since a client can occur many
times in this table.) The primary table is the on on the ONE side of the
one-to-many relation -- the Clients table in this example.

2. How are the relationships in a table joined and what is the best way to
join tables? I know about the primary key, but for some reason, sometimes
my
relationships do not work.

If you have not yet done so, open the Northwind sample database, open the
Relationships window, and see how those relations work.

Here's a basic example of creating relationships:
Relationships between Tables (School Grades example)
at:
http://allenbrowne.com/casu-06.html

And another:
http://allenbrowne.com/casu-23.html

Here's a PDF on normalizing data:
http://allenbrowne.com/casu-23.html

And a bunch more links to read:
http://www.accessmvp.com/JConrad/acc...abaseDesign101

3. What is front end and back end?

See:
Split your MDB file into data and application
at:
http://allenbrowne.com/ser-01.html

The back end is the shared data file that contains only the tables. The
front end is the program that links to the tables. Each user has their own
copy of the front end, so they don't interfere with each other.
4. How do you setup a database so that others can use it simultaneously?

Access is multi-user, so can do this. But for best results and ease of
maintenance, split as described above.

5. When Excel spreadsheets are updated (ones that I will be using for
importing) what is the best way to automatically import the updated
information into the database?

That's a much bigger question, and involves quite a bit of work, because you
need to handle:
- new rows (appending records)
- removed rows (deleting records)
- altered rows (updating records)
and it includes ways to distinguish between these, and to synchronize any
multi-user conflicts (edits in both places.)

That's on top of the usual import issues where data comes from untyped
columns (in Excel someone can type "Not applicable" into a date column),
subtotals and inserted rows between data, cells that are blank in Excel, but
marked Required in your database, new values in lookup columns,
creating/deleting/updating records in related tables to respond to columns
in the non-normalized spreadsheet, and other issues.

I will answer what I can in line:

"Emine" wrote in message
...
I was always confused on the following and I hope that someone can actually
answer a couple of these questions for me. BTW all you Gurus on this
forum
are absolutely fantastic! You guys are great! I've learned so much from
all
your expertise advise. Anyways here are my questions:

1. How do I set-up relationships and what is considered a primary table?

I am not familiar with the term primary table. Do you mean Parent table?
In a one to many relationship, the Parent table is on the One side of the
relationship and the Child table is on the many side. For example, One
Library has Many Books. You set up relationships in the relationship
window. You get to it from the Tools menu.

2. How are the relationships in a table joined and what is the best way to
join tables? I know about the primary key, but for some reason, sometimes
my
relationships do not work.

What do you mean by relationships do not work?
When joining two tables in a one to many relationship, you don't usually
join the primary key fields of both tables. It is usually the primary key
field of the Parent table and another field in the Child table. The field
you join in the Child table has to have the value of it's parent's primary
key field.

3. What is front end and back end?

An Access application should always be split using the database splitter.
The Front end is the application. It will contain forms, modules, macros,
queries, and reports. The backend is the actual "database" part. It
contains the tables, indexes, and relationships. The front end links to
tables in the backend to access the data. When multiple users will be
sharing the database, the back end should be in a shared network folder
where all users have read, write, delete privledges. Each user should have
a copy of the front end on their own computer. It should not be shared.

4. How do you setup a database so that others can use it simultaneously?

This is partially answered in #3, but additionally, when you set your
application options, (Tools, Options, Advanced tab) set the following:
Default open mode - Shared
Default record locking No locks
Check Open databases using record-level locking

5. When Excel spreadsheets are updated (ones that I will be using for
importing) what is the best way to automatically import the updated
information into the database?

Use the TransferSpreadsheet method or action. You can find details on it in
VBA Help. It is a method when used in VBA and an Action when used in a
macro. The advantage of using VBA is you are able to make it more flexible.
For example, allowing the user to select an Excel file to import at run time
more easily.


If someone can provide these answers for me OMG I would truly appreciate
it.
I am in the processing of actually designing one, have not started, but
will
be tasked to start one.

Ideally, splitting is something you do right near the end of the development
project, just before you give it to users to test.

Do you know what attached tables are? That's the first thing to understand.
As an example, if you wrote a sales database, you would split it so all the
tables and relationships are in an MDB in a shared folder. The front end is
a separate MDB that doesn't store any data. The tables are attached to the
back end. You copy the front end onto Jenny's machine, and she runs the
program there, writing the data to the back end. You also copy the MDB onto
Rolf's machine; he runs his copy of the program, and it also writes the data
to the back end. Since each has their own copy of the front end, they can do
things like filtering and reporting and even using public variables without
interferring with the other users. It is much more robust. Additionally, if
you do an update, you can copy your updated MDB onto Rolf's machine without
destroying the data. If it was not split, when you replaced the MDB with a
new one, you just replaced all his data as well.

There are various ways to handle #5, but none of them are things you can
resolve in a newsgroup post. The best solution would be for people not to
alter the data in Excel: instead use Access. Export copies of the data in
Excel if you wish, but don't try to synchronise it back into Access.

Another alternative might be to use something like Sharepoint to handle the
distributed data instead of Excel. You would need to design the application
from the start for Sharepoint if you want to go that way.

Another alternative would be to just replace all the existing data in Access
with the updated data in Excel. This assumes lots of horrid things, such as
that there is exactly one canonical copy of the data (not multiple Excel
files to be somehow synchronised), that the data is valid, that the data
types are correct, and many other things that are not easy to enforce.
Ultimately Excel is not a database, and lacks relational power and
integrity, data-typed columns, adequate validation, and so on. I can't give
you a simple fix for this

HTH

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Emine" wrote in message
...
Thank you so much Allen. You are fantastic!! I've read a lot of your
post
and have so much information and some designs you've created on my PC at
home.

OK now to your responses, I still just need a little more clairification
on
some of your answers please:

Response to #3: Can you give me an example? I still dont know about the
spliting and unsure of what you mean. And what do you mean each user has
their own copy.
Do I need to set this up for multi users before I even being design the
database and where do I begin. I've never done that?

Response to #5: Is this almost an impossible feat? Can it be done? Is
there something I can read so that I can try and incorporate as part of my
database?

Just for thought: I know from past experience, most programmers do not
like
using Access because it requires a lot of twigging and lots of VBA, so
they
opt to use Crystal. But I'm not a programmer and will Question #5 really
give me a hard time?

Again, THANK YOU-THANK-YOU!!!

Thank you to all. What I need to do is really catch up on my homework and
practice excercises. I know once I start practicing it will all come back to
me. Its been 3 years since I've touched Access.

Again, ALL you guys are fantastic! I don't know how to thank you enough!

"Klatuu" wrote:

I will answer what I can in line:

"Emine" wrote in message
...
I was always confused on the following and I hope that someone can actually
answer a couple of these questions for me. BTW all you Gurus on this
forum
are absolutely fantastic! You guys are great! I've learned so much from
all
your expertise advise. Anyways here are my questions:

1. How do I set-up relationships and what is considered a primary table?

I am not familiar with the term primary table. Do you mean Parent table?
In a one to many relationship, the Parent table is on the One side of the
relationship and the Child table is on the many side. For example, One
Library has Many Books. You set up relationships in the relationship
window. You get to it from the Tools menu.

2. How are the relationships in a table joined and what is the best way to
join tables? I know about the primary key, but for some reason, sometimes
my
relationships do not work.

What do you mean by relationships do not work?
When joining two tables in a one to many relationship, you don't usually
join the primary key fields of both tables. It is usually the primary key
field of the Parent table and another field in the Child table. The field
you join in the Child table has to have the value of it's parent's primary
key field.

3. What is front end and back end?

An Access application should always be split using the database splitter.
The Front end is the application. It will contain forms, modules, macros,
queries, and reports. The backend is the actual "database" part. It
contains the tables, indexes, and relationships. The front end links to
tables in the backend to access the data. When multiple users will be
sharing the database, the back end should be in a shared network folder
where all users have read, write, delete privledges. Each user should have
a copy of the front end on their own computer. It should not be shared.

4. How do you setup a database so that others can use it simultaneously?

This is partially answered in #3, but additionally, when you set your
application options, (Tools, Options, Advanced tab) set the following:
Default open mode - Shared
Default record locking No locks
Check Open databases using record-level locking

5. When Excel spreadsheets are updated (ones that I will be using for
importing) what is the best way to automatically import the updated
information into the database?

Use the TransferSpreadsheet method or action. You can find details on it in
VBA Help. It is a method when used in VBA and an Action when used in a
macro. The advantage of using VBA is you are able to make it more flexible.
For example, allowing the user to select an Excel file to import at run time
more easily.


If someone can provide these answers for me OMG I would truly appreciate
it.
I am in the processing of actually designing one, have not started, but
will
be tasked to start one.