Pulling info from 2 tables for print layout

[stealth162]

Here's what I'm trying to do. There may be an easy way to do this, I just can't rap my head around it. Here goes.

I have two tables in one file. With a relationship setup as follows.

The primary table is set up to allow the user to create new records which signify groups. So with each new group you create a record. The new record only has two fields. Group # and Version. Both of those are user enterable.

The file that holds all the records with all the info are in another table. When you create in a new record in this table you are able to enter a number signifying the record. So you'd be able to create records. 1a01, 1a02...2a01, 2a02 etc. The first number in this sequence is used to signify which group it's a part of. The numbers following are used to signify which record of that group it is (a01, a02 etc).

Here's how it all comes together. I have a layout setup then to print information for these records. It's setup right now to sort the records based on the individual group numbers it pulls from the first digit of each records signifying number. So it sorts and puts together ther respective groups. (1a01, 1a02, 2a01, 2a02. Also setup is a subsummary in this layout that displays the group number. So there is a heading. Group 1 followed by the respective records and Group 2 followed by respective records and so on.

Sorry if this is all long winded. What I'd like to do is setup a way so that when all these records are displayed on this layout and sorted it also looks to the other table and respective record and gets that version number. So it would say "Group 1 Version 0102", "Group 2 Version 0103".

I appreciate anyone who is willing to take the time to help.

[Sanjai]

Hi,

In the primary table, I assume that the Group# and Version are composite key which means that you can have more than one version for one group eg: Group1 Version 0101, Group1 Version 0102, Group2 Version 0101, Group2 Version 0102.

If my assumption is correct then in the primary table create a calculation field: cGroupVersion= Group & " " & Version

Select the cGroupVersion field in the subsummary section based on the sort on cGroupVersion.

Let me know if my understanding is incorrect.

--Sanjai

[stealth162]

Thanks for your response. I hope you can help me figure this out. I just can't seem to get it happening.

There will only be one current version for each group in response to your reply.

The way it works is that I have this main table with groups and respective versions. When setting a version for the group you edit a value list and leave the previous values (versions in the list). Then when you go to the other table with all the records you can set a version to which it is current. So record 1a01 could be set to any of the versions in the value list. This is not to be confused with the global current version. Basically you have one current version globaly for each group and then you want to be able to set in the the other table of records which version they are current to so you know if they need updating or not to be current with the global/overall version.

[Sanjai]

Hi,

Sorry for the one day break. What I understand is:

First Table: Group, Version

Second Table: Version, RecordNo.

For eg: In first table, if you select Group1 then you select Version from the list say Version1.

In second table, you create two records a01 and a02 under Version1.

You want that the report should display:

Group1 Version1

a01

a02

Am I correct? If yes then create a relationship "Table2ToTable1ByVersion" from table2 to table1 based on Version1 field in both the tables.

Create a calculation field in table1, cGroupVersion = group & version. Turn the indexing on for this field.

In the report layout in second table, in the summary section, keep the field cGroupVersion by selecting the relationship "Table2ToTable1ByVersion". Let me know your feedback.

[stealth162]

Only thing is. The records in the second table won't always have a version on them. When it does the summary it needs to somehow look to the number before the letter and get that as the group to which it belongs and then somehow match that number up with the corresponding record on the table that has the groups and versions.

eg;

Table with records. Record named 1a01 = Group 1, Record 1

Program needs to then take that derived group number and match it to the record on the main table. Then when in a layout for printing with a summary it puts all the records from group 1 together and then lists the version number for those records.

Does that make sense?

[Sanjai]

Hi,

Yes I understood the problem.

Create a calculation field in table 2,

cGroup = left(trim(recordfield),1)

Create a relationship from Table2toTable1 by matching the cGroup field in table 2 to the GroupNo. field in table1.

Create a calculation field in Table 1, cGroupVersion = GroupNO & " Version: " & Version

In your report layout in table 2, continue what you are doing except in the subsummary section where you display the group number, select the field cGroupVersion from the relationship Table3ToTable1.

The report would be sorted based on the group number field, if the version number exists in table 1 then it would be shown else only Group number would be shown.

[stealth162]

Hi Sanjai,

I took a shot at it and somewhere along the line I got a bit lost or made a mistake. I really appreciate your help so far. I was wondering if you wouldn't mind sending me your email address througha private message and then I could send you the file so you could take a closer look. Either way I appreciate your help.