Have blank fields in query on purpose

Question

I have a Microsoft Access database with the following table:

TableName: Parts
Cabinet ID - Number
Part ID - Number
PartClass - Number
Width - Number (double)
Length - Number (double)
Description - Text

I use the following SQL queries to get three views:

DoorPanels:
SELECT Parts.[Cabinet ID], Count(Parts.[Cabinet ID]) as Qty, Parts.Description as Name Parts.Width, Parts.Length 
FROM Parts
WHERE PartClass=13 GROUP BY Parts.[Cabinet ID], Parts.Description, Parts.Width, Parts.Length
ORDER BY Parts.[Cabinet ID]

DoorRails:
SELECT Parts.[Cabinet ID], Count(Parts.[Cabinet ID]) as Qty, Parts.Description as Name Parts.Width, Parts.Length 
FROM Parts
WHERE PartClass=14 GROUP BY Parts.[Cabinet ID], Parts.Description, Parts.Width, Parts.Length
ORDER BY Parts.[Cabinet ID]

DoorStiles:
SELECT Parts.[Cabinet ID], Count(Parts.[Cabinet ID]) as Qty, Parts.Description as Name Parts.Width, Parts.Length 
FROM Parts
WHERE PartClass=15 GROUP BY Parts.[Cabinet ID], Parts.Description, Parts.Width, Parts.Length
ORDER BY Parts.[Cabinet ID]

This gives me three separate views that hold only the parts that I want. Now, I have an additional table:

TableName: Doors
Cabinet ID - Number
Door ID - Number
Width - Number (double)
Height - Number (double)

I use another query to create a view for this as well:

SELECT Doors.[Cabinet ID], Count(Doors.[Door ID]) as Qty FROM Doors GROUP BY Doors.[Cabinet ID] ORDER BY Doors.[Cabinet ID]

So, finally I want to join all of these together...but this is where I am at a bit of a loss. What I need to get, would be a result like this, from the data that I have retrieved:

Doors.[Cabinet ID]    Doors.Qty    DoorRails.*    DoorStiles.*  DoorPanels*
1                     2            [data]         [data]        [data]
                                   [data]         [data]        [data]
2                     3            [data]         [data]        [data]
                                   [data]         [data]        [data]
                                   [data]         [data]        [data]

Now, to decipher what I am talking about in the result above, you can see that for [Cabinet ID] 1, I have a door quantity of 2, so I need 2 records for the parts for that door. [Cabinet ID] 2 has 3 doors, so I need 3 records for all the door parts for that.

Now, I understand that this is a little complicated, and possibly...well...impossible, but I have been working on this for a few days and haven't come up with anything. If it's not possible, I would appreciate the experts here telling me so.

Just for FYI - I can't make ANY changes to the database or the structure, all that I can do is run Queries against it.

Answer 1

I think the easiest way to do this would be via a Cartesian join to a table that simply has rows with a single field incrementing from 1 to the maximum number of doors you will ever need. For example,

Counter.Num
-----------
1
2
3

If you cannot even create local tables for your own use, you could simulate the above using a UNION query. Access has some limitations here, so you'll need to reference an existing table even though you won't use any of its rows. For example,

SELECT TOP 1 1 AS Num FROM msysobjects
UNION ALL
SELECT TOP 1 2 AS Num FROM msysobjects
UNION ALL
SELECT TOP 1 3 AS Num FROM msysobjects

Then use this Counter table/query as follows:

SELECT D.[Cabinet ID], C.Num & " of " & Count(D.[Door ID]) AS [Door Number], 
       DR.*, DS.*, DP.* 
FROM Counter AS C, 
     ((Doors AS D INNER JOIN DoorRails AS DR ON D.CabinetID=DR.CabinetID)
                  INNER JOIN DoorStiles AS DS ON D.CabinetID=DS.CabinetID)
                  INNER JOIN DoorPanels AS DP ON D.CabinetID=DP.CabinetID
WHERE C.Num <= Count(D.[Door ID])
GROUP BY D.[Cabinet ID] 
ORDER BY D.[Cabinet ID], C.Num

This will be a slightly different result than your sample. The difference is the Cabinet ID will be repeated for each door. Since the quantity would also be repeated, I substituted the door number instead (in the form of "1 of 2", "2 of 2", etc.).