Display 'many' record field horizontally 3

[Darrylles]

Hi,

I have a 1 to many table relationship.
[tt]
tbl1 tbl2
part# --->> part#
desc op#
op_date
[/tt]

I want to display the data like this:

tbl1.part# tbl1.desc tbl2.rec1.op_date tbl2.rec2.op_date
etc etc. (i.e. the 'many' record fields displayed on one line.
A pointer to how to do this will be very much appreciated.

One further point, each part# belongs to a 'category' of parts, whereby each part from a particular category has a pre-set number of ops and op descriptions.

I would assume that I would have to group by category in order for the op. numbers (and op# titles to tally) for each category of parts.

Can anyone point me in the right direction?

Regards,

Darrylle



Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk

 

[cityboy3]

I don't really understand your second question about categories, but I'll give your first question a shot.

1) One method is to base your report on the records in Table 1. Then write code attached to the OnFormat property of your detail section to: a)Find the related records in Table 2, and b)loop through them while positioning them on the page to print.

If you're not comfortable with the coding for this, try the following method:
2) Create a main report based on Table 1 and a subreport based on Table 2. In the subreport, format it so that it displays multiple columns (the number of columns depends on your data and the page layout). You should be able to preview this report on its own to tweak the display. Then drag the subreport onto your main report and make sure the link parent and child properties are set correctly. Size and position the subreport control to get the desired result.

I've used Method 2 on more than one occasion and it's worked out pretty well. You just need to play with the columns and margins for a bit.

good luck

 

[Darrylles]

Hi city,

Thanks for your response.

The sub-report that you mention above is my dilemma.

How do I show say 5 different records on one row in the sub-report?

i.e. the part# may have ops 10,20,30,40 & 50 (5 seperate records in tblOps, each with a date for the operation). I want to see these ops with their dates, displayed horizontally on one row across the subform.

Can you clarify how to do this?

Many thanks,

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk

 

[RoyVidar]

Have a look at dhookom's faq faq701-4233, which I think is also downloadable from Other Developer's Libraries. Even though it's supposed only to return one item from the child table, I think you should be able to use/tweak it.

Roy-Vidar

 

[Darrylles]

Hi Roy,

Thanks, but, (there's ALWAYS a but), I try not to use vba recordsets due to additional library requirement (and MS Access version library variation).

This method was always an option - much more versatile.
If I must do it this way, then I will, and compile the application, but there MUST be a standard low-level (or high-level dependant on your view-point)way of doing this.

Thank you both for your time, and I'll 'star' you both whichever way I go.

Regards,

Darrylle




Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk

 

[cityboy3]

If you want to show 5 records on one row in your subreport, you'll need to create a 5-column layout.

Let's say each field can fit in one inch of space horizontally. Create a report with just a detail section. Drag your data field there and adjust the right margin so that it's at the one-inch marker. If you preview the report now, you'll see one long column with all your data for all part numbers. Now, go to File -> Page Setup. Type 5 in the Columns tab, set any other Margins and space between columns, and make sure that you've selected Across, Then Down for the Column Layout. Preview it now and you'll see a 5-column list of all your part numbers.

When you drop this subreport into a main report, linked to the Part Number Field, then for each Part Number in the main report, you should see up to 5 columns of your data from Table 2 that correspond to that part number. If it goes past 5 columns, make sure you set the CanGrow options properly so that you get an additional line.

let me know if it works.

 

[Darrylles]

City,

Sorry, I'm lost.

I've created a new report. It has an header, detail and footer.
(Must I remove header / footer? If so how?)

I've dragged the data field to the detail section and set it's left edge to zero and right edge to '1'.

Running the report displays 5 rows of data (as expected).

Why should it add all 5 records to one data field?

(Sorry if I'm missing something obvious).

Regards,

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk

 

[Darrylles]

City,

Gotcha.

Thanks - this has resolved many questions.

ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk

 

[Darrylles]

Hi again,

All of the above works as expected - no prob.

Is it possible however to refer to the properties of the single painted textbox dependant on it's displayed content?

i.e. if the date is past, then turn the forecolor or background to red?

I doubt it, but would appreciate a yes/no.

Thanks again,

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk

 

[cityboy3]

You can. Search the help for "conditional formatting"

good luck