A rookie's first effort - I'm so proud :o)

[James Ivey]

http://www.iampeth.com/stuff/IAMPETH.mdb

Total Access Newbie here.

I would be very appreciative if a few experts would take a
look at my first efforts.

Its a membership database for an organization with approximately
450 members.

Its only got 5 records in it... filesize is 316 Kb

I'm wide open to critique and suggestions.

Thanks in advance!

James Ivey

 

[bsmith59]

http://www.iampeth.com/stuff/IAMPETH.mdb

Total Access Newbie here.

I would be very appreciative if a few experts would take a
look at my first efforts.

Its a membership database for an organization with approximately
450 members.

Its only got 5 records in it... filesize is 316 Kb

I'm wide open to critique and suggestions.

Thanks in advance!

James Ivey

Hi James,

It may be hard to critique without some further details about what you
eventually want to do with it. For example, will you use it for mass
mailings? Think about those sorts of things and then you'll get
better guidance.

The other suggestion is to avoid recreating the wheel. What you've
done is great practice, but contact mdb templates are a dime a dozen,
and generally all free. So explore using a template, and tweak it to
include "membership" attributes you want to track.

Cheers,
Brandon Smith-Daigle
accesspro.blogspot.com (access tips for non-programmers)

 

[James Ivey]

Hi James,

It may be hard to critique without some further details about what you
eventually want to do with it. For example, will you use it for mass
mailings? Think about those sorts of things and then you'll get
better guidance.

The other suggestion is to avoid recreating the wheel. What you've
done is great practice, but contact mdb templates are a dime a dozen,
and generally all free. So explore using a template, and tweak it to
include "membership" attributes you want to track.

Cheers,
Brandon Smith-Daigle
accesspro.blogspot.com (access tips for non-programmers)

Thanks Brandon. I appreciate your response.

As far as what we're going to do with it, at this point we hardly have a
clue )

Seriously, up to now, all our membership data has been kept in a
spiral-bound notebook 6 inches thick. 30 years worth of hand-written
information. Ouch.

So at this point, my main concerns are to identify the important bits, and
capture the data. Last Sunday I knew nothing about Access, so I'm as green
as can be.

But yes, I will definately explore using a template. And yes, it has been
great practice (and a good bit of fun as well!).

Best regards,
James

 

[Allen Browne]

James, there is a sample membership database by John Viescas that you can
buy for the just cost of the documentation:
http://www.microsoft.com/mspress/books/7107.asp

You can even read the relevant documentation on-line here:
http://msdn2.microsoft.com/en-us/library/aa662176(office.11).aspx

 

[James Ivey]

Looks good Allen. I just completed my order )

Thanks.

James

 

[Kirstie Adam]

Hi James, i am no way an expert, still a beginner myself, but i still was
interested to have a look! (i am overly excited by other people's databases.
my friends worry!)

For some reason the link doesn't work for me though, can i get to it by
clicking any links on the website - i can get to that?

Kirstie

 

[James Ivey]

Hi Kirstie,

Try it now )

James

Hi James, i am no way an expert, still a beginner myself, but i still was
interested to have a look! (i am overly excited by other people's
databases. my friends worry!)

For some reason the link doesn't work for me though, can i get to it by
clicking any links on the website - i can get to that?

Kirstie

 

[James Ivey]

Its really no big shakes... its just a form

But I'm proud of it ))

 

[Kirstie Adam]

Got it thanks!
One thing i always do when i create a form, is base it on a query of the
table rather than a table directly. This makes the info easier to manipulate
when your database gets a bit more complex.

Also, check out this website, it's top of my favourites list:
http://www.mvps.org/access/
especially, the ten commandments, which makes me smile
http://www.mvps.org/access/tencommandments.htm
and this is one i found recently...
http://www.functionx.com/access/index.htm

Good luck!

Kirstie

 

[Allen Browne]

Hi James, i am no way an expert, still a beginner myself, but i still was
interested to have a look! (i am overly excited by other people's
databases. my friends worry!)

Oh, dear, Kirstie. That's a serious worry!

It's a sure sign that you are getting addicted to Access.

 

[James Ivey]

I'm not really getting what you mean (

Could you look at mine and give me an example?

Thanks!

 

[Kirstie Adam]

Sure, however as i am not that experienced, i hope someone jumps in if
anything i say is wrong!

I always think of a form as a window that lets you look at your information
the way you want. However, as your database increases in size and
complexity, you might not always want to see that particular view! Sometimes
you'll want to see only a part of the table, and once you have more tables
you might only want to see a couple of fields from each.

eg. At the moment you have a table of members, and in it it says how many
conventions they have attended. At one point you might have a table
containing info on the conventions. And you may want to be able to click a
button and see only the members name and state and a list of each convention
they have been too.

This is when you start using queries to help you use a Pick 'N' Mix approach
(my words!) to what bits of what tables you want to view.

You then create your form based on the query, to make viewing and editing
more user friendly.

This is why whenever i build a form, i create a query to run it from, rather
than a table. Even if, as is the case at the moment, you only want to see
everything in the table, it makes life a bit simpler in the future!

To create a query, click on the Queries button, then click Create Query in
Design View.

When the window opens up there will be a box, with a list of all your tables
in it. In this case, it will just have your table Members. Click on table
Members, then click Add. This will add your table into the design grid of
the query. You can then click close.

If you only want to see certain bits of your table, just double click each
field you want to see, and it will move it down to the bottom half of the
screen.

If you want to use the whole table, as is the case here, double click the *
sign at the top of the list. Save your query with a meaningful name
(something like qryMembers)

Then create your form from that. (In this case, as you already have a form
created - open the form in design view, and bring up the form properties.
Under the Data tab there is a section called RecordSource. Click in this
section and from the drop-down list select your newly created query - this
changes where the form is gathering its info to from the table to the
query.)

Have just realised i have typed loads, i hope it makes sense!

Kirstie

 

[Guest]

You sound enthusiastic about addiction to Access

The learning curve is never-ending, don't you think?

 

[Kirstie Adam]

I think that learning curve is the main reason i like creating databases
through access so much! I am both easily bored (like to always be learning
new things) and a very organised person, so it suits me down to the ground!

 

[Fred Boer]

.... two new members to be assimilated into the collective. I'll inform Jeff
to prepare two new regeneration chambers.

(Inside joke: Jeff Conrad, Access Junkie describes Access devotees as
members of the Access collective - as per the Borg of Star Trek TNG...)

Cheers!
Fred

 

[Fred Boer]

Dear James:

Ah, the thrill of making something with Access. It won't stop, you know! I
remember posting a message saying something like "My application is now
finished" ... That was three years ago and I'm still working on it!

Browse these newsgroups as a daily ritual. Look at other examples - like the
Viescas you've ordered. Keep having fun! Suggestions.. hm. Think about
creating a separate table to hold a list of cities, and then use that as a
lookup table - rather than storing cities as a field in your main table.
That will keep you learning!

Oh, and look at the 10 commandments!
http://www.mvps.org/access/tencommandments.htm

Cheers!
Fred Boer

 

[BruceM]

To add a bit to what has already been written, a query let's you arrange the
information to suit your needs. It also is a way of performing
calculations, which includes combining first and last names (and whatever
else you need) into a more readable format.
The basics of creating a query have already been described, along with some
other helpful suggestions about maybe having a separate related Conventions
table (you may want a Notes table as well), but I will add that many
developers consider it best to add the fields individually rather than using
the asterisk when you need all of the fields.
You can place the LastName field to the left of the FirstName field, and set
the Sort Order for each field to Ascending to sort by LastName, then
FirstName. Also, at the top of a blank column in query design view:
FullName: [First_Name] & " " & [Last_Name]
Then when you want the full name to appear on a form or report, just use
FullName as the control source for a combo box. Similarly, you can combine
the address fields:
CSZ: [City] & ", " & [State] & " " & [Zip]
or whatever exactly the fields are named.
This is called concatenation. Help has more information about.
Another web site that is well worth checking, both for it's helpful
information and for its collection of links (on the right side of the page),
is here:
http://allenbrowne.com/tips.html

 

[Guest]

Hi James,

I am not an expert, but I think you did a great job for a newbie!

Here is a tip you might want to use as your database grows:

If you forsee that you will have many records, there are some things you can
do to help your database run faster by not storing the same data many times.

For example, think of how many times the same state, zipcode, or country
will be stored in your DB. To avoid needless repetitive data storage you can
create a PostalCode table that holds the ZipCodes, Cities, States (if you
plan to do mailings you might want to use the two letter state abbreviation),
and countries.

I use the Postal Code as the Primary Key Field since the Postal Code numbers
are not likely to change. (For almost everything else I use an ID Autonumber
field as the Primary Key)

In your Main table keep the Postal_Code field. Create a relationship
between the two tables based on the Postal Code.

You can use a query as your form Record Source or you can use the following
method to add the information to your Form:

Change your Postal Code text box to a combo box. Then you can use the combo
box on your form to add the Postal Code and the Citiy, State, and Country
information automatically.

You will have to create a query for your Postal Code combo box that includes
the City, State, and Country information. Use that query as the Row Source
for the combo box. Open your properties sheet for your combo box. Click the
"Format" tab. Set the following properties:

Column Count: 4 --Be absolutly sure that you have the total
number of columns here.
Column Heads: Either Yes or No
Column Widths: 0.7";1.5";1";1.5" --Change these to however it suits you
List Rows: 25
List Width: 5" --Be sure to set this wide enough if you want to see the
data when you click on the combo box.

After this, you will need to make your City, State and Country text boxes
Unbound. Then add to your Record Source for the City, for example, the
following expression: =cboPostal_Code.Column(1)

The Column is the Column in your Query. Access assigns the first column in
a Query the number 0, the second column number 1, and so on. You will use
the column number for "City", whatever that may be. Do the same with the
State and Country text boxes.

You will need to add the following code to the Forms On Current Event so the
Combo Box and text boxes will display the information. Your code should look
like this:

Private Sub Form_Current()

Me.cboPostal_Code.Requery

End Sub

Using this method you will store the City, State, and Country only once.
This can save a lot of memory and help your database to run faster.

Once you learn how to do this, you can use this method for your Membership
Status, Type, etc.

Of course, you can use a Query as the Form's record source which includes
the Postal Code Table Fields. Most of the time that will work very well.
However, sometimes when you use a query for the Form Record Source the query
will not allow you to add data to your tables. This can happen when your
Query uses data from more than one table. If you prefer to use queries, and
have that problem, you may be able to use the method I described to solve it.

I hope that is not too much info at one time. You can alway try it later if
you prefer.

Hunter57
http://accesstips.wordpress.com/
http://www.churchmanagesoftware.com/

 

[Guest]

That will not always work. There are situations (I live in one) where one
zip code has more than one town. I don't know about where you are, but here,
the Post Offce gets snarly if you use the incorrect town name even when the
zip code is correct.

 

[Guest]

Hi,

Wow, thanks for the info, I never heard that before. Are the last four
digits in the extended zipcode the same or are they different?

Hunter57