How to Combine 6 Reports...?

Question

Post reply

How to Combine 6 Reports...?

info 88249

SSC Enthusiast

Points: 119
More actions
May 27, 2016 at 8:28 am

#400216

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:

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

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

drew.allen SSC Guru Points: 76985 More actions · Answer 1

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

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

drew.allen SSC Guru Points: 76985 More actions · Answer 2

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

info 88249 SSC Enthusiast Points: 119 More actions · Answer 3

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

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

info 88249 SSC Enthusiast Points: 119 More actions · Answer 4

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!