Re: Designing and Access Questions

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


.