Mater - Detail Report

[Chicagodude]

I have one master and one detial table.
Master table
Part Info:
Part#
Part Desc
Date MFG

Detail table
Part Specification (one to many relation)
Part#
Specification ID
SPecification Desc
Spec.

Data on table
Master
1 - Pen - 11/11/2007
2 - Rubber - 11/1/2008
3 - Roller - 1/1/2008

Detail
1 - 100 - Length - 2.90
1 - 200 - Diameter - 3.00
1 - 700 - Grove - .5
2 - 400 - Width - 4.24
2 - 500 - height - .89

I want a create master -detail report in Access database in this way. How can i do that.

Report layout

Part No Part Desc Date MFG
1 Pen 11/11/2007

Specification ID 100 200 700
Specification Desc Length Diameter Grove
Spec. 2.90 3.00 .50


Same think for part# 2. Basically i need detail record in column base not row base.

Thanks

 

[fneily]

Should post in either:
Microsoft: Access Reports or
Microsoft: Access Queries and JET SQL Because you have to set your data in a query first.
You'll look at:
faq701-4233

First, correct your Part Specification table. All tables must have a primary key. So
tblPartSpecifications
PSID Primary Key
Part# Foreign key
Specification ID
SPecification Desc
Spec.

You'll create a query connecting tblParts to tblPartSpecifications on Part#

This'll give you:
Part_No Part_Desc Date_MFG Spec_ID Spec_Desc Spec.
1 Pen 11/11/2007 100 Length 2.90
1 Pen 11/11/2007 200 Diameter 3.00
etc.

You'll then concatenate the children records as in the reference. This'll produce one record for that part. Then just feed this to a report placing the controls appropriately.

 

[LisaWay]

It's possible to do what you're asking, but have you considered that your plan will not allow an unlimited number of detail records per part? You will be limited by the horizontal space available on a piece of paper. If that's what you really want to do, what is the maximum number of detail records you will allow per part?

Regards,
Lisa
lets-clean-up.com

 

[fneily]

Good point, Lisa. So if he has 175 specification ID's for a part he'll need to repeat that block of row headings. It'll look sloppy and usually hard to read.
But it's their call.

 

[Chicagodude]

Thanks for your input. I created query which will give me result from master and detail table like you said. I don't get it after that. How can i create one record for each part and how to display the resuls in the way i want a see. Each part has maximum 10 specification so report will not has more then 10 columns.

 

[fneily]

You should post your query output.
You should have a record that looks like:
1 Pen 11/11/2007 100 Length 2.90 200 Diameter 3.00 etc.

If you don't, then you didn't concatenate correctly. You'll have to fix the code.

Then you create a blank report and place your controls appropriately.

 

[MajP]

I would do it a little differently. Assume this is my detail table.

tblSpecs

partID_FK	specName	specValue
1	length	1.01
1	diameter	2.02
1	grove	0.03567
2	length	45.098
2	width	123
2	height	456.789
3	Bore diameter	23.098
3	length	1111.55
3	Volume	23

I would then build a crosstab query

TRANSFORM First(tblSpecs.specValue) AS FirstOfspecValue
SELECT tblSpecs.partID_FK
FROM tblSpecs
GROUP BY tblSpecs.partID_FK
PIVOT tblSpecs.specName;

You then get something that looks like

partID_FK	Bore diameter	diameter	grove	height	length	Volume	width
1		2.02	0.03567		1.01		
2				456.789	45.098		123
3	23.098				1111.55	23

with values in the fields that have a value for that record.

then build a function that returns a string that lists the labels and the values aligned underneath

Public Function getSpecText(lngID As Long) As String
  Dim rs As DAO.Recordset
  Dim fld As DAO.Field
  Dim nameLength As Integer
  Dim valueLength As Integer
  Dim strValue As String
  Dim strValues As String
  Dim strLabel As String
  Dim strLabels As String
  Static intSpaces As Integer
  
  intSpaces = 4
  Set rs = CurrentDb.OpenRecordset("Select * from xtbQrySpecs where partID_FK = " & lngID, dbOpenDynaset)
  For Each fld In rs.Fields
    If (Not Trim(fld.Value & " ") = "") And (Not fld.Name = "partID_FK") Then
       strLabel = fld.Name
       strValue = CStr(fld.Value)
       nameLength = Len(fld.Name)
       valueLength = Len(strValue)
       If nameLength > valueLength Then
         strLabels = strLabels & strLabel & Space(intSpaces)
         strValues = strValues & strValue & Space(nameLength - Len(strValue) + intSpaces + 2)
       Else
         strLabels = strLabels & strLabel & Space(valueLength - Len(strLabel) + intSpaces + 2)
         strValues = strValues & strValue & Space(intSpaces)
       End If
     End If
  Next fld
  getSpecText = strLabels & vbCrLf & strValues
End Function

This returns a string for each partID that looks something like

diameter    grove      length    
2.02        0.03567    1.01

Now you can use the function in a query something like

SELECT tblParts.partName, getSpecText([partID]) AS SpecDetails
FROM tblParts;

Now you can use the field SpecDetails as a long memo box for each record.

Only draw back is you have to use a proportional font for this field such as courier new.

 

[MajP]

remove the "+2" in the following

         strValues = strValues & strValue & Space(nameLength - Len(strValue) + intSpaces + 2)
       Else
         strLabels = strLabels & strLabel & Space(valueLength - Len(strLabel) + intSpaces + 2)

 

[fneily]

MajP - as I understand it from his first post, a part has multiple length, diameter, grove, etc. depending on the SpecID.

 

[MajP]

Could be, his example does not suggest that. Will need the OP to provide a better example if that is the case.

 

[fneily]

No. I think your right. For some reason my brain keeps seeing multiples. Strange.