CODEWITHSUNDEEP
sqlms-access
user3895957
user3895957

Reputation:

How to get the name of a table in MS access?

I have one table that has a list of Facilities, then I have many other tables, one for each Facility with the equipment that is there.

I am trying to make a query that can bring all of these tables together. The problem is that equipment names can be repeated from Facility to Facility so I need to get the Facility name to be associated with the equipment to have unique records. I do not have the ability to edit these equipment tables so I can't just add a column to the table and the only place that the Facility name is referenced in the Equipment Tables is in the title of the Table itself.

Is there any way that I can link the Facility Table records to the names of the Equipment Tables?

The Tables are similar to this:

Facilities

EI-456

EI-497

Hopefully that makes it a little more clear.

Upvotes: 0

Views: 87

Answers (2)

Brad Knight
Brad Knight

Reputation: 70

What you're asking for can be done dynamically through code (vba, etc) using your row record value to populate column.name in a query string.

I'd run an initial query to determine the facility name and then do:

Dim sillyString as String

sillyString = .row(?).item("column") = "EI-456"

strSQL = "SELECT * FROM " & sillyString & "WHERE ..."

Upvotes: 0

iDevlop
iDevlop

Reputation: 25252

That looks like terrible design. You should have:

  • a Facilities table
  • an Equipments table (optional, depending on your needs)
  • a Locations table (or Assignment or...) where you just store the FacilityId and the EquipmentId.

I suggest that you read a bit about database normalisation. That will really pay off in the long run.

Upvotes: 3

Related Questions