Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839

    Query to find and place data from 1 table to another


    I am trying to convert 1 table's data into another. I am trying to move the radio network information by platform to radio to channel. There will be multiple radios/platforms.

    I have put a table named desired outcome of what I am trying to do.

    I think I need to filter by

    Unit, then by section, then by role, then by platform, then by radio, then by channel.

    But to assign the correct net to the correct channel on the correct platform in the right section for the right role, I need to use the Platform ID to find the radios assigned to the platform. This gives me the radio parent ID, thus in turn gives me the channel ID to assign the network from the other table (Channel 1 would be the lesser of the equipment ID, IF, there is a channel 2). Do I use Dlookup? How would I use the Dlookup? It looks like I need to do dlookup on a dlookup on a dlookup? Would pass through queries enable this also? I am not familiar with them.

    Assistance is appreciated. The start of the db is in the ZIP.

    Thanks
    T
    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Data makes no sense. Platform id's in NBOI are not in Data. DesiredOutcome shows values in Network and Waveforms fields. Query shows source of these values as Data table but those records do not have any values in those fields. None of the fields in DesiredOutcome derive from NBOI table.

    Neither source table has a primary key defined. I cannot determine what if any relationship these tables have.

    Advise no spaces nor punctuation/special characters (underscore only exception) in naming convention.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    June7,
    The relationship would be from the platforms the unit, paragraph number, Section, then role. I know its not a norm and accepted naming convention, but I didn't develop those tables.

    The Desired outcome is the Networks and Waveforms Fields being filled in.

    I'm getting duplicate entries in the TestSINC1 query. now - most likely because the NBOI table has 2 rows in it (at the moment.

    Reading the NBOI Table:


    NBOI.Netbase = Data.TOE Title
    NBOI.para = [Data.para #]
    NBOI.[Paragraph Description] = Data.[Para Desc]

    Unit - (Could use UIC also as a Filter/pivot point)
    ......Para
    ........ Para Desc
    ............Platform (Platform ID-sets base for radios)
    ...............Radio Mount (uses parent_equipment_ID to link to Platform and uses LIN to identify the Radio/transceiver LIN, and amount of channels per radio.
    ....................Radio/Transceiver (uses parent_equipment_ID to link to the radio mount, lower equipment_item_ID is channel 1.

    NBOI.[VRC-92F SINC long long]: number of that radio type on the platform/[VRC-90F SINCGARS long: number of that radio type on the platform

    Example:

    Unit1
    .....0101
    ........COMMAND GROUP
    ..........EXECUTIVE OFFICER
    ..............M1097A2 (HMMWV UTILITY W/E) or LIN: T07679 gives Unique_ID/Platform_ID/Parent_Equipment_ID = 316573
    ..................AN/VRC-92F(C) or LIN: R45543 gives Unique_ID = 3170867 and Parent_Equipment_ID = 316573 (LIN gives channel amount of 2) - NBOI.[VRC-92F SINC long long WLS-1] tells you which radio gets what channels
    ………………………….RT-1523(C)/U or LIN: R30343 (Channel 1) Unique_ID = 3170868 and Parent_Equipment_ID = 3170867
    12001-2BCT82ABN FS VHF derived from NBOI.SINC1
    ………………………….RT-1523(C)/U or LIN: R30343 (Channel 2) Unique_ID = 3170870 and Parent_Equipment_ID = 3170867
    12172-3BCT82ABN Cmd VHF derived from NBOI.SINC2
    ..................AN/VRC-90F(C) or LIN: R45543 gives Unique_ID = 3170869 and Parent_Equipment_ID = 316573 (LIN gives channel amount of 1)
    ………………………….RT-1523(C)/U or LIN: R30343 (Channel 1) Unique_ID = 3170878 and Parent_Equipment_ID = 3170869
    12002-2BCT82ABN O&I VHF derived from NBOI.SINC3

    I think a reference table with the Radio Mount LINS could be made with an added field for the number of RT's it uses. Right now I am focused on 2 radio types with 3 channels. There is much more radios/Channels/Waveforms

    After the unit/para/section/role/platform/radio mount have been identified somehow, I am guessing that a query needs to look at the Radio type fields ([an/vrc-90F/92F Ws-1) fields to find which net columns to look at for the correct net for channel 1/2/3/4/5.


    Example:
    NBOI.[VRC-92F SINC long long WLS-1] has [SINCGARS1::SINCGARS2] so that should prompt to look at SINC1 for Channel 1 and SINC2 for Channel 2 on the radio mount with LIN:R45543.
    NBOI.[VRC-90F SINC long WLS-1] has [SINCGARS3] so that should prompt to look at SINC3 for Channel 1 on the radio mount with LIN:R68044.


    I think I stirred up the already muddy waters.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I modified your query to do compound INNER join on the four field pairs you identified and the resulting query has only 10 records. After that, I am totally lost. I still don't understand where Wavelength and Networks values are supposed to come from.

    How can parent_equipment_ID link to Platform since there is no common values?

    You may have to dumb this down a lot for those of us who know nothing about radio communications and be very explicit about what you need to find from where. Step-by-step, how you would analyze the data to derive the desired info, i.e., write pseudocode.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I agree with June. Totally lost.

    The reason you had/have duplicate records in your query is that you have a Cartesian query. Since there is no relationship between the two tables in the query, each record in one table is matched with each record in the other table. The "Data" table has 10 record and the table "NOBI" has 2, so 20 records are returned.
    I added the keyword DISTINCT the the SQL of the query and 10 records were returned.


    In your examples, you don't use the same field names as in the tables, I can't determine which tables to use when and don't understand where the values for Wavelength and Networks are coming from or which table they should be put in.
    Are you trying to fill in data from two tables into a third table, filling in two fields in the existing table, or ???????


    Given the data you provided, manually walk me through each step on how YOU would get the values for the Wavelength and Networksfields.

  6. #6
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    ssanfu, June7,

    The information to fill in the data.networks field would come from the NBOI.SINC# fields based on conditions from the radio type Cross referenced by the Data.Lin and NBOI. Columns (I guess you could change the field names to the Radio mount type to its lin?)

    Example:
    [VRC-90F SINC long] and [VRC-90F SINC long Wls-1] TO [R68044] AND [R68044 wls-1] and use that as a means to link? Is there a way to search field names by row?

    Once the vehicle platform and radio mount have been filtered down to for that instance. Then the NBOI columns with the WLS-1 would tell what nets to what transceiver go to.


    There will be 27K plus rows with differing data to sort through with many LINS in the Data Table. Using the NBOI Table and Data Table references unit, paragraph, section, role you find the right vehicle.

    All this is to find the right radio channel net to go with the radio in data. The Data.networks data

    Say we find out the corresponding truck and radio mount. Using an additional table that references the Radio mount LIN (tblRadioLIN.RMntType), it looks for data.equipment_item_ID and radio LIN (tblRadioLIN.RTType) and uses determines its uses that LIN to search (data.LIN AND Data.platform_id) for the right radio(s).

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    That does not provide a step-to-step process of how you would evaluate the data to determine the wavelength and network. Until you do, I doubt we can help. So far, I think I've heard:

    1. join tables on the four fields (done in post 4)

    2. something in [VRC-90F SINC long] and [VRC-90F SINCGARS long Wls-1] fields specifies which SINC# fields to look at (there is no field named [VRC-90F SINC long Wls-1], it is [VRC-90F SINCGARS long Wls-1])

    3. somehow equipment identifiers have bearing on selecting the wavelength and network

    Another table may be appropriate.

    Build a process decision flow chart if that will help you explain the algorithm.

    Brackets [ ] are used to define objects (fields, controls, tables, queries, forms, reports), not data.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Its frustrating sorry I did add the UIC and the netbase fields to the Data table. I used this query for pulling out channel data in the WLS-1 columns

    Code:
    SELECT NBOI.[NBOI Sort Order (int)], NBOI.[VRC-116  HMS MP Single  WLS-1] AS Nets, IIf([Nets]<>"",IIf(Left([Nets],InStr([Nets],":")-1) Like "*|*",Replace(Left(Left([Nets],InStr([Nets],":")-1),InStr([Nets],"|")-1),"[",""),Mid(Left([Nets],InStr([Nets],":")-1),2,20)),"") AS CH1a, IIf([CH1a]="SRW1",[SRW1],IIf([CH1a]="SRW2",[SRW2],IIf([CH1a]="MUOS",[MUOS1],IIf([CH1a]="SINCGARS1",[SINC1],IIf([CH1a]="SINCGARS2",[SINC2],IIf([CH1a]="SINCGARS3",[SINC3],IIf([CH1a]="SINCGARS4",[SINC4]))))))) AS CH1aNet, IIf([Nets]<>"",IIf(Left([Nets],InStr([Nets],":")-1) Like "*|*",Mid(Left([Nets],InStr([Nets],":")-1),InStr([Nets],"|")+1))) AS CH1b, IIf([CH1b]="SRW1",[SRW1],IIf([CH1b]="SRW2",[SRW2],IIf([CH1b]="MUOS",[MUOS1],IIf([CH1b]="SINCGARS1",[SINC1],IIf([CH1b]="SINCGARS2",[SINC2],IIf([CH1b]="SINCGARS3",[SINC3],IIf([CH1b]="SINCGARS4",[SINC4]))))))) AS CH1bNet, IIf([Nets]<>"",Mid(Left([Nets],InStr([Nets],"]")-1),InStr([Nets],":")+2)) AS 116CH2, IIf([116CH2] Like "*|*",Left([116CH2],InStr([116CH2],"|")-1),[116CH2]) AS CH2a, IIf([CH2a]="SRW1",[SRW1],IIf([CH2a]="SRW2",[SRW2],IIf([CH2a]="MUOS",[MUOS1],IIf([CH2a]="SINCGARS1",[SINC1],IIf([CH2a]="SINCGARS2",[SINC2],IIf([CH2a]="SINCGARS3",[SINC3],IIf([CH2a]="SINCGARS4",[SINC4]))))))) AS CH2aNet, IIf([116CH2] Like "*|*",Mid([116CH2],InStr(1,[116CH2],"|")+1)) AS CH2b, IIf([CH2b]="SRW1",[SRW1],IIf([CH2b]="SRW2",[SRW2],IIf([CH2b]="MUOS",[MUOS1],IIf([CH2b]="SINCGARS1",[SINC1],IIf([CH2b]="SINCGARS2",[SINC2],IIf([CH2b]="SINCGARS3",[SINC3],IIf([CH2b]="SINCGARS4",[SINC4]))))))) AS CH2bNet, [CH1aNet] & IIf([CH1bNet]<>""," | " & [CH1bNet],"") AS [116R1 Channel1], [CH2aNet] & IIf([CH2bNet]<>""," | " & [CH2bNet],"") AS [116R1 Channel2]FROM NBOI
    WHERE (((NBOI.[VRC-116  HMS MP Single  WLS-1])<>""));

  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by Thompyt View Post
    I did add the UIC and the netbase fields to the Data table.
    The dB I downloaded in Post #1 does not have "UIC" and "netbase" fields in table "Data".
    Table "NBOI" does not have a field "[VRC-116 HMS MP Single WLS-1]". The query in Post #8 will not execute.

    ================================================== =====================================

    Trying to understand........
    Click image for larger version. 

Name:	Desired1.png 
Views:	20 
Size:	190.0 KB 
ID:	31775
    Looking at A in the image above, the first record that has an entry in the field "Networks" has a blue circle.
    I created a query "qryData", B, using the data in the fields circled in red from A -"DesiredOutcome".

    Executing the query resulted in one record from table "Data" (B) . My understanding of the problem is you want to somehow fill in field "Networks" in table "Data" using table "NBOI". But there does not seem to be enough data in table "NBOI" to be matched to anything in table "Data".

    Are there other tables involved?

  10. #10
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    ssanfu,
    Exactly - I want to fill in the networks field in Data.networks from the appropriate record in NBOI.

    92FRadio: IIf([Equip LIN]="R68146",DLookUp("[unique_id]","[Data]","[unique_id] ='" & [equipment_item_id] & "'")) gives out the Radio Unique_ID

    How do I get these codes to reference 92fRadio?

    Channel1:
    155CH1: IIf([Equip LIN]="XXB840",DLookUp("[unique_id]","[Data]","[unique_id] ='" & [parent_equipment_item_id] & "'"))

    Or Channel2:
    155CH1: IIf([Equip LIN]="XXB840",DLookUp("[unique_id]","[Data]","[unique_id] ='" & [parent_equipment_item_id] & "'"))

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The first expression makes no sense. Why would you pull unique_id when unique_id is also part of the search criteria? unique_id and equipment_item_id are identical values in each record, this will just pull unique_id from that same record. Perhaps it should be referencing [parent_equipment_item_id]. In which case, possibly a table self-join is what you need instead of this DLookup.

    Also, the IIf() expressions do not provide value for the False return.

    Again, describe step-by-step the analysis process you would follow to derive the desired data - no Access code, no queries, no DLookup() - just simple, plain English.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    June7,
    Example:
    Find the rows were LIN equals a value of R45543 and get is unique_ID 3174010.
    Then find all the the LINs with XXB840 and XXB841 where the parent_equipment_item_id is equal to the unique_ID 3174010.

    I was leaving first equation because all i need to do is change between parent_equipment_item_id and Equipment_Item_ID. There are other radios that have the mount, then a radio with 2 channels. I do see your point for making it simpler.
    Thanks

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I give up. Still makes no sense. I am using the first posted db. There are two records with LIN R45543. Neither of them has a unique_ID of 3174010. And how should I know which to choose? The expressions you showed don't even use values that are in the db.

    The analysis steps should not even reference specific values from records. Define the logical process then build structure to implement it. Review: https://en.wikipedia.org/wiki/Flowchart and http://www.unf.edu/~broggio/cop2221/2221pseu.htm
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    looktest2.zip


    I am in the process of making a wider db to use for examples. "There are two records with LIN R45543." The both have unique_id's.

    the sub component to the radio mount parent_id's are the same as the LIN: R45543 unique_ID. you can find the 2 unique_ids of the sub components with parent_id and pertinent LIN.

    I realize I didn't reference the correct ID's. Apologies.

    I am in the process of determining how to reference which channel goes to which radio for this on. The higher unique_ID should be channel 2.






  15. #15
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    It looks like you are in the Army, so thank you for your service.



    "Old Programmer's Rule" is this: If you can't do it on paper then you can't do it in Access.
    Until you can draw out your tables AND rules for how they change for each function you want to perform, you are going nowhere fast.

    I created a query using tables "Data" and "DesiredOutcome" (from looktest2.accdb). The query is
    Code:
    SELECT Data.id, Data.[TOE Title], Data.[Bumper # / Plat ID], Data.[USA #], Data.equipment_item_id, Data.platform_id, Data.parent_equipment_item_id, Data.[Equip Common Name], Data.Networks, DesiredOutcome.Networks
    FROM Data INNER JOIN DesiredOutcome ON (Data.parent_equipment_item_id = DesiredOutcome.parent_equipment_item_id) AND (Data.platform_id = DesiredOutcome.platform_id) AND (Data.equipment_item_id = DesiredOutcome.equipment_item_id) AND (Data.[USA #] = DesiredOutcome.[USA #]) AND (Data.[Bumper # / Plat ID] = DesiredOutcome.[Bumper # / Plat ID]) AND (Data.[TOE Title] = DesiredOutcome.[TOE Title])
    ORDER BY Data.[TOE Title], Data.[Bumper # / Plat ID], Data.[USA #], Data.equipment_item_id, Data.platform_id;
    For the record where ID = 7, how did you get "12172-3BCT82ABN Cmd VHF" as the value for "Networks" (in "DesiredOutcome")?

    Lets say you printed the tables on paper. Tell me the steps you used to get the "Networks" value of "12172-3BCT82ABN Cmd VHF" for ID = 7.
    No Access queries, just using the printouts; what are the table names and field names........

    Describe the steps.
    Steps
    -------
    1)
    2)
    3)
    .
    .
    .



    I think this will require VBA coding, so it is important to state each and every step.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Find related data in a table
    By paultje_bos in forum Queries
    Replies: 4
    Last Post: 12-05-2016, 04:49 AM
  2. Replies: 3
    Last Post: 06-26-2014, 08:35 AM
  3. Replies: 7
    Last Post: 10-15-2012, 09:48 AM
  4. Replies: 4
    Last Post: 07-09-2012, 01:49 PM
  5. Place a table value in a textbox
    By justhininabouti in forum Programming
    Replies: 2
    Last Post: 11-26-2011, 10:56 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums