CODEWITHSUNDEEP
ms-access
Gale
Gale

Reputation: 1

Relational Links in Access tables

I have an Access database that contains 13 tables. The data in the tables are numbers. I would like to be able to relate specific fields in the tables and sum the totals for all of the tables together. For instance, I have fields for patient information completed in several of the tables the number in the field is the number of patients who completed the form. Each table represents a different facility. The total would represent the number of patients from all of the facilities who completed the form so that I have a grand total.

I'm not sure about how to go about linking the tables. Thanks, Gale

Upvotes: 0

Views: 28

Answers (1)

June7
June7

Reputation: 21379

These tables do not have relationships - they have the same kind of data, not related data (such as relating Customers to Orders). This is not correct data structure. Should not have a separate table for each facility. Should be one table with another field to ID the facility. Otherwise, UNION query these tables into a single dataset should be in first place.

SELECT ID, F1, F2, F3, "Fac01" AS Facility FROM Table1
UNION SELECT ID, F1, F2, F3, "Fac02" FROM Table2
...
UNION SELECT ID, F1, F2, F3, "Fac13" FROM Table13;

Then use that dataset in subsequent queries, such as GROUP BY or CROSSTAB.

Upvotes: 1

Related Questions