none
Software tracking Crosstab query? RRS feed

  • Question

  • Hello,

    We are trying to tracking applications on workstations.  We have 2 tables...  

    Computers Table

    ComputerID

    ComputerName

    UserID

    Software Table

    AppID

    ApplicationName

    I have a join table with ComputerID, AppID, InstalledYN

    We're trying to have a query that shows Computername, User, App1-YN, App2-YN, etc...

    DO i need to create a separate record in the join table for each possible PC to App? SO every time we add a new PC or App create the needed records?

    Seems like there should be an easier way...

    Thanks,

    Ernst.

    Wednesday, October 3, 2018 2:07 PM

All replies

  • Instead of three fields ComputerID, AppID, InstalledYN, use only two: ComputerID, AppID.

    Then just add records for installed apps to the join table. So if computer 1 has 3 apps, add 3 records for computer 1, and if computer 2 has 10 apps, add 10 records for computer 2.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, October 3, 2018 3:20 PM
  • Hi Hans,

    Ok...  so if there is a record its installed if not then 0...  So how do I get the 0s to show up?  With the crosstab query it doesnt show columsn with Nulls...

    Ernst.

    Wednesday, October 3, 2018 3:40 PM
  • As Hans says, omit the Boolean InstalledYN column from the table which models the many-to-many relationship type, and insert rows into that table only where the computer has the application installed, which is easily done in a form/subform.  You can then return the data in a crosstab query, returning 'Y' or 'N' at the intersections of the rows and columns by calling the IIF function in the TRANSFORM clause.  The following is an example, using data from my DatabaseBasics demo, which returns the contacts per employee:

    TRANSFORM IIF(MAX(ContactEmployers.EmployerID) IS NULL,"N","Y")
    SELECT FirstName, LastName
    FROM Employers INNER JOIN (Contacts INNER JOIN ContactEmployers
    ON Contacts.ContactID = ContactEmployers.ContactID)
    ON Employers.EmployerID = ContactEmployers.EmployerID
    GROUP BY LastName, FirstName
    PIVOT Employer;

    It returns the following result table:

    FirstName   LastName   Escargots   Paris Regional   Stafford Widget   West Midlands
                                          de Paris      Medical Center  Company            Gadget Stores

    Jennifer       Black           N                N                        N                        Y
    Juliette        Blanc           Y                N                        N                        N
    John            Brown         N                N                        Y                         Y
    Janet          Green          N                Y                         N                        N
    James         White          N                N                         Y                        N

    if you want to try it yourself, you'll find the file as DatabaseBasics.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that the use of the MAX aggregation operator in the TRANSFORM clause is entirely arbitrary.  Any aggregation operator could be used.

    Ken Sheridan, Stafford, England


    Wednesday, October 3, 2018 4:11 PM
  • Hi Ken,

    Thanks for the help.  It does clarify things...  so if you enter a new employer which will get ID 5 in the DB then run the query the new employer will not get a column until at least one Contact is assigned this employer in the join table...

    How can we get a column even if there are no related records?

    Thanks,

    Ernst.

    Thursday, October 4, 2018 1:25 AM
  • You could create a query based on the software table and the join table, linked on AppID.

    Double-click the join line and select the option to return all records from the software table.

    For the Value field, use something like

    V: Nz(Count(*),0)

    and set its Total option to Expression.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, October 4, 2018 10:57 AM
  • How can we get a column even if there are no related records?
    By adding an IN clause in which all possible column headings are listed.  A column with 'N' at each row will be returned if there is no data for that column.  This also allows you to control the order in which the columns are returned, e.g. in my case the employers could be returned in country order:

    PIVOT Employer IN("Escargots de Paris", "Stafford Widget Company", "West Midlands Gadget Stores", "Paris Regional Medical Center");

    The first is Paris, France, the last is Paris, USA, the middle two are in the UK.


    Ken Sheridan, Stafford, England

    Thursday, October 4, 2018 12:09 PM