New Access User

  • Thread starter Thread starter Ed
  • Start date Start date
E

Ed

I manage a large Apartment Community and need a program to do a better job
than the Excel databases we have now. Access looks like the way to go. Got a
copy and have been toying with it. I know what I want to, but can't figure
out how to put it into a User Friendly Format.

Any brave souls out there want to spend the next few weeks helping out a
NEWBIE?

Ed
 
I manage a large Apartment Community and need a program to do a better job
than the Excel databases we have now. Access looks like the way to go. Got a
copy and have been toying with it. I know what I want to, but can't figure
out how to put it into a User Friendly Format.

Any brave souls out there want to spend the next few weeks helping out a
NEWBIE?

Ed
Click to expand...

Well... all of us, if you'll post *specific* questions here. We're all
volunteers, donating time on the newsgroups.

If you want special one-on-one tutoring, or consulting on the development of
your specific database, you should expect to pay for such private service. You
may want to check around locally for an Access users group or a local Access
developer, or consider hiring someone remote to work with you.

On the other hand, if you wish, feel free to post reasonably clear and
detailed limited questions here, and the volunteers will try to help.

John W. Vinson [MVP]
 
Ed said:
I manage a large Apartment Community and need a program to do a better job
than the Excel databases we have now. Access looks like the way to go. Got
a
copy and have been toying with it. I know what I want to, but can't figure
out how to put it into a User Friendly Format.

Any brave souls out there want to spend the next few weeks helping out a
NEWBIE?
Click to expand...

Aren't there COTS packages that will do what you want? Might not be worth
re-inventing that wheel.

Keith.
www.keithwilby.com
 
John W. Vinson said:
Well... all of us, if you'll post *specific* questions here. We're all
volunteers, donating time on the newsgroups.

If you want special one-on-one tutoring, or consulting on the development of
your specific database, you should expect to pay for such private service. You
may want to check around locally for an Access users group or a local Access
developer, or consider hiring someone remote to work with you.

On the other hand, if you wish, feel free to post reasonably clear and
detailed limited questions here, and the volunteers will try to help.

John W. Vinson [MVP]
Click to expand...
-----------------------------------------------------------------------------------------------
John,

Hoping we can use Apartment Number as the KEY to all the databases!

We need a Main screen (switchboard) . A Resident field...Type in the Apt
number to show Resident's info (name(s), Phone #s, Lisence plate #s..etc.)

There needs to be an option to add new data to the Resident Table, save and
go back to the SWITCHBOARD.

There needs to be a SUITE INVENTORY on the Main. Type in an Apt# and get the
info on what I need...(READ, EDIT and SAVE) go back to the SWITCHBOARD

I think once I can figure this out, I will be able to expand it more on my
own. But I know I'll be back with more questions!

Ed
 

You certainly CANNOT do so, and it makes no sense to do so! A Primary Key must
be unique within the table - you can have one and only one record with that
key.

The Apartment number would be a key to the table of apartments. But if you
want to track (say) car license numbers, will you restrict each apartment ton
one and only one car? Or one and only one resident?

You will have one-to-many relationships using the ApartmentNumber as a
*foreign key* - a nonunique link to the Apartments table - but it will not be
the primary key.
We need a Main screen (switchboard) . A Resident field...Type in the Apt
number to show Resident's info (name(s), Phone #s, Lisence plate #s..etc.)
Click to expand...

STOP!!!!

Designing your forms first is like building your apartment building,
installing the windowframes and soffits, and then deciding where you're going
to pour the foundations.

Build your tables *first*. Forms *are just tools* - windows which let you
enter and edit data in tables. The tables are fundamental; if your table
design is incorrect, you'll be thrashing with all sorts of problems forever.
Design your tables and their relationships first, and THEN design forms to fit
those tables - not vice versa.

Just as a frinstance, you say "A resident field". That's incorrect! You will
need a table of Residents, related one to many to a table of Apartments; each
apartment may have zero, one, two or more residents, and you'll need a record
for each Resident. The Form to manage this data may well have a Form based on
the Apartments table, with a Subform based on the Residents table so that you
can add new residents as needed. Each Table would have fields pertinent to the
information that table records - the Residents table would have fields for
FirstName, LastName, maybe Birthdate, etc. etc. A Phones table would similarly
have multiple records per apartment (you very well might have two phones for a
resident).
There needs to be an option to add new data to the Resident Table, save and
go back to the SWITCHBOARD.

There needs to be a SUITE INVENTORY on the Main. Type in an Apt# and get the
info on what I need...(READ, EDIT and SAVE) go back to the SWITCHBOARD

I think once I can figure this out, I will be able to expand it more on my
own. But I know I'll be back with more questions!
Click to expand...

Do check out the tutorials at

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

John W. Vinson [MVP]
 
John and all...

Once I know that it can be done with Access, I would not rule out hiring
someone. However, I don't want someone to write it for me, I need to learn it
and be able to maintain it.

Ed
 
Do I need to have a different table for each? An Apartment Number table,
Resident table, carpet table, Phone Number table?

Ed
 
Do I need to have a different table for each? An Apartment Number table,
Resident table, carpet table, Phone Number table?
Click to expand...

Absolutely.

The first step in designing a database requires that you step away from the
computer. Turn it off, even. Get a pad of paper and a pencil (with a good
eraser, if my experience is a guide!).

Identify the Entities - real-life persons, things or events - of importance to
your application. Each type of entity - an Apartment, a Resident, a Carpet, a
Telephone - needs to have Attributes (discrete chunks of information that you
need to know about the entity). These attributes will belong only to that kind
of entity: a carpet doesn't have a wired/cellular attribute while a phone
number very well might. Each kind of Entity gets its own table; each Attribute
of an entity gets a field in that table.

Every Table needs a Primary Key. Autonumbers are often used for this purpose
but that's neither essential nor necessary. A Primary Key should meet three
requirements: it MUST be unique within the table (no two records can have the
same value); it SHOULD be stable, rarely or never changing; it HELPS if it's
compact (just so searching and sorting can be faster). For example, an
ApartmentNumber like "312A" meets all three conditions: you won't have two
apartments with that number, you won't often be changing it; and it's only 4
bytes long. A person's name is NOT a good choice, since it fails all three
critiera - different people sometimes do have the same name (I know three Fred
Browns for example), people change their names, and names can be lengthy.

If you find yourself with repeating fields - e.g. JanuaryPayment,
FebruaryPayment, MarchPayment - oops, you've found another entity (a Payments
event in this case) which needs another table.

You then need to identify the relationships between entities - for example,

Every Apartment has zero, one, or multiple Residents.
Every Resident resides in one and only one Apartment.

might be such a pair of rules, defining a One to Many relationship. To model
such a relationship in the table, create a field in the "many" side table
with the same datatype and size as the Primary Key in the "one" side table,
and use the Relationships window to link the two tables. This many-side table
field is called a "Foreign Key".

Check the tutorial links for further information.

John W. Vinson [MVP]
 
John and all...

Once I know that it can be done with Access, I would not rule out hiring
someone. However, I don't want someone to write it for me, I need to learn it
and be able to maintain it.
Click to expand...

Sounds like a good idea. Don't hesitate to post questions - but it would also
be worth your while to get a good book. There are many, and learning styles
differ, so what might be a good book for one person might not for another; you
may want to find a good bookstore and sit down with eight or ten Access books
and browse through them to see which fit your own style.

John W. Vinson [MVP]
 
Back
Top