Solved

Table grouping options in Access reports

Posted on 2004-10-08
35
176 Views
Last Modified: 2008-03-06
Hello - I have inherited an Access database whose basic structure i don't want to modify at the moment

There is a query which generates a report which looks like the following:

SELECT Orders.OrderID, Colours.Colour, Ranges.Range, Customers.CompanyName, [Order Details].OrderDetailID, [Order Details].ProductID, [Order Details].[Drawer Width], [Order Details].[Blank Height], [Order Details].[Gable Height], [Order Details].[Gable Depth], [Order Details].[Blank Width], Products.[Gable Qty], Products.[Panel Qty], [Order Details].[Base Bottom Width], [Order Details].[Base Shelf Width], Products.[Base Shelf Qty], [Order Details].[Base Bottom Depth], [Order Details].[Rail Width], [Order Details].[Rail Depth], [Order Details].[Back Width], Products.[Back Qty], [Order Details].[Base Back Height], [Order Details].[Base Shelf Depth], [Order Details].[Base Con PanWidth], Products.[Blank Qty], [Order Details].[Base Con Pan Depth], Products.[Base Con Pan Qty], Products.[Base Qty], Products.[Std Drawer Qty], Products.[Pan Drawer Qty], Products.[Rail Qty], [Order Details].ProductName, [Order Details].Depth, [Order Details].Hand, [Order Details].Width, [Order Details].Height, [Order Details].Description, [Order Details].[Panel Height], [Order Details].[Panel Width], [Order Details].Quantity, [Order Details].UnitPrice
FROM (Ranges INNER JOIN (Customers INNER JOIN (Colours INNER JOIN Orders ON Colours.[Colour ID] = Orders.[Colour ID]) ON Customers.CustomerID = Orders.CustomerID) ON Ranges.[Range ID] = Orders.[Range ID]) INNER JOIN (Products INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID) ON Orders.OrderID = [Order Details].OrderID;

OK i know that's a lot - but basically one of the reports that is generated by this shows a cutting list.  I need to create a report which has the following as titles:

Type   Height    Width    Depth   Quantity

Where Height, Width and Depth generically display any of the variables which have height width or depth in the name and Type says whether it's a Base, Base Shelf, Panel, Pan Drawer etc.

What would be the best approach to deal with this?

Many thanks
0
Comment
Question by:alipri
  • 25
  • 10
35 Comments
 
LVL 6

Expert Comment

by:mcorrente
ID: 12259109
Alright... you need a union query
0
 
LVL 6

Expert Comment

by:mcorrente
ID: 12259115
I'm trying to dig through your query to write it.
0
 

Author Comment

by:alipri
ID: 12259158
Brilliant thank you
0
 
LVL 6

Expert Comment

by:mcorrente
ID: 12259267
Alright... I don't think I have enough info to write it, let me ask some questions:

Are these all the products you're dealing with:
Drawer
Blank
Gable
Base Bottom
Base Shelf
Rail
Back
Base Con Pan
Std Drawer
Pan Drawer
Panel
0
 
LVL 6

Expert Comment

by:mcorrente
ID: 12259274
That "blank" may just be a field for where you don't have a specific column named for it, right?
0
 
LVL 6

Expert Comment

by:mcorrente
ID: 12259407
actually, give me a list of the different products and what fields apply to each, and I'll give you the query.
0
 

Author Comment

by:alipri
ID: 12259427
right in the first instance - blank is a blank panel
0
 
LVL 6

Expert Comment

by:mcorrente
ID: 12259587
also include the ProductID for each product... I'll need that to order the query.
0
 

Author Comment

by:alipri
ID: 12259781
Wow- I have a huge list of Products do you want them all with the data?
0
 
LVL 6

Expert Comment

by:mcorrente
ID: 12259959
Well, you're table design isn't optimal.  You're going to have to create a separate query for each type of product because they all rely on different fields for their measurements.
In essence, your query will consist of several queries joined by UNION:

SELECT [Query to pull First Product]
UNION SELECT [Query to pull Second Product]
UNION SELECT [Query to pull Third Product]

etc.
0
 

Author Comment

by:alipri
ID: 12260042
OK I'll try that, thank you very much.

(I know the table design is horrendous, but like i say, it's been inherited and i can't afford to change the underlying structure at the moment!)
0
 
LVL 6

Expert Comment

by:mcorrente
ID: 12260177
Understood.  My Access "Mentor" said that the best access design people never knock something that works, no matter how it works.
0
 
LVL 6

Expert Comment

by:mcorrente
ID: 12260251
if you have an email address, I'll send you a couple of pages from a book I have that describe Union Queries.  Up to you.
0
 

Author Comment

by:alipri
ID: 12260597
Ouch - sorry just reread the solution - I have something like 200 products, each with individual gables, pan drawers, shelves etc...

I can't go through each one right?
0
 
LVL 6

Expert Comment

by:mcorrente
ID: 12260768
hmmm.... if this is an .mdb file, I'd be willing to look at it.  It may be that I'm not fully understanding the layout.  Or, if you could send me just the data from the two tables (Order Details and Products), I could look at that.
0
 

Author Comment

by:alipri
ID: 12260777
thanks - how can i send it to you?
0
 
LVL 6

Expert Comment

by:mcorrente
ID: 12261165
sorry... lunchtime hit.  Attach it to an email and email it to mike_corrente@yahoo.com
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 6

Expert Comment

by:mcorrente
ID: 12261400
alright... nowdo you want the measurements from the Order Details table or the Products table?
0
 

Author Comment

by:alipri
ID: 12261425
I think the measurements are from the Order Details table
0
 
LVL 6

Expert Comment

by:mcorrente
ID: 12261715
Alright... I'm trying very hard to figure out what you want done.  I only see data in the main columns (Width, Height, etc.).  There is no data in the other columns.
0
 
LVL 6

Expert Comment

by:mcorrente
ID: 12261986
Best I can tell is that you want the different aspects of the product (shelf, base, etc.) to be listed with their heights and widths separately, like so (the ?'s indicate where data could be but isn't, and the -'s indicate where the field is not relevent to the particular aspect):

600 Low Wall
                             Height          Width        Depth         Quantity
Whole Unit              600             575           300             -
Gable                    ?                -                ?               2
Base Bottom            -                      ?       ?       2
Base Con Pan          -            ?       ?       ?
Base Shelf                    -            ?       ?       1
Rail                    -            ?       ?       ?
Blank                    ?            ?       -       ?

etc.

And you want this for each product...
0
 
LVL 6

Expert Comment

by:mcorrente
ID: 12261994
well that fake table did not look like that when I hit enter.  Let me try again.
0
 
LVL 6

Expert Comment

by:mcorrente
ID: 12262002
600 Low Wall
            Height      Width      Depth      Quantity
Whole Unit      600      575      300      -
Gable            ?      -      ?      2
Base Bottom      -      ?      ?      2
Base Con Pan      -      ?      ?      ?
Base Shelf      -      ?      ?      1
Rail            -      ?      ?      ?
Blank            ?      ?      -      ?
Base Back      ?      ?      -      1
Base Post      -      -      -      ?
Bearer            -      -      -      ?
Std Drawer      -      ?      -      ?
Pan Drawer      -      ?      -      ?
Panel            ?      ?      -      ?
Legs            -      -      -      ?
P Clips            -      -      -      ?
Shelf Pegs      -      -      -      ?
Buffers            -      -      -      ?
H Bracket      -      -      -      2
100Hinge      -      -      -      2
180Hinge      -      -      -      0
45Hinge            -      -      -      0
30Hinge            -      -      -      0
Bifold Hinge      -      -      -      0
Hinge Plate      -      -      -      2
0
 
LVL 6

Expert Comment

by:mcorrente
ID: 12262013
well... it won't line up, but you get the point.
0
 
LVL 6

Accepted Solution

by:
mcorrente earned 500 total points
ID: 12262412
SELECT Products.ProductName, "Whole Unit" as Type, "1" as OrderField, Products.Height as Height, Products.Width as Width, Products.Depth as Depth, "-" as Quantity FROM Products WHERE Products.ProductID = [Insert Product ID]
UNION SELECT Products.ProductName, "Gable" as Type,  "2" as OrderField, Products.[Gable Height] as Height, "-" as Width, Products.[Gable Depth] as Depth, Products.[Gable Qty] as Quantity FROM Products WHERE Products.ProductID = [Insert Product ID]
UNION SELECT Products.ProductName, "Base Bottom" as Type,  "3" as OrderField, "-" as Height, Products.[Base Bottom Width] as Width, Products.[Base Bottom  Depth] as Depth, Products.[Base Qty] as Quantity FROM Products WHERE Products.ProductID = [Insert Product ID]
UNION SELECT Products.ProductName, "Base Con Pan" as Type,  "4" as OrderField, "-" as Height, Products.[Base Con Pan Width] as Width, Products.[Base Con Pan Depth] as Depth, Products.[Base Con Pan Qty] as Quantity FROM Products WHERE Products.ProductID = [Insert Product ID]
UNION SELECT Products.ProductName, "Base Shelf" as Type,  "5" as OrderField, "-" as Height, Products.[Base Shelf Width] as Width, Products.[Base Shelf Depth] as Depth, Products.[Base Shelf Qty] as Quantity FROM Products WHERE Products.ProductID = [Insert Product ID]
UNION SELECT Products.ProductName, "Rail" as Type,  "6" as OrderField, "-" as Height, Products.[Rail Width] as Width, Products.[Rail Depth] as Depth, Products.[Rail Qty] as Quantity FROM Products WHERE Products.ProductID = [Insert Product ID]
UNION SELECT Products.ProductName, "Blank" as Type,  "7" as OrderField, Products.[Blank Height] as Height, Products.[Blank Width] as Width, "-" as Depth, Products.[Blank Qty] as Quantity FROM Products WHERE Products.ProductID = [Insert Product ID]
UNION SELECT Products.ProductName, "Base Back" as Type,  "8" as OrderField, Products.[Base Back Height] as Height, Products.[Back Width] as Width, "-" as Depth, Products.[Back Qty] as Quantity FROM Products WHERE Products.ProductID = [Insert Product ID]
UNION SELECT Products.ProductName, "Base Post" as Type,  "9" as OrderField, "-" as Height, "-" as Width, "-" as Depth, Products.[Base Post Qty] as Quantity FROM Products WHERE Products.ProductID = [Insert Product ID]
UNION SELECT Products.ProductName, "Bearer" as Type,  "10" as OrderField, "-" as Height, "-" as Width, "-" as Depth, Products.[Bearer Qty] as Quantity FROM Products WHERE Products.ProductID = [Insert Product ID]
UNION SELECT Products.ProductName, "Std Drawer" as Type,  "11" as OrderField, "-" as Height, Products.[Drawer Width] as Width, "-" as Depth, Products.[Std Drawer Qty] as Quantity FROM Products WHERE Products.ProductID = [Insert Product ID]
UNION SELECT Products.ProductName, "Pan Drawer" as Type,  "12" as OrderField, "-" as Height, Products.[Drawer Width] as Width, "-" as Depth, Products.[Pan Drawer Qty] as Quantity FROM Products WHERE Products.ProductID = [Insert Product ID]
UNION SELECT Products.ProductName, "Panel" as Type,  "13" as OrderField, Products.[Panel Height] as Height, Products.[Panel Width] as Width, "-" as Depth, Products.[Panel Qty] as Quantity FROM Products WHERE Products.ProductID = [Insert Product ID]
UNION SELECT Products.ProductName, "Legs" as Type,  "14" as OrderField, "-" as Height, "-" as Width, "-" as Depth, Products.[LegsQty] as Quantity FROM Products WHERE Products.ProductID = [Insert Product ID]
UNION SELECT Products.ProductName, "P Clips" as Type,  "15" as OrderField, "-" as Height, "-" as Width, "-" as Depth, Products.[P Clips Qty] as Quantity FROM Products WHERE Products.ProductID = [Insert Product ID]
UNION SELECT Products.ProductName, "Shelf Pegs" as Type,  "16" as OrderField, "-" as Height, "-" as Width, "-" as Depth, Products.[Shelf Pegs Qty] as Quantity FROM Products WHERE Products.ProductID = [Insert Product ID]
UNION SELECT Products.ProductName, "Buffers" as Type,  "17" as OrderField, "-" as Height, "-" as Width, "-" as Depth, Products.[Buffers Qty] as Quantity FROM Products WHERE Products.ProductID = [Insert Product ID]
UNION SELECT Products.ProductName, "H Bracket" as Type,  "18" as OrderField, "-" as Height, "-" as Width, "-" as Depth, Products.[H Bracket Qty] as Quantity FROM Products WHERE Products.ProductID = [Insert Product ID]
UNION SELECT Products.ProductName, "100Hinge" as Type,  "19" as OrderField, "-" as Height, "-" as Width, "-" as Depth, Products.[100Hinge Qty] as Quantity FROM Products WHERE Products.ProductID = [Insert Product ID]
UNION SELECT Products.ProductName, "180Hinge" as Type,  "20" as OrderField, "-" as Height, "-" as Width, "-" as Depth, Products.[180Hinge Qty] as Quantity FROM Products WHERE Products.ProductID = [Insert Product ID]
UNION SELECT Products.ProductName, "45Hinge" as Type,  "21" as OrderField, "-" as Height, "-" as Width, "-" as Depth, Products.[45Hinge Qty] as Quantity FROM Products WHERE Products.ProductID = [Insert Product ID]
UNION SELECT Products.ProductName, "30Hinge" as Type,  "22" as OrderField, "-" as Height, "-" as Width, "-" as Depth, Products.[30Hinge Qty] as Quantity FROM Products WHERE Products.ProductID = [Insert Product ID]
UNION SELECT Products.ProductName, "Bifold Hinge" as Type,  "23" as OrderField, "-" as Height, "-" as Width, "-" as Depth, Products.[Bifold Hinge Qty] as Quantity FROM Products WHERE Products.ProductID = [Insert Product ID]
UNION SELECT Products.ProductName, "Hinge Plate" as Type,  "24" as OrderField, "-" as Height, "-" as Width, "-" as Depth, Products.[Hinge Plate Qty] as Quantity FROM Products WHERE Products.ProductID = [Insert Product ID]
ORDER BY OrderField
0
 
LVL 6

Expert Comment

by:mcorrente
ID: 12262417
That might be the largest query I've ever seen.
0
 
LVL 6

Expert Comment

by:mcorrente
ID: 12262476
I'm guessing that this is what you want.  It breaks each aspect of the product into it's own little record with height, width, depth, and quantity were relevant.  You won't be able to view the query in design mode cause it's too confusing.  Just paste it into SQL.  When you run it, it will pop up asking you for ProductID.  This is based off of the Products table.  Type in any valid ID, and it will pull up the aspects with the measurements for you.  You can automate this if you bind it to a report by replacing the "[Insert Product ID]" on each line with the ProductID you want to list - for example:
Forms!MyLookupForm!txtProductID.value

I hope this is what you need.  Let me know if it doesn't work.
0
 
LVL 6

Expert Comment

by:mcorrente
ID: 12262479
I need a drink...
0
 

Author Comment

by:alipri
ID: 12274763
Thanks so much, I'm just looking at the query now.
0
 
LVL 6

Expert Comment

by:mcorrente
ID: 12275778
that what you looking for?
0
 

Author Comment

by:alipri
ID: 12275889
Sorry had a meeting - the query is very useful, and I'm taking a look at how to get the report out of it at the moment. (things very hectic here!)
0
 
LVL 6

Expert Comment

by:mcorrente
ID: 12276012
I understand.  I would suggest you create a grouping level in the report based on ProductName and create a bound text box in the header that is bound to ProductName.  Then you can put all the fields except ProductName in the detail and it will come out with a list of the cut details ordered by Product.
0
 
LVL 6

Expert Comment

by:mcorrente
ID: 12276018
This of course will not be an updatable query.  You can view the data, but you can't edit or add new data.
0
 

Author Comment

by:alipri
ID: 12276082
Hey McCorrente - Here's the points, because you've more than earned them so far!

However, I'm not good enough with access to take it beyond this to the final step.  So if I set up another question - could you take me to the end?
0
 
LVL 6

Expert Comment

by:mcorrente
ID: 12277006
absolutely
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now