Update a Linking Table

[MattGreer]

I'm not sure I'm using the right terminology here, so I'll try to explain. Linking table is a table that resolves a many-to-many relationship. I'm also using the abbreviation "CPK" for Composite Primary Key. Ok, on to the discussion!

I have three tables. One called "Locations", a second called "InstrumentLocations" and a third called "Instruments". Here's the table setups:

[tt]
Locations
---------
LocationName (PK - short text)

InstrumentLocations
-------------------
LocationName CPK (FK to the Locations Table)
InstrumentID CPK (FK to the Instruments Table)

Instruments
-----------
InstrumentID (PK)
InstrumentName
InstrumentType
[/tt]

So the problem that I'm having is that an Instrument can report more than one type of data, a Pressure or a Temperature, for example. So the combination of InstrumentName and InstrumentType forms a unique Index which is used in a Data collection table. An instrument can also appear at more than one Location, thus the many to many relationship.

I have an Excel file that relates InstrumentName to LocationName, one in each column. I can import this into a temporary table in Access 2013. How do I write a query that will update the InstrumentLocations table with this information, thus linking the Instruments table to the Locations table?

Sorry if this is confusing, I'm definitely no expert!

Thank you!!

Thanks!!


Matt

 

[dhookom]

Matt,
You have done a nice job of describing the tables but not the Excel file.

Also, are you updating or appending to InstrumentLocations?

Duane
Hook'D on Access
MS Access MVP

 

[MattGreer]

Right now, InstrumentLocations is empty, so I would be appending to it. I've already got the locations filled out (typed in manually, only 8 locations) and I've appended about 20 Instruments to the Instruments table just to get started. It's pretty easy to get those in there.

The Excel file is a pain. There are six sheet tabs in it, each reporting a different group of sensors, but within a sheet tab they're all the same type. So on Sheet1, they're all pressure transmitters, but on Sheet4, they're coriolis which report Temperature, Pressure, and Flow.

So in the Excel file, for example, the information comes in this format one one tab:

[tt]
Device Location Date Value
----------------------------------------------------------
1234_SEPXYZVE1236 LOCATION2 10/22/2013 2:34:03 AM 156
4231_SEP348901423 LOCATION1 10/22/2013 2:34:18 AM 151
[/tt]

and on another tab it's like this:

[tt]
Device Location Date Value1 Value2 Value3
----------------------------------------------------------------------------
1234_SEPXYZVE1234 LOCATION2 10/22/2013 2:32:03 AM 156 45.6 34516
5617_SEP5784EQVES LOCATION7 10/22/2013 2:39:06 AM 145 52.3 56123
[/tt]

This only really needs to be done once as the Instruments and Locations are static. Any future additions I can do manually, but for this initial setup it would save quite a bit of time if I could import into that linking table.

So glad you replied. Let me know if there's any other information you need.

Thanks!!


Matt

 

[MattGreer]

I figured this out by backing my way into it from a simpler query. I had an import table (simply dumped from Excel) that had the Location and InstrumentName already it. I ended up with a query that looked like this:
[tt]
INSERT INTO tblInstrumentLocations ( InstrumentID, LocName )
SELECT tblInstruments.InstrumentID, tblLocations.LocName
FROM (tblSheet1Import INNER JOIN tblLocations ON tblSheet1Import.[Location Name] = tblLocations.LocName) INNER JOIN tblInstruments ON tblSheet1Import.InstrumentName= tblInstruments.InstName
WHERE (((tblSheet1Import.Device)=[tblInstruments].[InstName]));
[/tt]

The indexing rules/key rules on the tblInstrumentLocations prevents duplicate entries. I'm sure this is not very elegant but it will get me started for now.

Is it normal for database professionals to try to whole-hog import records, knowing/expecting that many will be rejected due to the rules placed on the receiving table?

Thanks!!


Matt

 

[dhookom]

I typically append to a temporary table with very few restrictions. I then create queries based on the imported table.

Duane
Hook'D on Access
MS Access MVP