You could create a One to One realtionship between two tables to fix the problem. But I bet if you Normalize your data your table won't have as many fields.
In earlier versions of Access you just go to Access Help (not VBA Help) and ask about "specifications". In my experience, the number of fields you seem to require indicate there is probably a normalization problem.
I have just started adding fields to a new table for a quotation tool and there are lots of fields - i think i have hit some limit - as it comes up with an error when i go to save the table.
You definitely have a design problem. Access tables should be narrow and long, not short and wide. This is a common issue with people who have good experience of spreadsheets, but limited experience of relational databases. Now that word relational is the hint, instead of lots of columns in your table, you need to create rows in a related table. This has two advantages. Firstly you can have as many rows as you need, no limit. Secondly, you only use a row if you need it - with columns, it's there whether it has any data or not.
Now this process of moving from many columns to related rows is called normalisation (unless you're an american speaker in which case it's normalization). There's information in these forums about it, but even more on the net. You need to do the reading before you get into a pickle, because un-normalised designs make life so much harder as the application develops. Fix it now!
So by splitting the fields relivant to each page of teh quote system into seperate tables with the first field containing the Quote ID and a seperate table holding the quote id and customer data would be better - I wont claim best at this point.
My concern will be when i go to make a tabbed page for imputting the data how to go about attaching multiple tables to a single parent form.
Have you read up on normalization yet? Many have suggested it, but you haven't mentioned if you have. Do it. Your concerns about "multiple tables" are null and void after you get normalization. An ID (primary key) is an ID is an ID, regardless of where or how it's referenced.
Neil has a good point in that, usually (but not always), Access tables are tall and narrow, not short and fat. This means that Access tables are best with a few fields (1-20 or so) and a lot of records. That's "tall and narrow", visually. If your tables have more than 20 or so fields, there had better be a need (and there can be -- I have tables with 100 fields sometimes). Usually, though, you can separate things out into components.
For example, on a job application, you're usually asked the basics (name, address, other personal info), education background, job history, skills, and references. You can theoretically put all that in one field, but that's very unwise. Separate it into the components listed (basics, education, etc.) and assign an ID to it. Maintenance and portability become much easier.
That example may not apply to your situation, but you know your data better than us. You've already separated the data in to tabs, so there are your starting separation points. If you have any data that is repeated on each tab, that data belongs in its own table.
The idea is that you don't want to repeat the same data over and over again. For example, if you are storing "United States of America" somewhere, then you assign that a value and refer to that value.
Code:
CountryID CountryName
1 United States of America
2 Non-Denominational PC Country Listing Here
. .
. .
That way, you don't clutter your data with the same thing over and over. This is key in that if you store "United States of America" all over the place, the second it changes, you have to change it everywhere it's used. Using a lookup, you change it once and it's applied everywhere it's used.
That is (first level) normalization, and that, I believe, is what you are not quite getting yet.
Aagh gentleman yes i do that with AgentID's, Advertising Sources, Product Codes, and many others..
My Problem is I am making a sales quotation system.
The table holds the details of the Quote itself - I have to hold the sale price as at time of quotation as prices can change and the data will be exported back to the head office to a master Quotation System for Accounts to be able to see all sales reps quotes easily.
The Table I am looking at making holds,things like...
AgentQuoteID
Customer Ref Number - this will link to the Customer Table with their details
Widget#1Type
Widget#1Qty
Widget#1Colour
Widget#1Size
Widget#1Cost
Widget#1Installation
Widget#2Type
Widget#2Qty
Widget#3Colour
Widget#4Size
Widget#5Cost
Widget#6Installation
Gadget#1Installation
Gadget#1Qty
Gadget#1Cost
Gadget#2Installation
Gadget#2Qty
Gadget#2Cost
.
.
.
the problem I have is that there are over 255 of these details and 90% of teh time all of them will be used.
Table - tblQuoteDetails
QuoteDetailID - Autonumber (PK)
QuoteID - Long Integer (FK)
ItemID - Long Integer (FK)
ItemCost - Double
ItemQty - Integer
ItemInstallation - Whatever datatype it is
ColorID - (FK) only filled in if item has a color
Table - tblItems
ItemID - Autonumber (PK)
ItemDescription - Text
ItemTypeID - Long Integer (FK)
Table - tblItemType
ItemTypeID - Autonumber (PK)
ItemTypeDesc - Text
Table - tblColors
ColorID - Autonumber (PK)
ColorDescription - Text
Hopefully you get the idea with that sample structure.
If you are talking about Bob's excellent response then your MainForm would be bound to a query of the tblQuote table and there would be a SubForm on the MainForm in Continuous Form mode and bound to a query of the tblQuoteDetails table with the QuoteID as the LinkChild/MasterField. FK = ForeignKey
I am working on a database to monitor childhood obesity and for each referral we need to store about 600 items of information, i.e diet, activity, eating hablits etc this has to be recorded 4 times over a 12 month period. So you do get exceptions to long and thin.
