size of table

[andrewm]

unfortunately I am making a form using more than 255 fields which is the max for access 97. I tried using more than 1 table but still no help.
what can I do? will upgrading help?

thanks

andrewm

 

[PARSO]

Im sure that using muliple tables and relationships would make it much easier

 

[neileg]

How can you read a form with 255 fields? I'm sure that there's something wrong with your database design. Are all of these fields populated in all of your records?

 

[Mile-O]

The moment you see fields that go Item1, Item2, Item3 or Monday, Tuesday, Wednesday, for example, this becomes an indication that you need a new table. I suspect this first step of normalisation is where you have went wrong.

Upgrading won't help as all versions of Access allow only 255 fields which is a hell of a lot more than is necessary.

Your problem, I suppose, is that you have tried to create a spreadsheet layout within a database table. Spreadsheets are why we have Excel.

 

[andrewm]

Im new at it

thanks,
I tried more than 1 table and combined using a query - the query is huge and doesn't allow it.
also how do I do a form based on a relationship
( I am trying to do a weekly roster with 350 "field" excel is "ok" but access should cope with it )

andrewm

 

[Mile-O]

also how do I do a form based on a relationship
( I am trying to do a weekly roster with 350 "field" excel is "ok" but access should cope with it )

Never mind the form for just now. You need to work on your table structure. Saying 350 fields in Excel is okay is irrelevant as you may as well just do it in Excel then. Excel is a spreadsheet application; Access is a database application and is, therefore, a completely different kettle of fish - even though a database table looks similar to an spreadsheet.

So, you need a weekly roster. Can you detail the fields you have? Maybe post an empty example of your database? What do you need? You need to identify entities - employees, departments, etc..

 

[andrewm]

database example

Thanks,

attached is the database file (form not finished) - help will be appreciated.

email to andrewm@senet.com.au


andrewm

 

[Mile-O]

attached is the database file (form not finished)

It's not attached. Remember to .zip it up and ensure it meets the upload size limit. And again, don't worry about the form.

 

[andrewm]

attachment

sorry - will attach file now.

the main table is obviously where I will save the data.
the other is where a combo box retrieves the data.

andrewm

 

[Mile-O]

Okay, had a look and you have tried to make a spreadsheet layout in a database.

I can see how to strip this down to a properly normalised structure (for your own reference I'd recommend this) but I'd need to know more about all this Monday, Tuesday, Wednesday, stuff. I can't see much of a pattern.

And what is the ultimate goal of this database?

 

[andrewm]

thanks, yes it is like a spreadsheet. I tried excel, but wasn't that user friendly for the secretary. I thought using access and with combo boxes it could be user friendly.
essentially - monday to friday, and allocating a person to each allocated area. Then eventually doing queries etc to work out different people's work allocation.

is there a simple example of what I am trying to do?

andrewm

 

[Mile-O]

is there a simple example of what I am trying to do?

I'll try and make a quick example template.

 

[Mile-O]

Why do you have a field called date?

Are the rosters made on a weekly basis or is it the same roster continued week after week? Do you need to store historical rosters?

 

[andrewm]

template

thanks,

yes I need a date ( week starting date ) and would like to keep historical data so to do queries on it ( eg. what I particular person had done in the past year etc )

ta

andrewm

 

[andrewm]

still no use

Hi,

Ive tried to normalise etc. still no avail.
Has anyone got a template table and form (with combo boxes) for a weekly roster to get me started.
Is there an example template on the net?

andrewm

 

[GabrielR]

following SJ McAbney suggestions:

First Do a table with the names of the people that are going to be included on the DB.

most likely you will need for that table:

1 column called lngEmpId set it to Autonumber and convert it to Primary Key
1 Column called strEmpName set it to text
If there is another info that you need that is related to employees only it must be here. Call the table tblEmployees

Second, do a table with the Name of the departments (allocations). Againg an Autonumber Id for the table: lngDeptId, and a string (text) for the name of the alocation strDeptName.

Third, Here comes the real deal:
for what I've seen in your example, you will need:

tblRoster

That table will need:

lngRosterId (Autonumber)
lngRosterDay (Number) --> You will need another table with the weekNames
lngRosterEvent (Number) -->Same Here, events will be:

Theater
Ward
Preadmission
CME
Consultant Etc ( This values will go on a table called tblEvent)

dtmDateEvent

and finally a table called tblEventType
that will hold 2 colums with one being an Autonumber Id lngTypeId
and the Other strTypeName

That is all you will need for that table.

Let me help you out with a little basic template to get you started, as SJ McAbney said, no need to worry about forms, this is far more important now.

 

[andrewm]

entering data

thanks for the help for the table..

I appologise if i do not explain myself clearly.

I want to use bound combo boxes to retieve info ( emp, dept etc ) and save it into the roster table. ( to make it user friendly ) I see the links between the tables. I still do not know the next step. do I make a form related to a query with all the tables mentioned?
sorry for harping on the point. there still will be eventually

7 days
20 allocated areas
each having a emp and description x 2
thus 288 unique fields

I made a small database similar to orders form - retrieved information from tables and saved the combo information in a "central" table

what am I missing in the above

If this is too hard I guess excel maybe my answer

i am keen to have a solution

(ps is there any templete access roster databases out there)

andrew

 

[neileg]

I want to use bound combo boxes to retieve info ( emp, dept etc ) and save it into the roster table. ( to make it user friendly ) I see the links between the tables. I still do not know the next step. do I make a form related to a query with all the tables mentioned?
sorry for harping on the point. there still will be eventually

7 days
20 allocated areas
each having a emp and description x 2
thus 288 unique fields

I made a small database similar to orders form - retrieved information from tables and saved the combo information in a "central" table

what am I missing in the above

What you are missing is that Access is a relational database. You are thinking in terms of a flat table, like Excel.

When you want to store lots of essentially similar information, your design should lead you to storing these as individual records (rows in Excel terms) not fields.

So your table would look like this:
tblRoster
RosterID, PK autonumber
Day Text, holding day of week
AreaID Number, linking to ID in tblArea holding 20 allocated area info
EmpID Number, linking to ID tblEmployee having a emp and description

Here you have 5 fields and an unlimited number of entries.

You are confusing the form design with the table design. That's because in Excel, the form and the data are the same thing (usually). In Access, data lives in tables, queries retrieve and manipulate data, forms display data and reports print it out.

 

[GabrielR]

Working on a little template to show.... Give me a couple of Hours so we can show you the diffrence between Excel and Access...

 

[Mile-O]

Sorry, I've been too busy to finish the template I was working on. This is where I got to...