Re: Designing and Access Questions

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" <Emine@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A2F9437F-16F3-490F-9B30-E3681ED0609C@xxxxxxxxxxxxxxxx
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/accessjunkie/resources.html#DatabaseDesign101

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 spread***, and other issues.

.

Loading