How to Combine 6 Reports...?

  • Hello... I recently purchased an Inventory System and their Reporting Area is not so good... and to request "Custom Reports" is extremely expensive.

    So I decided to ask them for Access to my SQL tables... the problem I am having is that to produce ONE report with all the data that I need... I am having to run 7 different reports, copy & paste them into excel...

    Is there any way I can merge them, and get 1 single report? BTW... my SQL Skills are very limited 😛 ... many thanks in advance! 😉

    here is what I got so far:

    Report 1 (This will give me the most information about the products, but some of the values stored in Items & Details_J Table are Codes... so I need to run 6 other reports to get the Actual Value) :w00t: :

    SELECT

    i.ITEMS_PK as 'SKU 1',

    i.INVNUM as 'SKU 2',

    i.OWNERNUM as 'Online SKU',

    i.NEWITEM as 'Condition',

    i.BIN as 'Location',

    k.DESCRIPT as 'Online Status',

    h.DESCRIPT as 'Brand',

    i.MODELNUM as 'Reference #',

    i.SERIALNUM as 'Serial #',

    l.DESCRIPT as 'Model Name',

    c.lc_Descript as 'Dial Color',

    i.AMOUNT as 'Cost',

    i.STATUS as 'Status',

    j.Weight as 'Weight',

    i.DESCRIPT as 'Item Description',

    i.CSOURCE as 'Seller'

    FROM items as I

    inner join Detail_J as J

    on I.ITEMS_PK = J.Items_FK

    inner join Level3 as L

    on I.LEVEL3_FK = L.lv3_pk

    inner join Level4 as K

    on I.LEVEL4_FK = K.lv4_PK

    inner join Level5 as H

    on I.LEVEL5_FK = H.lv5_PK

    inner join Lookup_C as C

    on I.Color = C.lc_pk

    order by J.Items_FK

    Report 2:

    SELECT

    c.lc_Descript as 'Condition 2'

    FROM items as I

    inner join Lookup_C as C

    on I.Condition = C.lc_pk

    order by I.Items_PK

    Report 3:

    SELECT

    c.lc_Descript as 'Gender'

    FROM Detail_J as J

    inner join Lookup_C as C

    on J.Gender_FK = C.lc_pk

    order by J.Items_FK

    Report 4:

    SELECT

    c.lc_Descript as 'Bezel'

    FROM Detail_J as J

    inner join Lookup_C as C

    on J.Style_FK = C.lc_pk

    order by J.Items_FK

    Report 5:

    SELECT

    c.lc_Descript as 'Case Material'

    FROM Detail_J as J

    inner join Lookup_C as C

    on J.Metal_FK = C.lc_pk

    order by J.Items_FK

    Report 6 :

    SELECT

    c.lc_Descript as 'Bracelet Size'

    FROM Detail_J as J

    inner join Lookup_C as C

    on J.Sizelen_FK = C.lc_pk

    order by J.Items_FK

    Report 7 :

    SELECT

    c.lc_Descript as 'Bracelet Material'

    FROM Detail_J as J

    inner join Lookup_C as C

    on J.Karat_FK = C.lc_pk

    order by J.Items_FK

    this is my result:

  • Please provide sample data and expected results as indicated by the link in my signature.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Looking at your queries, it looks like you may not realize that you can join to the same table multiple times as long as they have different aliases.

    SELECT *

    FROM Join_Details j

    INNER JOIN Lookup_C AS g --gender

    ON j.Gender_FK = g.lc_pk

    INNER JOIN Lookup_C AS s --style

    ON j.Style_FK = s.lc_pk

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • With the 1st report I get something like this:

    SELECT

    i.ITEMS_PK as 'SKU 1',

    i.INVNUM as 'SKU 2',

    i.OWNERNUM as 'Online SKU',

    i.NEWITEM as 'Condition',

    i.BIN as 'Location',

    k.DESCRIPT as 'Online Status',

    h.DESCRIPT as 'Brand',

    i.MODELNUM as 'Reference #',

    i.SERIALNUM as 'Serial #',

    l.DESCRIPT as 'Model Name',

    c.lc_Descript as 'Dial Color',

    i.AMOUNT as 'Cost',

    i.STATUS as 'Status',

    j.Weight as 'Weight',

    i.DESCRIPT as 'Item Description',

    i.CSOURCE as 'Seller'

    FROM items as I

    inner join Detail_J as J

    on I.ITEMS_PK = J.Items_FK

    inner join Level3 as L

    on I.LEVEL3_FK = L.lv3_pk

    inner join Level4 as K

    on I.LEVEL4_FK = K.lv4_PK

    inner join Level5 as H

    on I.LEVEL5_FK = H.lv5_PK

    inner join Lookup_C as C

    on I.Color = C.lc_pk

    WHERE I.BIN = 'PH'

    order by J.Items_FK

    Result:

    SKU 1 SKU 2 Online SKU ConditionLocationOnline StatusBrand Reference #

    579 I-579 RL120 1 PH D -> E -> A ROLEX 326935 ch

    585 I-585 RL70 1 PH D -> E -> A ROLEX 116505 PBK

    973 I-973 CA8 1 PH D -> E -> A CARTIER W69004Z2

    with the second query I get this:

    SELECT

    c.lc_Descript as 'Condition 2'

    FROM items as I

    inner join Lookup_C as C

    on I.Condition = C.lc_pk

    WHERE I.BIN = 'PH'

    order by I.Items_PK

    Result:

    Condition 2

    N: B&P

    N: B&P

    N: B&P

    what I need is ALL the information in 1 report:

    SKU 1 SKU 2 Online SKU ConditionLocationOnline StatusBrand Reference # Condition 2

    579 I-579 RL120 1 PH D -> E -> A ROLEX 326935 ch N: B&P

    585 I-585 RL70 1 PH D -> E -> A ROLEX 116505 PBK N: B&P

    973 I-973 CA8 1 PH D -> E -> A CARTIER W69004Z2 N: B&P

  • drew.allen (5/27/2016)


    Looking at your queries, it looks like you may not realize that you can join to the same table multiple times as long as they have different aliases.

    SELECT *

    FROM Join_Details j

    INNER JOIN Lookup_C AS g --gender

    ON j.Gender_FK = g.lc_pk

    INNER JOIN Lookup_C AS s --style

    ON j.Style_FK = s.lc_pk

    Drew

    Thank You very much for your explanation!

    Following your advice, I made some adjustments on the code and this is how it looks now!

    SELECT

    i.ITEMS_PK as 'SKU 1',

    i.INVNUM as 'SKU 2',

    i.OWNERNUM as 'Online SKU',

    i.NEWITEM as 'Condition',

    T.lc_Descript as 'Condition 2',

    i.STATUS as 'Status',

    i.BIN as 'Location',

    k.DESCRIPT as 'Online Status',

    h.DESCRIPT as 'Brand',

    i.MODELNUM as 'Reference #',

    i.SERIALNUM as 'Serial #',

    l.DESCRIPT as 'Model Name',

    c.lc_Descript as 'Dial Color',

    i.AMOUNT as 'Cost',

    M.lc_Descript as 'Case Material',

    b.lc_Descript as 'Bracelet Material',

    S.lc_Descript as 'Bezel Type',

    g.lc_Descript as 'Gender',

    j.Weight as 'Weight',

    z.lc_Descript as 'B Size',

    i.DESCRIPT as 'Item Description',

    i.CSOURCE as 'Seller'

    FROM items as I

    inner join Detail_J as J

    on I.ITEMS_PK = J.Items_FK

    inner join Level3 as L

    on I.LEVEL3_FK = L.lv3_pk

    inner join Level4 as K

    on I.LEVEL4_FK = K.lv4_PK

    inner join Level5 as H

    on I.LEVEL5_FK = H.lv5_PK

    inner join Lookup_C as C

    on I.Color = C.lc_pk

    inner join Lookup_C as T

    on I.Condition = T.lc_pk

    INNER JOIN Lookup_C AS G --gender

    ON j.Gender_FK = G.lc_pk

    INNER JOIN Lookup_C AS S --Bezel

    ON j.Style_FK = S.lc_pk

    inner join Lookup_C as B ---bracelet material

    on J.Karat_FK = B.lc_pk

    inner join Lookup_C as Z ----bracelet Size

    on J.Sizelen_FK = Z.lc_pk

    inner join Lookup_C as M ---- Case Material

    on J.Metal_FK = M.lc_pk

    order by J.Items_FK

    I am finally getting the DATA I need, in ONE single report!!!:-D

    Happy Weekend!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply