Maximum no. of records....

[Guest]

I'm creating an Attendance record for a networking group which meets up
regularly at least once a month. There will be 2 tables. If 20 people
attend the August 30th Summer Barbecue, there would be a single record in the
Events table and 20 rows in the Attendance table. My question is, if 20 or
more people turn up to each event every time, then the Attendance table will
get longer and longer (bigger and bigger lengthways) quite quickly, is there
a limit to how many records that can be stored? I don't want to have to
re-design or create another table afterwards if this one fills up quickly.
The networking group will meet up continuously you see. If this table fills
up then do I just create a second one etc? I'm a newish user so any advice
would be greatly appreciated!

 

[Gijs van Swaaij]

I would be greatly surprised if there is a limit to the number of rows
that Access can store. I know that Access is often inferior to other
database programs like Oracle Database or OpenBase, but that would be a
serious lack in Access. Anyway, even if there is a limit, it will
probably be somewhere around several millions of records, so no problem
at all for you.

 

[Nikos Yannacopoulos]

There is no record number limit. There is a file size limit of 2GB, but
it seems unlikely you'll ever get anywhere near that with what you
describe. To give you an idea, I've got a database with billing data,
the invoice header table is currently at 200,000 + records, and the
invoice items tavble is currently at 700,000 + records, and the file
size is ca. 63MB.

Tip on your design: if you expect people to attend several events (as
opposed to one-time-attendees), then there is a clear many-to-many
relationship between people and events, and this implies your table
structure should actually comprise three tables:

tblEvents
EventID (PK, possibly autonumber)
EventDate
EventVenue
Event Decription
etc.

tblAttendees
AttendeeID (PK, possibly autonumber)
FirstName
LastName
etc.

tblEventAttendance
EventID (FK on tblEvents)
AttendeeID (FK on tblAttendees)
etc. (e.g. donation amount?)

With this design you only need fill in each attendee's details once,
instead of repeating them every time they append a new event. Also, if
you store their address, for instance, and they move, you only have to
update one record in tblAttendees, instead of one record per event they
have attended. This is called data normalization, and has a number of
advantages (the ones mentioned are just the obvious, and not even
necessarily the most important ones).

HTH,
Nikos

 

[Garret]

Since I'm still learning about relationships (which I consider the most
confusing part of Access), I tried making that DB that you suggested
Nikos, but ran into trouble. I wanted to know which fields in the
tblEventAttendance needed to be PrimaryKeyed. Whenever I try to set up
the relationship, it wont let me use tblEventAttendance as the first
table, I can only make it as the second table (the many part of the 1
to many), or if I set both EventID and AttendeeID on the
tblEventAttendance as both primary keys, I can set up only 1-1
relationships using tblEventAttendance as the first table. (If you
follow what I mean by "first table"). Maybe I just don't understand
which tables are supposed to be the parents of which. Help me please?
Thank you

 

[Nikos Yannacopoulos]

Garret,

No, I'm not sure what you mean by "first" table. As far as tblAttendance
goes, you can either (a) not make a PK at all, or (b) introduce an
additional field (autonumber) just for the purpose, or (c) make a
composite primary key comprising both foreign keys (EventID and
AttendeeID; foreign key = primary key in another table). I would opt for
the latter, because (a) it will prevent duplicate entries (as in
entering the same person attending any given event twice), and (b) it
will enforce indexes (as the plural of index is mostly referred, to, or,
correctly: indices) on the fields, and improve database performance.

I'm not sure I would agree that relationships are the most confusing
part of Access, and it's definitely not an Access thing! As a matter of
fact a robust, normalized data structure (what you actually refer to by
'relationships', I suppose) is the single most important part in any
database, regardless of the software it's built in. Everything else
(queries, reports, user interface etc) you can build on / modify / fix
at any stage in your development, but if you don't get your data
structure right before you go any further, you're in for serious
trouble. I suggest you do some reading on the subject, it will help you
enormously. You could start by googling for "relational database
design", "data normalization" and "entity relationship model".

HTH,
Nikos

 

[Garret]

I tried all three of your suggestions, each yeilding the same result.
What I mean by "first table" is the table that goes in column 1 of the
relationship builder window - the table that will be the parent (column
2 is the child).
In your (c) suggestion (where there are two PKs) I can manage to have
tblEventAttendance in the first column, but when it links to the other
two tables, it can only have a 1-1 relationship.
In more detail: I have my relationship window with three tables:
tblEvents, tblAttendees, tblEventAttendance. The tables are structed
just as you listed in a previous post. tblEventAttendance has PKs on
EventID and AttendanceID, (only other field in that table is
TotalRevenue). When I click on EventID on this table, and try to drag
it onto the EventID in the tblEvents, the relationship builder pops up.
The only option is to have a 1-1. If I do the opposite (drag from
tblEvents to tblEventAttendance), then it allows to set up a 1-many
relationship.
Perhaps I just don't understand which tables are going to be part of
which. Should the tblEventAttendance have the "+" on each record which
means it holds child records, or should it have something like
EventID AttendanceID Revenue
1 1 $50.00
1 2 $50.00
1 3 $50.00
2 1 $45.00
2 2 $45.00

Thanks for any help.

 

[Nikos Yannacopoulos]

I tried all three of your suggestions, each yeilding the same result.

To a certain extent, this is expected; it depends on the result you are
looking at.

In your (c) suggestion (where there are two PKs)

You can *never* have two PK's on a table! You may have one PK consisting
of several fields (what I call a composite PK) by selecting several
fields while in table design view, and clicking on the PK button (so you
then see the key sign next to all those fields), but you can't have
several separate PK's.

I can manage to have
tblEventAttendance in the first column, but when it links to the other
two tables, it can only have a 1-1 relationship.

This is funny; I'm not sure I'm following you.

In more detail: I have my relationship window with three tables:
tblEvents, tblAttendees, tblEventAttendance. The tables are structed
just as you listed in a previous post. tblEventAttendance has PKs on
EventID and AttendanceID,

NO! Not AttendanceID! Should be EventID, on which you join to tblEvents,
and AttendeeID, on which you join to tblAttendees (i.e. people). This
way you should get two one-to-many relationships, the many side being on
tblEventAttendance on both joins.

(only other field in that table is TotalRevenue).

No. This field is for storing the contribution of the particular
attendee (person) to the particular event, not the event total. To get
the event total, you need to sum this field on all records for the
particular event. If you just wanted to store the evetn total, without
caring for each person's contribution, then the field belongs in
tblEvents, not here.

When I click on EventID on this table, and try to drag
it onto the EventID in the tblEvents, the relationship builder pops up.
The only option is to have a 1-1. If I do the opposite (drag from
tblEvents to tblEventAttendance), then it allows to set up a 1-many
relationship.

Funny. It shouldn't make a difference which side you start from... at
least not if there are already a few records in the tables, so Access
(actually, Jet) can "see" the one vs. many entries.

Perhaps I just don't understand which tables are going to be part of
which. Should the tblEventAttendance have the "+" on each record which
means it holds child records,

This is a side effect of the joins; it is referred to as the table's
sub-datasheet property. Yet, the answer is no, it shouldn't have child
records conceptually; Acces may show it as having, and it's misleading.
In a correct design, tblEvents and tblAttendees should both have child
records in tblEventAttendance.

or should it have something like
EventID AttendanceID Revenue
1 1 $50.00
1 2 $50.00
1 3 $50.00
2 1 $45.00
2 2 $45.00

Wrong. See my comments above on the FK fields and the revenue field.

Nikos

 

[Garret]

You can *never* have two PK's on a table! You may have one PK consisting
of several fields (what I call a composite PK) by selecting several
fields while in table design view, and clicking on the PK button (so you
then see the key sign next to all those fields), but you can't have
several separate PK's.

This is what I have, I just had the theory wrong I guess. So even
though you see 2+ keys, there is still really only 1 PK then?

NO! Not AttendanceID! Should be EventID, on which you join to tblEvents,
and AttendeeID, on which you join to tblAttendees (i.e. people). This
way you should get two one-to-many relationships, the many side being on
tblEventAttendance on both joins.

I know, you misunderstood me. This is what I have been intending to
do. EventID on tblEventAttendance links to EventID on tblEvents, and
AttendanceID on tblEventAttendance links to AttendanceID on
tblAttendees.

No. This field is for storing the contribution of the particular
attendee (person) to the particular event, not the event total. To get
the event total, you need to sum this field on all records for the
particular event. If you just wanted to store the evetn total, without
caring for each person's contribution, then the field belongs in
tblEvents, not here.

Oh, I misunderstood, but thats not the issue right now anyway.

Funny. It shouldn't make a difference which side you start from... at
least not if there are already a few records in the tables, so Access
(actually, Jet) can "see" the one vs. many entries.

Whichever side I start from is the table that is in the first column of
the relationship builder window (the parent). The table that is
dragged onto is the second column (the child). Sometimes it doesnt
matter which side, but that is only when there is only one possible way
to have the tables link (I've found).

This is a side effect of the joins; it is referred to as the table's
sub-datasheet property. Yet, the answer is no, it shouldn't have child
records conceptually; Acces may show it as having, and it's misleading.
In a correct design, tblEvents and tblAttendees should both have child
records in tblEventAttendance.

Is there anyway I can send you the DB? Or you send me a proper working
version of the DB? This is Ac2000 btw.

 

[Tony Toews]

I would be greatly surprised if there is a limit to the number of rows
that Access can store. I know that Access is often inferior to other
database programs like Oracle Database or OpenBase but that would be a
serious lack in Access.

Inferior? Access, actually Jet and SQL Server/Oracle fill two
different niches. No idea what OpenBase is.

Anyway, even if there is a limit, it will
probably be somewhere around several millions of records, so no problem
at all for you.

Not at all. A friend has 100,000,000 records consisting of the daily
closing stock prices. And he can get to any stocks records in less
than a second. Obviously he has indexed the appropriate fields.
<smile>

And the limit is how many records can fit in 2 Gb MDB.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Nikos Yannacopoulos]

Nikos Yannacopoulos wrote:




This is what I have, I just had the theory wrong I guess. So even
though you see 2+ keys, there is still really only 1 PK then?

Yes, it is 1 PK. This means that each combination of values in all the
PK fields is unique, even though the values in each field alone may
repeat. For example, there may be several records with AttendeeID = 2
(one for each of several events he/she attended), and there may be
hundreds of records with EventID = 4 (one for each attendee in the
particular event), but there will only ever be one record with the 4 - 2
event/attendee combination. None of the two fields can uniquely identify
a record, but the two together can.

I know, you misunderstood me. This is what I have been intending to
do. EventID on tblEventAttendance links to EventID on tblEvents, and
AttendanceID on tblEventAttendance links to AttendanceID on
tblAttendees.

Is AttendanceID the PK in tblAttendees? The name you have chosen is
misleading.

Oh, I misunderstood, but thats not the issue right now anyway.




Whichever side I start from is the table that is in the first column of
the relationship builder window (the parent). The table that is
dragged onto is the second column (the child). Sometimes it doesnt
matter which side, but that is only when there is only one possible way
to have the tables link (I've found).

Depends on whether there are enough records in the tables already for
Jet to be able to determine the relationship type.

Is there anyway I can send you the DB? This is Ac2000 btw.

You are welcome to (compact and zip, if possible, and) send me your
database to look at; I have A2K3 so the version is no issue. The problem
is this is my last day in, I'll be off for the next two weeks, so it'll
be sometime before you hear back from me... unless you catch me before I
go today, my time zone is two hours east of GMT and I expect I'll be on
line until 16:00 local time. My address is available in the post header.

Or you send me a proper working version of the DB?

Sorry, I don't have one for events management. This was all theory.

Nikos

 

[Guest]

Thanks for the advice - in fact I already have the 3rd table (the Attendees
one) that you mentioned. All my info was originally in Excel and I
transferred the Attendees list into Access first. Now I'm onto the
Attendance list, which was just 1 spreadsheet in Excel, and to move it into
Access I was surprised to find I need to have 3 tables! At least I'm only
working on 2 now....

Thanks again!