Two tables in one form

[Tom Servo]

Hello all,

I am extremely new to Access. I had help from someone in my office but they have left. I work in an engineering office. We are trying to use access as a data entry for field reports. The reports are taken from Excel. I have copied the reports we used in Excel and inserted them into Access so they will look the exact same. For example, one of the forms tests the concrete strength over time. The goal of this report is to be able to take all the final strengths from multiple tests and create another report. This isn't the issue, I just want you to know what we are using Access for. I know this isn't the typical use for Access but it should work.

My problem. One of the reports has over 375 data points. I know a table has 255 fields. So, I need two. We will be inserting the data into a form, have it save into the table and publish the final report in a report.

My question: How do I insert two tables into one form? The two tables will share a test location field.

Please help in any way you can. Remember though, I am super green when it comes to Access. Let me know if you have any further questions.

THANK YOU!!

 

[millarjeremy]

create a query with with both the tables, joined by the test location field.
Also have a really good review of why all that data needs to sit in 1 table. it can be spread across multiple tables and brought together with qry. Have a look at the northwind dba for a general read, it will help.
Just did this for a client who want to record some 400 entry points for each weld on a gas site.......

 

[Tom Servo]

Thanks Jeremy

I made the query using the test location at the relationship. Another problem has come up. When I insert more than 26 fields from table two, A little green tab comes up in the top of the box. What does this mean?

 

[rzw0wr]

Click the tab.
What does it say?

Dale

 

[Pat Hartman]

Queries and tables are pretty much interchangeable to Access so if tables are limited to 255, so are queries.

A better solution is to normalize the schema. Without knowing what the datapoints represent, I can't make a definitive statement as to how to do it. You may be looking at repeating groups (the same data that occurs multiple times such as 12 months) or the datapoints may appear to be unique. But when you are looking at an accounting package, individual "expenses" also look like they are unique - electric, gas, lawn, garbage, etc. But you would NEVER see a table set up with columns of these names. You would always see them as individual rows in a "child" table. So think about something like:
tblDataPoints:
ProjectID (autonumber primary key)
TestedDT
TestDesc
PointValue