Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a query in Microsoft Access which extracts data from 2 tables: 'modems' and 'antennas'

Headings: 'StName' 'ModemType' 'ModemInstallDate' 'ModemPrice' 'AntennaType' 'AntInstallDate' 'AntennaPrice' 
Line1:    'St01'   'modem1'    '01/01/2011'       '$300'       'ant01'       '01/01/2012'     '$1000' 
Line2:    'St02'   'modem1'    '01/01/2012'       '$300'       'ant01'       '01/01/2010'     '$1000'   
Line3:    'St03'   'modem1'    '01/01/2010'       '$300'       'ant01'       '01/01/2011'     '$1000'  

This output puts data for different equipment in one line under the station name 'St0x'. However I need to divide this long line to some shorter lines which would contain data for the particular equipment. Thus I have to obtain an output (View or Excel table) which would look like the following:

Headings: 'StName' 'Equipment' 'EquipmentType' 'InstallDate' 'Price' '2010'  '2011' '2012'
Line1:    'St01'   'modem'     'modem1'        '01/01/2011'  '$300'  ''      '$300'  ''
Line2:    'St01'   'antenna'   'ant01'         '01/01/2012'  '$1000' ''      ''      '$1000'
Line3:    'St02'   'modem'     'modem1'        '01/01/2012'  '$300'  ''      ''      '$300'
Line4:    'St02'   'antenna'   'ant01'         '01/01/2010'  '$1000' '$1000' ''      ''  
Line5:    'St03'   'modem'     'modem1'        '01/01/2010'  '$300'  '$300'  ''      ''
Line6:    'St03'   'antenna'   'ant01'         '01/01/2011'  '$1000' ''      '$1000' ''     

Is it actually possible?

share|improve this question
    
I don't know much about Access, but like MySQL, this is a database. There has to be an equivalent function. In MySQL, you would do this with the CREATE VIEW command, selecting only part of the lines for each view. Your solution lies in finding the equivalent for Access. –  Ariane Jan 8 '13 at 0:21
    
It looks doable. An if statement in your query should take care of getting the amounts in the year column, and the rest doesn't look that hard. Will look further when i get near a box with access if it hasn't been fixed by then. Can you illustrate how the individual tables are structured - it may be easier to create a new query. –  mcalex Jan 8 '13 at 0:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.