more than 255 fields

[PAULinLAOS]

What do you do if you want to have more than 255 fields in 1 table? Is there
some trick to making one, or is it impossible?

Is it a bad structure to have that many fields? OK, my data entry form must
look like this:

ID Country 1Day 2Day 3Day 4/moreDay Total
1 America 4 0 2 4 10
2 Armenia 1 0 0 0 1
3 Brazil 0 2 1 0 2

This form has 82 countries on it. The columns stand for length of stay at a
guesthouse. It's used to collect data from guesthouses on how many people
stayed, their nationality and length of stay.

I started making a table like this:
Field1 Field2 Field3 Field4 Field5 Field6
....
Amer1day Amer2day Amer3day Amer4day Arm1day Arm2day....

This would allow me to make a data entry form that looks just like the
pencil and paper one. But too many fields! With 82 countries that's 328
fields.

I tried a different database structure where I put all of the country names
in one table and another table that would record the country ID and have 4
columns for the lenght of stay like this:

ID CountryID 1Day 2Day 3Day 4/moreDay Total
1 1 4 0 2 4 10
2 4 1 0 0 0 1
3 3 0 2 1 0 2

But when making a form, I have to enter in, via a combo box, the country
name, which makes data entry very long.

Any ideas? It seems like a simple problem but one that I really can't figure
out.

PaulinLaos

 

[mnature]

Generally speaking, if you think you need more than 255 fields in one table,
you are probably doing something wrong. Access is a relational database, not
a spreadsheet. It sounds as if you are trying to make the tables look like
spreadsheets.

First off, you probably need a table that just lists the countries. You
have 82 countries, so you will have 82 records in the table. There will be a
primary key field, and a country field. That is all you need in your country
table.

Then it sounds as if you are trying to collect information about how many
people have stayed in a guesthouse, what nationality they are, and how long
they have stayed. So you need a Guesthouse Table. This table will have
fields that will include a primary key, NumberOfGuests, NationalityGuests,
LengthOfStay. I assume that the nationality is what relates back to your
country table, so that would be a number field, and would tie back to the
country table in your relationships chart. Each record would then reflect a
particular stay, and would give you numbers, nationality, and length of stay.
You would then use queries, forms and reports to sort out this information.

As far as entering the country, you would do that on a data entry form,
probably as a pull-down combo box that gives you a list of all countries, and
you just choose the one you want.

 

[John Vinson]

What do you do if you want to have more than 255 fields in 1 table?

You don't.

Is there some trick to making one, or is it impossible?

It is both impossible and unnecessary.

Is it a bad structure to have that many fields?
Yes.

OK, my data entry form must
look like this:

ID Country 1Day 2Day 3Day 4/moreDay Total
1 America 4 0 2 4 10
2 Armenia 1 0 0 0 1
3 Brazil 0 2 1 0 2

Don't confuse data ENTRY with data STORAGE. They are two different
tasks with two different sets of requirements.

This form has 82 countries on it.

Design your Tables *FIRST* - logically, based on the structure of the
data. Starting the design with your Form is like building a house by
assembling the windows and windowframes first, and then deciding where
to pour the foundations after they're all done.

The columns stand for length of stay at a
guesthouse. It's used to collect data from guesthouses on how many people
stayed, their nationality and length of stay.

I started making a table like this:
Field1 Field2 Field3 Field4 Field5 Field6
....
Amer1day Amer2day Amer3day Amer4day Arm1day Arm2day....

This would allow me to make a data entry form that looks just like the
pencil and paper one. But too many fields! With 82 countries that's 328
fields.

No. It's three fields:

ID; Country; DaysOfVisit

I tried a different database structure where I put all of the country names
in one table and another table that would record the country ID and have 4
columns for the lenght of stay like this:

ID CountryID 1Day 2Day 3Day 4/moreDay Total
1 1 4 0 2 4 10
2 4 1 0 0 0 1
3 3 0 2 1 0 2

But when making a form, I have to enter in, via a combo box, the country
name, which makes data entry very long.

ummm... in what way does selecting the country name from an
autocomplete textbox differ from searching down a list of 82
textboxes!? It's FASTER, not slower!

Any ideas? It seems like a simple problem but one that I really can't figure
out.

Check out some of the suggestions at

http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html

especially the Database Design 101 links.

John W. Vinson[MVP]

 

[PAULinLAOS]

John,

Thanks for trying to sort out the problem. I agree on making the simple
structure that you recommend.

The problem with an autofill is this: my staff don't know the alphabet well,
so when there are multiple countries that start with the same name, it gets
confusing for them (I'm in Laos, believe me) and takes time. I'm looking for
a way to get the list box (or pull down combo box) to perhaps change each
time with each new record, going to the next country listed alphabetically.
That way they won't have to think about what the next country should be. I
suppose that I would have to make the list box based on a query of the
countries table, and after each record is completed, the it is requeried and
the list box appears to change.

Or maybe is there a way to tell the control on the form to move to the next
incrementally available ID for countries? Or way to make something like that
in the table itself, telling the column that holds the ID for countries that
it has to go incrementally from 1 - 82? If I could do that then on my form I
would put the list of countries in unbound text boxes, and assume that each
new record had an incrementally new ID for country. Am I making sense?
Hardly, huh.

This is killing me.

 

[John Vinson]

John,

Thanks for trying to sort out the problem. I agree on making the simple
structure that you recommend.

The problem with an autofill is this: my staff don't know the alphabet well,
so when there are multiple countries that start with the same name, it gets
confusing for them (I'm in Laos, believe me) and takes time. I'm looking for
a way to get the list box (or pull down combo box) to perhaps change each
time with each new record, going to the next country listed alphabetically.
That way they won't have to think about what the next country should be. I
suppose that I would have to make the list box based on a query of the
countries table, and after each record is completed, the it is requeried and
the list box appears to change.

Or maybe is there a way to tell the control on the form to move to the next
incrementally available ID for countries? Or way to make something like that
in the table itself, telling the column that holds the ID for countries that
it has to go incrementally from 1 - 82? If I could do that then on my form I
would put the list of countries in unbound text boxes, and assume that each
new record had an incrementally new ID for country. Am I making sense?
Hardly, huh.

Well, one question: in a typical data entry session do you have data
to enter for every country? Or is the data "sparse" - ten of the 83
countries?

If it's dense, and you ordinarily DO fill in at least something for
most of the countries - or, for that matter, if it's easier for your
workers to see all of the countries even if there is nothing to enter
- you can base a Subform on a Query joining the (82-row) Countries
table to the table you're updating. Join by CountryID, and be sure to
include BOTH the Countries.CountryID and your table's CountryID in the
Query (this will let Access autofill it). Use a "Left Outer Join" -
select the join line and say "Show all rows in Countries and matching
rows in <yourtable>".

Base the continuous Form on this query. Have a textbox for the country
name, but make that textbox's Enabled property False, Locked property
True - it's just to display the name, you don't want the user editing
it.

When they enter data into the other fields in your table, Access will
fill in the CountryID automatically.

John W. Vinson[MVP]

 

[PAULinLAOS]

Hi. Yes, I understand, and actually I set up the database the way you propose
to begin with. However, I need to make it as easy as possible for staff to
enter in the data. That's my real problem with the correct database structure.

Is there any way, that if I have a list box for countries on my form that
chooses from my 'countries' table that lists all the country names, that the
list box will change for each new record like this: say for the first record
the list box starts with 'Algeria', then for the next record it starts at the
alphabetically next country 'America', then for the next record the box's
first listing is 'Angola'? etc. until the last record, it's Zimbabwe and
stops.

If this is possible, then I could make the list box narrow enough to show
only one country name make it appear to automatically enter the successive
country name. That way the staff would continue entering the next succesive
country without any effort at all. (Using Autofills helps but not enough, as
my staff do not speak English well and do not really know the alphabet well
enough).

Do you understand what I'm getting at?