Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2005
    Location
    Minnesota
    Posts
    42

    Unanswered: MS Access Convert Rows to Columns and avoid null so that you can Group into 1 record

    Greetings!,

    In Microsoft Access i am trying to make Columns into Rows from a table and avoid getting null so that I can group according to Dept all the other Attribute Fields into 1 row by Dept. If I use iif() function and pull each attribute into separate field I end up with the below format with null values. I would like everything in one row according to Dept.

    Is there sql that can do this efficiently? I could create 6 alias tables and link to table itself and pull each of the 6 Dept attributes separately in each alias table to get desired result. I am looking for a more efficient way if possible. Anyone know what better sql would look like?

    ThanksClick image for larger version. 

Name:	A0003.PNG 
Views:	5 
Size:	24.5 KB 
ID:	16361

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    use the nz function to supply a value if the specified column is NULL
    use a pivot table / querry to create the data


    but better yet don't use an EAV model, unless you really, really need to
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2005
    Location
    Minnesota
    Posts
    42
    Thanks, in query I am using AP TYPE FIELD: iif([Table1]![ATTRIBUTE]="AP_TYPE",[Table1]![ATTRIB_VALUE],"") and then Group by for each Attribute into a new field and grouping but the no space won't compress. I think I will be in same boat with NZ function. I don't think in a pivot i can get the values to display from each new column(from Attribute) which was a row and Value(from ATTRIB_VALUE). Unless I am not seeing something.

    I can easily get this to work with 6 alias tables and link but it seems really inefficient, i think.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by John2Chr View Post
    I can easily get this to work with 6 alias tables and link but it seems really inefficient, i think.
    seems inefficient or is inefficient? what efforts have you taken to prove that either way?
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2005
    Location
    Minnesota
    Posts
    42
    Yes, I did not initially try but now i did and here it is with just 3 of the fields...need to do with 6 but can use same method. Not sure if there is a better way to do this. If not, I will need to do it this way then - I will join table to itself 6 times and fight with the nulls by choosing <>"". This might be best way to do it, then?

    SELECT Table1.DEPT, IIf([Table1_1]![ATTRIBUTE]="AP TYP",[Table1_1]![ATTRIB_VALUE],"") AS [AP TYPE F], IIf([Table1_2]![ATTRIBUTE]="ACT",[Table1_2]![ATTRIB_VALUE],"") AS [ACT F], IIf([Table1_3]![ATTRIBUTE]="AUTH",[Table1_3]![ATTRIB_VALUE],"") AS [AUTH F]
    FROM (((((Table1 LEFT JOIN Table1 AS Table1_1 ON Table1.DEPT = Table1_1.DEPT) LEFT JOIN Table1 AS Table1_2 ON Table1.DEPT = Table1_2.DEPT) LEFT JOIN Table1 AS Table1_5 ON Table1.DEPT = Table1_5.DEPT) LEFT JOIN Table1 AS Table1_3 ON Table1.DEPT = Table1_3.DEPT) LEFT JOIN Table1 AS Table1_4 ON Table1.DEPT = Table1_4.DEPT) LEFT JOIN Table1 AS Table1_6 ON Table1.DEPT = Table1_6.DEPT
    GROUP BY Table1.DEPT, IIf([Table1_1]![ATTRIBUTE]="AP TYP",[Table1_1]![ATTRIB_VALUE],""), IIf([Table1_2]![ATTRIBUTE]="ACT",[Table1_2]![ATTRIB_VALUE],""), IIf([Table1_3]![ATTRIBUTE]="AUTH",[Table1_3]![ATTRIB_VALUE],"")
    HAVING (((IIf([Table1_1]![ATTRIBUTE]="AP TYP",[Table1_1]![ATTRIB_VALUE],""))<>"") AND ((IIf([Table1_2]![ATTRIBUTE]="ACT",[Table1_2]![ATTRIB_VALUE],""))<>"") AND ((IIf([Table1_3]![ATTRIBUTE]="AUTH",[Table1_3]![ATTRIB_VALUE],""))<>""));

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    based on data that looks like:-
    Code:
    department	attribute_code	item
    A0002	ACT	A100101
    A0002	AP_TYPE	05
    A0002	LLBD	01
    A0002	PRG	A1001
    A0002	SPEC	13 142 01
    A0003	ACT	A100101
    A0003	AP_TYPE	05
    A0003	AUTH	A
    A0003	LLBD	01
    A0003	PRG	A1001
    A0003	SPEC	13 142 01
    B0007	SPEC	999
    C0666	ACT	B100101
    C0666	AP_TYPE	05
    C0666	AUTH	B
    C0666	LLBD	99
    C0666	PRG	B1001
    C0666	SPEC	15 142 01
    ok so what you could try is something similar to:-
    Code:
    SELECT d.code as Dept, I1.item as  AP_TYP , I2.item as PRG, I3.item as ACT, i4.item as LLBD , i5.item as SPEC, i6.item as AUTH
    FROM (((((departments AS d LEFT JOIN eav_data AS I3 ON d.code = I3.department) LEFT JOIN eav_data AS I1 ON d.code = I1.department) LEFT JOIN eav_data AS i4 ON d.code = i4.department) LEFT JOIN eav_data AS i5 ON d.code = i5.department) LEFT JOIN eav_data AS i6 ON d.code = i6.department) LEFT JOIN eav_data AS I2 ON d.code = I2.department
    WHERE  I1.attribute_code='AP_TYPE' AND I2.attribute_code='PRG' AND I3.attribute_code='ACT' AND i4.attribute_code='LLBD' AND i5.attribute_code='SPEC' AND i6.attribute_code='AUTH';
    which gives the resutls:-
    Code:
    Dept	AP_TYP	PRG	ACT	LLBD	SPEC	AUTH
    A0003	05	A1001	A100101	01	13 142 01	A
    C0666	05	B1001	B100101	99	15 142 01	B
    I set out two tables
    one for departments
    one for the data, which I called eav_data. eav data is joined 6 times to the department table using the alias I1..I6

    Ive not worked out a way to report blank attributes within the same group
    ..which is a long winded way of dong it

    the alternative, easier way to do this is to do as suggested in post #'2 and use a pivot / crosstab query.
    Code:
    TRANSFORM  min(eav_data.[item]) AS MinOfitem
    SELECT eav_data.[department], Min(eav_data.[item]) AS [Total Of item]
    FROM eav_data
    GROUP BY eav_data.[department]
    PIVOT eav_data.[attribute_code];;
    which gives the results:-
    Code:
    department	Total Of item	ACT	AP_TYPE	AUTH	LLBD	PRG	SPEC
    A0002	01	A100101	05		01	A1001	13 142 01
    A0003	01	A100101	05	A	01	A1001	13 142 01
    B0007	999						999
    C0666	05	B100101	05	B	99	B1001	15 142 01
    Last edited by healdem; 05-21-15 at 05:34.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Apr 2005
    Location
    Minnesota
    Posts
    42
    Wow, That seems way better(more organized) - your pivot solution. I don't really understand that function well, I believe so sorry if i doubted. It appears that Max will do the same thing if you replace where Min is.

    Lets say I had an EFFective Date field in addition to the three columns. So for Dept A0003 it has an AP TYP of 02 with an effective date of 01/01/1901 and an effective date 1/1/2013 of AP TYP to change to 05. All of these attributes have a max effective date and can change to something new based on effective date. So for Fiscal Year through 2013 Dept A0003 had an app type of 02 and for FY2014 and FY2015 it was 05. Can this solution you provide pull based on max effective date too? Lets say I wanted to pull for FY2012 what the Department attributes are? Is this easy to do in this solution?

    Right now I am running a query off the table to sort out effective date before trying to assign attributes but perhaps I can solve in this query too by putting max effective date as row and using Max against it.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Its all down to the where clause(s)
    ...although in access sql you may be better off limiting your data by pre filtering data in a query. You can use a table or query or both or multiples thereof as feedstock to subsequent queries.
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Apr 2005
    Location
    Minnesota
    Posts
    42
    Yeah, struggling to get this to work in the pivot table. I can solve using the 6 alias tables and maxeffect date on each attribute with link. But
    I thought maybe i could get this to work with subquery and here is my subquery which isn't panning out.placed in the pivot table..Basically would like to pull by maxeffect and that effect date could be different in each fiscal year...see attached.

    here is maxeffect date query:
    (SELECT Max(m.EFFDT) FROM Table2 AS m WHERE m.DEPT = t.DEPT AND m.ATTRIBUTE = t.ATTRIBUTE AND m.EFFDT <= #2/1/2014#) Ie. I am trying to pull attributes for FY2014 but want flexibility to pull any entered date.

    here is whole query:
    TRANSFORM Max(Table2.ATTRIB_VALUE) AS MinOfitem
    SELECT Table2.Dept, Max(Table2.ATTRIB_VALUE) AS [Total Of ATTRIB_VALUE]
    FROM Table2
    WHERE (((Table2.EFFDT)=(SELECT Max(m.EFFDT) FROM Table2 AS m WHERE m.DEPT = t.DEPT AND m.ATTRIBUTE = t.ATTRIBUTE AND m.EFFDT <= #2/1/2014#)))
    GROUP BY Table2.Dept, Table2.EFFDT
    PIVOT Table2.ATTRIBUTE;

    Here is what I am trying to achieve:
    Click image for larger version. 

Name:	A0003effdt.PNG 
Views:	2 
Size:	49.7 KB 
ID:	16362

  10. #10
    Join Date
    Apr 2005
    Location
    Minnesota
    Posts
    42
    ok...i am able to grab the max effective dates for ATTRIBUTE within Dept but I'm back to square one in that I cannot get it to transpose with Attribute as a Column heading with Attrib Value underneath in only one row without the grouping problem of having spaces and multiple rows for a dept.

    Here is sql that works for grabbing but i need to transpose.

    SELECT a.DEPT, a.ATTRIBUTE, a.EFFDT, a.ATTRIB_VALUE
    FROM table2 AS a, (SELECT ATTRIBUTE, max(EFFDT) AS max_date FROM table2 GROUP BY ATTRIBUTE) AS b
    WHERE (((a.ATTRIBUTE)=[b].[ATTRIBUTE]) AND ((a.EFFDT)=[b].[max_date]));

  11. #11
    Join Date
    Apr 2005
    Location
    Minnesota
    Posts
    42
    This is what i try but it won't let me aggregate so I am stuck with multiple rows. I get an error when trying to pivot off the subquery approach.

    Here is sql:

    SELECT a.DEPT, a.ATTRIBUTE, a.EFFDT, IIf([a]![ATTRIBUTE]="AP TYP",[a]![ATTRIB_VALUE],"") AS [AP TYPE], IIf([a]![ATTRIBUTE]="LLBD",[a]![ATTRIB_VALUE],"") AS LLBD2
    FROM table2 AS a, (SELECT ATTRIBUTE, max(EFFDT) AS max_date FROM table2 GROUP BY ATTRIBUTE) AS b
    GROUP BY a.DEPT, a.ATTRIBUTE, a.EFFDT, IIf([a]![ATTRIBUTE]="AP TYP",[a]![ATTRIB_VALUE],""), IIf([a]![ATTRIBUTE]="LLBD",[a]![ATTRIB_VALUE],"")
    HAVING (((a.ATTRIBUTE)=[b].[ATTRIBUTE]) AND ((a.EFFDT)=[b].[max_date]));

  12. #12
    Join Date
    Apr 2005
    Location
    Minnesota
    Posts
    42
    Got it to work....I used pivot method suggested above...took a few swings but got it working....

    TRANSFORM Min(a.ATTRIB_VALUE) AS MinOfitem
    SELECT a.DEPT, Min(a.ATTRIB_VALUE) AS [Total Of ATTRIB_VALUE]
    FROM table2 AS a, (SELECT ATTRIBUTE, max(EFFDT) AS max_date FROM table2 GROUP BY ATTRIBUTE) AS b
    WHERE (((a.ATTRIBUTE)=[b].[ATTRIBUTE]) AND ((a.EFFDT)=[b].[max_date]))
    GROUP BY a.DEPT
    PIVOT a.ATTRIBUTE;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •