255 fields is the max in everything!? : (

Nope - 255, end of story.

You can search the help for all limitations:
Open Mr PaperClip and type "Specifications", and read "Access specifications".

Can you explain why oh why you "need" > 255 fields -> Sounds rather un-normalis(z)ed to me. { http://en.wikipedia.org/wiki/Database_normalization }

Dave

looks like the query has the limit of 255 fields also, not the form?:

http://www.databasedev.co.uk/access_specifications.html

wow.  I keep getting hosed / not sure if I am making this more complicated than it needs to be?

I have the query driving the combo box to pick the client to display?  just punt that and scroll through the clients with the buttons at the bottom of the form?  But how do I get the data of both tables in the single form, allow displaying and editing of that data for that client?

arghhhhh   access is hard! : (

> But how do I get the data of both tables in the single form, allow displaying and editing of that data for that client?
You don't  - unless you are using subforms - and from what I understand, this is not what your after with your current architecture

Back to my other point:
Can you explain why oh why you "need" > 255 fields?

  As Flavo has said, it's your database design that's at fault.  It's *very* rare to come across any relational database design that needs more then 40 or 50 fields at most for any given table.

  In twenty two years, I've only seen two that really had a problem with the 255 field limit.

JimD.

ARGGGGH

you guys would know best, but.....

I've developed 20 or so pages of info about small networks - firewall, router, internet provider, shared folders, users, groups, network printers, server software, client machine software, machine specs (that one I haven't really tried including in this), etc.  I have them looking really nice in word and at first each was it's own document.  then I strung them into 1 long 20 page doc, so you can move up and down through all the client's info.

It's gotten unweildy because I'll realize I left out a field / want to add some info and have to edit the 20 or so clients docs that already exist along with the template.  So I thought an access database would be the way to go and dump the data into the template via a word mail merge.

So I start creating the table.  some of the data ARE tables, but I resorted to user1, username1, user2, username2, etc. all in a flat file / 1 table / 1 record per user.

so yeah, printer, printerlocation1, etc. does eat up some records.  but these are small businesses so there's only a few of each.  And yeah, doing user1, user2, sets a finite limit of users, finite number of shared printers, etc... a trade off for my ignorance of access : (

I understand the concept of normalization - the library system I learned about in a basic access / database class years ago sticks in my mind - tables for members, books and a table that has a pointer to the users, books they have out and due date.  a user only appears 1 time in the member table

I don't think this affords itself much to normalization.  picture some long legal form - just lots of fill in the blanks.  you want to store the data of the fill in the blanks  how would you handle it if there's more than 255 fill in the blanks?

thanks!

<<flat file >>

 That's where you made the mistake.

<<I don't think this affords itself much to normalization.  picture some long legal form - just lots of fill in the blanks.  you want to store the data of the fill in the blanks  how would you handle it if there's more than 255 fill in the blanks?>>

  You have to normalize, if you don't your just not storing the data efficently.  You'll also have problems with SQL.  For example, answer the question: "How many printers are there at each client?"

  With your setup, you'd need to loop through every record, look at each printer field and see if it is filled or not, rather then simply counting records with a SQL statement.

  Better yet, say you have 10 printer fields, you setup all your forms, reports, etc and then some client comes along that has 11?  Your whole design needs to change.  Done correctly, it would simply be adding a record to a table.  

  Take a look at the schema below, which I did for an old question similar to yours.

JimD.

tblAssets - One record per asset
AssetID
Description
EmployeeID
VendorID
AssetCategory
DepartmentID
DateAcquired
DateSold
NextSchedMaint
StatusID
PurchasePrice
DepreciationMethod
DepreciableLife
SalvageValue
Currentvalue
Comments
Description
Staff
(plus another 16 entries which fields are Yes/No)

tblAssetAttributes - One record per asset per attribute
AssetID - PK1
AttributeID - PK2
Value

tblAttributes - One record per allowed attribute type
AttributeID - PK
AttributeClassID - FK
Description

tblAttributeClasses - One record per attribte class type.
AttributeClassID - PK
Description


Here's the way it might look:

tblAttributeClasses
1  CPU
2  MEMORY
3  DISK
4  NETWORK

tblAttributes
AttributeID/AttributeClassID/Description

1       1     Pentium
2       1     Pentium II
3       1     Pentium III
4       2     RAM SIMM
5       2     EDO DIMM
6       2     SDRAM DIMM
7       3     Conner Model XYZ 13.2 GB
8       3     Seagate ST2120A
9       4     Intel 10/100 Pro
10      4     3Com 905c


tblAssets
AssetID   Description
1         JIM's PC
2         Ron's PC

Now to tie it all together:
tblAssetAttributes
AssetID/AttributeID/Value

1         3          500
1         6          512
1         7          
1         9          

2         1          66
2         4          16
2         8

  Poor Ron, he really needs a new PC<g>.

  With this design, you can now track anything (including software) against each asset without changing the design of your database.

  Note that you could easily add an Asset Category to group the assets (Computer, printer, WAP, or whatever) and include fields for model, serial number, product key, etc.

JimD

Thanks for the thoughts. I am weak at linking up tables / forms / queries (I understand the concept of relations, all the properties each field has in a table and form, etc... but pulling it all together / making a combo box, etc.  is where I am weak.
 
Do you have a sample of what you wrote in your reply you could send me?  Or how much would having you rough this out cost?  Once the framework is there, I can add the 15 other fields in addition to the first 5 on a form, etc...
 
and I am thinking that you get a better looking result by exporting to word docs I have already made.  Can access reports really look nice / as easy as word to update / add / remove things?
 
I have to get a better book than the one I am trying to read to get the basics of queries, forms and reports.  I think I understand tables and the properties about the fields, etc.  But am weak on the rest of it.  Any recommendations?

Of course your other choice is to break down and buy something.  There are a multitude of product out there for asset tracking.

JimD

it's not just assett tracking though.  it's all types of documentation about the (small business) network.  I haven't seen anything that does that.  There's some books I've seen for the smb consultant and all make it sound like homebrew forms, let alone automate it (as much as you would think an IT consultant would have a computer based process! : )

a page about the router, a page about the software on the server, a page about the internet provider, etc.  and jumping between each seemed like a pain... so make 1 long record.  yes, as you mention - the 11th printer will mess things up.  as would the xth user and yth shared folder... but for small businesses I am dealing with x > the number of items they are dealing with most times.  when I get into the bigger networks I'll be able to pop for $$ and do it 'right'...

I'm going to try this in excel : (

Just a note, Excel also has the 255 column limit

Yeah, but they have many more rows.  I will use a column for each client and so each client will have loads of rows of data?

>Yeah, but they have many more rows.

How?

Access has an infinite allowable number of rows (only limited to the total size of the database = 2Gb in 2000+), where as Excel is locked into a maximum of just over 65,500.

I'd seriously considering thinking about using Jim's advice here: http:#17372315

>but pulling it all together / making a combo box, etc.  is where I am weak.

We all started somewhere :P.

Although using Excel now seems like it will be the easiest way, in the end it may not be....

Dave