Multi-Table Report or Form

[AccessGirl5050]

I have 78 different classes in Excel that are linked into Access 2013 as individual tables. Each Table has Student name, class name, instructor name, certification #, Class date, and expiration date.

I need to create a Report or Form that I can enter a Students name, and it will pull up all classes that the student has taken through all 78 tables (aka classes).

I would like a blank form that I can go into at any given time and pull up any given student upon demand, and generate results. But the concept feels like a report, not a form.

Please guide me in the right direction.

[Seth Schrock]

You really need to look at Database Normalization and Table Structures. Because the maximum number of tables in a query is 32, you can't even create a UNION query to combine them all before running your search. What I would do is create a table with all the fields that you currently have plus one for the class name. You can then import all the data from your classes into the new table along with the class ID. You can then easily run a query with a WHERE clause to filter the results by class name, student name, instructor name, etc.

[AccessGirl5050]

I understand how that would work for what I have, however, the reason for the tables being linked from excel, is we hold several classes every week and we need to be able to search all of the updated information.

If I take the information and create a new table, it will not include current and future students that will be added by another person in the office.

[Seth Schrock]

This can be automated so that you can pull the current info each time. Obviously this would increase the amount of time it takes, but it would work. Another option would be to combine the Excel spreadsheets and have the class ID as another field. This would actually probably be the best option. Another option would be to ditch Excel and manage it completely within Access.

The only other option that I see as possible (however, not at all efficient) would be to search each table and insert the records found into a Results table. This would be searching 78 table individually and then copying the data and then viewing the data.

[AccessGirl5050]

Awesome! I will give it a try then with the option to be automated. Im ok with the time it takes, as long as the boss gets what she wants.

Each Excel document has about 1000+ records, so i wouldnt want them to all be in one document anywhere.

We are willing to train co workers to use Access, but they make a big deal, because anything new is scary :p

Thank you for your guidance, I will let you know if I run into troubles along the way!

[NeoPa]

Please may I add my advice here.

I am very experienced in both Access and Excel and I would say that sticking with Excel for this task is likely to lead you down an ever narrowing road until you get to the point where you simply can't proceed any further on your current path. At this point you'll need to start again from where you are now and give some serious consideration to exactly what your real constraints are.

I would suggest it would be wiser to do that at this point instead and find a solution that is viable going forwards.

What are your constraints?
What about the environment means that it cannot be worked on by multiple parties?

NB. While Excel may start to struggle at around 100,000 records if designed properly, Access typically handles many more records without batting the proverbial eyelid.

I can't tell you what your best solution is as I don't understand your environment clearly enough. Once you do though, you'll be in a better position to choose your way forward.

[AccessGirl5050]

In Excel, each class is designated a tab.
Each Tab is linked into Access as a table.

The boss wants the secretary to continue utilizing Excel to keep track of class details. The boss also wants Access to be a backup so she can run reports of her own. So Only the boss and myself need to be able to navigate Access.

Access is actually giving me issues, saying queries are too complex, and that the request exceeds the size limit.

The Excel Document is only 1600KB, and Access says certain requests is exceeding 2GB (?!!?) Not sure how that is even possible.

But I'm getting stuck, and boss s pushing for a deadline. Ugh.

[twinnyfo]

AccessGirl5050,

If you have a Front End and Back End, the user (i.e. the secretary) should be able to enter all the necessary data using an Access Form (which does not require advanced Access knowledge or skills).

Based on the descriptions I am seeing in your original Post and the discussion, moving to Access-only would be the "best" solution, but would require a bit of effort on your part to make it user-friendly and simple to use for those who have to navigate it.

I also understand deadlines....

But we are here to help in whatever ways we can.