72 Fields on a MsAccess 2007 form

[access2010]

We have 3 Excel Spreadsheets with 24 columns on each sheet.

Can we have 72 fields containing this data for ONE item on a Ms Access 2007 form using Tabs?

Thank you.
Crystal

 

[Minty]

There is a finite number of fields for a table, I believe it is 254. so you still have some head room.
However if you get close to or above 127 fields and try and do anything clever involving all of those fields, because of the way access works it starts to hit the 254 field limit as it creates internal system structures with the fields listed twice, and then gives up with obscure error messages.

The other point is the number of controls on a form, but that is a much higher limit somewhere around 750 or higher in later versions.

 

[The_Doc_Man]

There is one more "gotcha" to aware of. No matter whether you have 16 maximum-sized text fields (255 bytes each) or a huge number of currency and date/time and double-floating fields... there is also a record size limit. If I remember correctly, that limit is 4K bytes total for all fields combined in a single record (not counting MEMO fields, which in newer versions of Access is called LONG TEXT). Comparing to Excel fields, for numbers with fractions, that is probably either 4 (SINGLE float) or 8 (DOUBLE float) bytes per field in Access. DATE/TIME is also 8 bytes. LONG (integer) is 4 bytes. A Yes/No field is, if I recall this correctly, 2 bytes. TEXT (or in newer versions SHORT TEXT) is the number of bytes you allow as the maximum for that text string. Total up the allocation for each field. If your 72 fields don't exceed 4K bytes, this should work.

NOTE: If you are somehow tying those 3 sheets x 24 columns because each sheet has some common identifier so you can link the rows in some way, you would have redundant data in terms of the 2nd and 3rd sheet - data that could be removed if you merged the three rows to one record.

 

[LarryE]

What you are telling us is that each record (item) in your table has 72 different characteristics? Is that really true?

 

[Uncle Gizmo]

We have 3 Excel Spreadsheets with 24 columns on each sheet.

Can we have 72 fields containing this data for ONE item on a Ms Access 2007 form using Tabs?

Thank you.
Crystal

Hi Crystal,
Thanks so much for your question! From what you've described, it sounds like you’re looking to display the data from your three Excel spreadsheets in a single Access form, potentially using tabs to organize the fields. This could work well, and one approach we’re considering is to set up a form with three tabs, with each tab showing data from one of the spreadsheets.

Alternatively, we might use subforms in datasheet view on each tab to give you an experience similar to what you’re used to in Excel, but with the added power of Access for managing and analyzing your data.

Before we move forward with specific recommendations, though, we’d like to clarify a few details to ensure we’re on the right track:

- Are the rows in each of your Excel sheets related? Do they align with each other based on a unique identifier, like a product code, device ID, or some other key? In other words, does each row across the three spreadsheets represent the same item, with each sheet containing different fields for that item? - How do you plan to use this data in Access—will you mostly be viewing it, making edits, or generating reports? - Would you prefer to keep a similar structure to what you have in Excel, or are you looking for a more customized interface with Access?

Understanding how your data is structured and how you want to work with it will help us recommend the best solution for you.

Looking forward to hearing more about your setup so we can guide you better!

 

[XPS35]

Before you start thinking about forms, you first need to consider how you will store the data. That's the most important thing when setting up a database.
An important question here is how and whether the data in the spreadsheets are related to each other. We have no insight into that.

 

[cheekybuddha]

Hi Crystal,
Thanks so much for your question! From what you've described, it sounds like you’re looking to display the data from your three Excel spreadsheets in a single Access form, potentially using tabs to organize the fields. This could work well, and one approach we’re considering is to set up a form with three tabs, with each tab showing data from one of the spreadsheets.

Alternatively, we might use subforms in datasheet view on each tab to give you an experience similar to what you’re used to in Excel, but with the added power of Access for managing and analyzing your data.

Before we move forward with specific recommendations, though, we’d like to clarify a few details to ensure we’re on the right track:

- Are the rows in each of your Excel sheets related? Do they align with each other based on a unique identifier, like a product code, device ID, or some other key? In other words, does each row across the three spreadsheets represent the same item, with each sheet containing different fields for that item? - How do you plan to use this data in Access—will you mostly be viewing it, making edits, or generating reports? - Would you prefer to keep a similar structure to what you have in Excel, or are you looking for a more customized interface with Access?

Understanding how your data is structured and how you want to work with it will help us recommend the best solution for you.

Looking forward to hearing more about your setup so we can guide you better!

Is this written by ChatGPT?

 

[Uncle Gizmo]

Is this written by ChatGPT?

Yes, well spotted!

 

[mike60smart]

We have 3 Excel Spreadsheets with 24 columns on each sheet.

Can we have 72 fields containing this data for ONE item on a Ms Access 2007 form using Tabs?

Thank you.
Crystal

Hi Crystal
Are you able to upload a copy of the Excel file showing all 3 sheets?

 

[Uncle Gizmo]

Is this written by ChatGPT?

1 of 2
I deliberately posted exactly what chatty said without alteration. My goal here was to demonstrate what you can accomplish with ChatGPT for people that are not yet familiar with its use... Answering this particular question offered a reasonably simple and self-explanatory look at the process....

Regarding this question on {AWF} Access World forums:-

Question - 72 Fields on a MsAccess 2007 form

72 Fields on a MsAccess 2007 form

We have 3 Excel Spreadsheets with 24 columns on each sheet. Can we have 72 fields containing this data for ONE item on a Ms Access 2007 form using Tabs? Thank you. Crystal

www.access-programmers.co.uk

I was using the ChatGPT Agent - MS Access Copilot and in this exchange I am UG - uncle gizmo

UG Said:-
Crystal an original poster has just asked this question:-

We have 3 Excel Spreadsheets with 24 columns on each sheet.

Can we have 72 fields containing this data for ONE item on a Ms Access 2007 form using Tabs?

Thank you.
Crystal

UG Said:-
The question is very vague, very little information...

Reading between the lines I think she is asking if she can have a form with a Tab control with three tabs and on each Tab have 24 Fields.

That's my guess!

UG Said:-
What do you think my friend chatty?

ChatGPT said:
Hey there!
I totally agree with your interpretation. It sounds like Crystal is trying to have a form in MS Access that shows data from three Excel spreadsheets, with 24 fields (columns) on each sheet. And she wants to organize this on a single Access form by using tabs, with each tab containing 24 fields—so, 3 tabs for a total of 72 fields.

Here’s what I’m thinking:

Suggested Setup:

Tab Control: Add a Tab Control to the form. This control allows you to have multiple tabs in one form.
Tab 1: This will show the first 24 fields from the first spreadsheet.
Tab 2: This will show the second 24 fields from the second spreadsheet.
Tab 3: This will show the last 24 fields from the third spreadsheet.

You’ll need to ensure the tables or linked Excel files are properly designed, but it’s definitely feasible.

🛠 Steps to Create the Form with Tabs
Import or Link the Excel Data:
Either import the Excel sheets into Access tables or link to the spreadsheets. Ideally, each spreadsheet should have a table with 24 columns in Access.
Create a Query or Unified Table:
If all three sheets have data for one item (like different attributes of a single item), you’ll want to combine these tables into one using a query or a unified table.
Form Design:
Open a blank form and add a Tab Control from the toolbox.
Add three tabs—one for each spreadsheet's data.
In each tab, add text boxes and other controls as needed to display the 24 fields for that section.
Set Data Source:
Make sure each control on the tabs (like text boxes) is bound to the appropriate field from the query or table.

Note: This should be done in a well-structured database. If the data in each of these spreadsheets is very different, there might be a better approach (such as splitting into related tables). However, if the data is all related to a single item and just too large for one form page, using tabs is a good way to manage the complexity.

Does that align with what you were thinking? Let me know!

UG Said:-
I'm thinking how a subform on each tab displaying the data from each Excel sheet in data sheet mode then she will have practically exactly the same as she has already got!

ChatGPT said:
Ah, I see where you're going with this—great idea!

Using a subform on each tab in Datasheet View would give Crystal a nearly identical experience to what she’s used to in Excel, but within the more powerful framework of Access. Each subform could represent one of the Excel sheets, and she'd have the ability to view and edit the data as she does now, while leveraging Access's relational database features.

🛠 Here’s a step-by-step approach for this:
Steps to Implement Subforms on Tabs

Import or Link the Excel Spreadsheets:

Either import the three Excel sheets into separate tables in Access or link them directly, so the data remains in sync with Excel. You’ll have three Access tables, each with 24 columns.

Create a Form with a Tab Control:

Add a Tab Control to your form with three tabs—one for each spreadsheet.
Each tab will have a subform that shows the relevant data.

Create a Subform for Each Excel Sheet:

Create three separate subforms based on the data in each of the imported or linked Excel tables.
Set the subforms to display in Datasheet View (in the subform's properties, go to the "Default View" and set it to "Datasheet").

Add Each Subform to the Corresponding Tab:

On Tab 1, add the subform for the first Excel table (spreadsheet 1).
On Tab 2, add the subform for the second Excel table (spreadsheet 2).
On Tab 3, add the subform for the third Excel table (spreadsheet 3).

Bind Each Subform to the Correct Data Source:

Make sure that each subform is correctly linked to the corresponding Excel table (or linked table) as its data source.
The Result:
Tab 1: Displays data from Excel Sheet 1 (24 columns) in datasheet view.
Tab 2: Displays data from Excel Sheet 2 (24 columns) in datasheet view.
Tab 3: Displays data from Excel Sheet 3 (24 columns) in datasheet view.

Advantages of This Setup:

It closely mimics the layout and functionality Crystal is familiar with in Excel.
She’ll have the ability to filter, sort, and edit data just like in Excel but within the Access environment.
It’s much easier to manage multiple tables and forms in Access once the data is properly structured.

Do you think this solution will make it more intuitive for Crystal, since it'll feel very "Excel-like" but with the added benefits of Access? Let me know if you'd like more detailed instructions on any of the steps!

UG Said:-
Could you write a reply to Crystal, I wouldn't answer her questions specifically just give a very broad overview of what we think is the best approach. And then try and Winkle out of her more information what should we ask her next?

ChatGPT said:
Absolutely, here’s a polite and broad response you could use to guide Crystal while encouraging her to provide more information. The aim is to suggest a practical approach without getting too technical, while also nudging her to clarify her requirements:

ChatGPT - Reply to Crystal:

Hi Crystal,

Thanks for your question! From what you’ve shared, it sounds like you're looking to organize the data from your three Excel spreadsheets into a single form in Access using tabs. We think an effective approach might be to create a form with three tabs, where each tab contains the data from one of the spreadsheets. This setup can help you view and manage all 72 fields easily.

Another option we’re considering is using subforms in datasheet view on each tab, which would make your form feel quite similar to how you’re working with the data in Excel now, while giving you the added benefits of Access. This way, each tab could represent one of your spreadsheets, and you can interact with the data as you would in Excel.

That being said, before diving deeper into the best solution, it would be helpful to know a bit more about how the data in your Excel sheets is structured and how you’d like to use it in Access:

- Are the 3 spreadsheets related to the same item or data set (e.g., different aspects of the same records), or do they represent separate items? - How do you plan to work with the data in Access—will you mainly be viewing it, editing it, or using it for reporting and analysis? - Would you prefer to maintain the look and feel of Excel, or are you looking for a more customized interface?

Let us know a bit more about your needs, and we’ll be happy to recommend the best way forward!

Looking forward to hearing from you!

Reasoning:
This reply introduces the general concept of tabs and subforms, keeping it simple and understandable.
The three questions are designed to extract more information without being overwhelming:
To clarify if the spreadsheets are interconnected (this will help determine if they should be linked or treated as separate entities).
To understand how she plans to use the data, which will influence the form design.
To gauge her preference for either an Excel-like experience or something more tailored to Access.

Does this hit the mark? Let me know if you'd like to tweak anything!

1 of 2

 

[Uncle Gizmo]

2 of 2

UG Said:-
Ah, but we have missed out an important question! We need to know if the rows in each XL sheet in the three different shades if the rows a line if they have a unique reference and that the three sheets show each row continuously for the 72 items that match one product or device or whatever you want to call the row? Can you spell that out sensibly?

ChatGPT said:
Ah, great catch! You're absolutely right—we definitely need to find out whether the rows in the three spreadsheets align with each other based on some unique identifier. This will determine how we manage the relationships between the data across the tabs.

Here’s an updated version of the reply that includes this critical question:

ChatGPT - Revised Reply to Crystal:

Hi Crystal,

Thanks so much for your question! From what you've described, it sounds like you’re looking to display the data from your three Excel spreadsheets in a single Access form, potentially using tabs to organize the fields. This could work well, and one approach we’re considering is to set up a form with three tabs, with each tab showing data from one of the spreadsheets.

Alternatively, we might use subforms in datasheet view on each tab to give you an experience similar to what you’re used to in Excel, but with the added power of Access for managing and analyzing your data.

Before we move forward with specific recommendations, though, we’d like to clarify a few details to ensure we’re on the right track:

- Are the rows in each of your Excel sheets related? Do they align with each other based on a unique identifier, like a product code, device ID, or some other key? In other words, does each row across the three spreadsheets represent the same item, with each sheet containing different fields for that item? - How do you plan to use this data in Access—will you mostly be viewing it, making edits, or generating reports? - Would you prefer to keep a similar structure to what you have in Excel, or are you looking for a more customized interface with Access?

Understanding how your data is structured and how you want to work with it will help us recommend the best solution for you.

Looking forward to hearing more about your setup so we can guide you better!

Key Additions:
The first new question explicitly asks if the rows align across the spreadsheets, based on a unique identifier. This is critical for determining whether to link tables or treat the data separately.
The rest of the reply is unchanged but now flows naturally into that vital inquiry.

Does this feel more complete now? It ensures we don’t move forward until we fully understand the relationship between the rows.

2 of 2

 

[Uncle Gizmo]

UG Said:-
Ah, but we have missed out an important question!

An important point to Note, is that ChatGPT does not have enough experience to answer some questions on its own. In fact, it's wrong to talk in terms of "experience" when referring to ChatGPT...

 

[plog]

Access is not Excel With Forms.

You need to properly set up your tables and fields. That process is called data normalization:

Database normalization - Wikipedia

en.wikipedia.org

Give that link a read, find a few tutorials then apply what you learn to your data. Then, you can build the queries and reports you need to get your data out of the database like you need. Finally, the last step is building the forms you need to interact with your data. I understand forms are the sexy part, but they are the last thing to consider when building a database.

 

[The_Doc_Man]

With no disrespect to Access2010 as a member, they have a record of having difficulty with some of our more advanced suggestions. I will not try to downplay anyone's importance, but sometimes we have to be careful in the complexity we suggest for this particular user. Some users need more detailed attention than others.

 

[AccessBlaster]

The biggest problem is you have 72 fields on an Access form, it sounds horrendous and unusable.

 

[access2010]

There is a finite number of fields for a table, I believe it is 254. so you still have some head room.
However if you get close to or above 127 fields and try and do anything clever involving all of those fields, because of the way access works it starts to hit the 254 field limit as it creates internal system structures with the fields listed twice, and then gives up with obscure error messages.

The other point is the number of controls on a form, but that is a much higher limit somewhere around 750 or higher in later versions.

Thank you Minty for your information, at the present time our Tab Forms have a total of 56 fields.
Nicole

 

[access2010]

There is one more "gotcha" to aware of. No matter whether you have 16 maximum-sized text fields (255 bytes each) or a huge number of currency and date/time and double-floating fields... there is also a record size limit. If I remember correctly, that limit is 4K bytes total for all fields combined in a single record (not counting MEMO fields, which in newer versions of Access is called LONG TEXT). Comparing to Excel fields, for numbers with fractions, that is probably either 4 (SINGLE float) or 8 (DOUBLE float) bytes per field in Access. DATE/TIME is also 8 bytes. LONG (integer) is 4 bytes. A Yes/No field is, if I recall this correctly, 2 bytes. TEXT (or in newer versions SHORT TEXT) is the number of bytes you allow as the maximum for that text string. Total up the allocation for each field. If your 72 fields don't exceed 4K bytes, this should work.

NOTE: If you are somehow tying those 3 sheets x 24 columns because each sheet has some common identifier so you can link the rows in some way, you would have redundant data in terms of the 2nd and 3rd sheet - data that could be removed if you merged the three rows to one record.

Thank you, Doc Man for your comments. With the current tests that we have run with our 56 Fields everything seem to be working.
Nicole

 

[access2010]

What you are telling us is that each record (item) in your table has 72 different characteristics? Is that really true?

Thank you LarryE, for your question. Yes, we are preparing for the possibility of 72 different specific comparisons.
Nicole

 

[access2010]

Hi Crystal,
Thanks so much for your question! From what you've described, it sounds like you’re looking to display the data from your three Excel spreadsheets in a single Access form, potentially using tabs to organize the fields. This could work well, and one approach we’re considering is to set up a form with three tabs, with each tab showing data from one of the spreadsheets.

Alternatively, we might use subforms in datasheet view on each tab to give you an experience similar to what you’re used to in Excel, but with the added power of Access for managing and analyzing your data.

Before we move forward with specific recommendations, though, we’d like to clarify a few details to ensure we’re on the right track:

- Are the rows in each of your Excel sheets related? Do they align with each other based on a unique identifier, like a product code, device ID, or some other key? In other words, does each row across the three spreadsheets represent the same item, with each sheet containing different fields for that item? - How do you plan to use this data in Access—will you mostly be viewing it, making edits, or generating reports? - Would you prefer to keep a similar structure to what you have in Excel, or are you looking for a more customized interface with Access?

Understanding how your data is structured and how you want to work with it will help us recommend the best solution for you.

Looking forward to hearing more about your setup so we can guide you better!

Thank You Uncle Gizmo.

I thought that we could use 3 tabs (Sub Forms) to separate comparison data for each group, but have now changed the layout to 5 Tabs on the sub form to analyze / display similar data groups.

Yes each of the Rows is Related to a unique ID / Key Field.

Yes, each row across the three spreadsheets represents the same item.

The Data in the Access forms will be used for viewing purposes at this present time and MAY be used for reports later.

No Editing on the form is planned at this time.

The reason we are looking at Custom Access Forms is that most of the Staff are not software literate and my opinion I feel that the forms will be easier to read.

I hope to import the data from our Excel Spreadsheets, Into a MsAccess Table and have the Form display the data for each individual item.
Nicole

 

[access2010]

Before you start thinking about forms, you first need to consider how you will store the data. That's the most important thing when setting up a database.
An important question here is how and whether the data in the spreadsheets are related to each other. We have no insight into that.

Thank you XPS35 for your comment.

The forms that I have created will be used to display the data for each individual item.

Nicole