72 Fields on a MsAccess 2007 form (3 Viewers)

[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?