Using Multiple INNER JOIN to provide conversion values for different channels

[cms370]

All,

This could be another simple question...here we go:

'Channel_Data_Table' contains 2 variable channels each with their corresponding channel ID column.

Channel_Data_Table:
Channel A, Channel A_ID, Channel B, Channel B_ID

'Conversion Table' is used to store channel ID's and their associated conversion factors.

Conversion Table:
Channel_ID, Conversion Factor

I need to create a query that will take the value under the Channel field, utilize the corresponding Channel_ID, reference the Conversion Table, multiply the Conversion Factor by the Channel Information and provide this as an output.

How is this done? Do I have to perform multiple INNER JOINs? In reality, I actually have 15 channels that I need to do this with, but for discussion purposes, I wanted to keep it simple at 2.

Any tips or suggestions would be helpful. Thanks.

 

[MSAccessRookie]

All,

This could be another simple question...here we go:

'Channel_Data_Table' contains 2 variable channels each with their corresponding channel ID column.

Channel_Data_Table:
Channel A, Channel A_ID, Channel B, Channel B_ID

'Conversion Table' is used to store channel ID's and their associated conversion factors.

Conversion Table:
Channel_ID, Conversion Factor

I need to create a query that will take the value under the Channel field, utilize the corresponding Channel_ID, reference the Conversion Table, multiply the Conversion Factor by the Channel Information and provide this as an output.

How is this done? Do I have to perform multiple INNER JOINs? In reality, I actually have 15 channels that I need to do this with, but for discussion purposes, I wanted to keep it simple at 2.

Any tips or suggestions would be helpful. Thanks.

I do not believe that your Table Design is properly normalized, and therefore your task will be more difficult than it needs to be. Try reading up on Normalization.

In the mean time, consider a single table approach with the ChannelID as the Primary Key. Something like the following would be a good start:

 [FONT=Courier New][B]tblChannelData[/B][/FONT]
[INDENT][INDENT][FONT=Courier New][B]ChannelID                  AutoNumber[/B][/FONT]
[FONT=Courier New][B]ChannelDescription         Text[/B][/FONT]
[FONT=Courier New][B]ChannelConversionFactor    Number[/B][/FONT]

[/INDENT][/INDENT]

 

[cms370]

Thanks for the information. I read through a Microsoft Support article on normalization. To be honest, however, I think that the approach I am taking adheres to the 3 levels of normal form...although I probably need to digest this a bit more.

The reason I am including the 'Channel A_ID', 'Channel B_ID', ... with each record is that the the channels can change, without any prior warning. The file that is read into the database contains the channel ID that is being sampled at that time step and that ID is being placed into the channel ID field. So now I have knowledge of what channel is being read in and the ID that corresponds to the appropriate conversion factor for that channel.

I would like the query to go through and select from the channels that I specify (I will supply the channel ID's that I am currently interested in for that query run) along with a given date range. I would then like the query to select the records that match up for the channels that I have provided, use the conversion table (which has every possible channel available) to look up the conversion factor, apply the conversion factor and then produce the output table with the converted channel info for the given date range.

Once again, I really have no control over the channel data that is coming in for each of the available 15 channels. It is totally up to the local set up. I need to be flexible enough to accept the 'raw' data from each channel and also know what the data describes (hence, channel ID).

I suppose another possibility is to do the following(??):

"SELECT [Channel_Data_Table].[Channel A]*" & Dlookup("[Conversion Factor], "[Conversion Table]", "[Conversion_Factor_ID] = [Channel A_ID]") & ";"

 

[cms370]

Ok...after a bit more thought, and consulting with some others...I have moved all of the 'Channel A' , 'Channel B' , ... into one column called 'Channel'. So instead of having 15 channels across, I now just have one channel which will change with each record that is entered.

I think this will be a much easier way of doing things.

Im guessing that this is what you were trying to point out yesterday MSRookie...thx.

 

[MSAccessRookie]

Ok...after a bit more thought, and consulting with some others...I have moved all of the 'Channel A' , 'Channel B' , ... into one column called 'Channel'. So instead of having 15 channels across, I now just have one channel which will change with each record that is entered.

I think this will be a much easier way of doing things.

Im guessing that this is what you were trying to point out yesterday MSRookie...thx.

Not to worry. I may not always get my points across the first time, but they often indicate a step in the right direction. Glad to hear that you are able to move forward.

 

[cms370]

Ok, things are working well with this approach. My next issue is that I need to take the query and split it back out into separate columns so that it can be exported as a text file.

Right nows I have the following for a given query 'Data Select Query':

..., Channel Name, Channel Value, ...
..., ChannelA, 100, ...
..., ChannelB, 23.4, ...
..., ChannelA, 912.3, ...
..., ChannelC, 10293.74, ...
..., ChannelB, 35, ...
..., ChannelC, 8, ...
..., ..., ...,...

But I would like to get it into the following:

..., ChannelA, ChannelB, ChannelC, ...
..., 100, 23.4, 10293.74, ...
..., 912.3, 35, 8, ...

Are there any built in commands to do this? My line of thinking is to create a Make Table query that will create a new table, based on this data and iterate through each line to determine if there is a new channel that needs to be added, and then populate with the channel value.

Any suggestions? Thanks.