Complex RecordSource

[Bill]

I have an application that supports class
reunions. Within each classmate's record,
there are fields of information that potentially
lead to a situation where four (4) different
name tags need to be sequentially printed.

I can approach this situation by writing a
general module that will create additional
tables that I can subsequently Union Select
to create the report's RecordSource. But,
if it's possible to dynamically create such
records "on the fly" within the report's code
sheet at runtime, I would prefer to do that.

For example: (1 record)
LName FName CurName SPName ChCM ChSP
Smith Carol Brown Bill 1 3

Expand to 4 sequential name tags: (Appropriately
formatted in the OnFormat sub.)

1 - Carol (Smith) Brown
2 - Prime Rib
3 - Bill Brown
4 - Chicken

There's some code required to recognize that
when there's a female classmate, her spouses
name tag needs to use "CurentName" (CurName)
as the last name on the name tag of the spouse.

So, the question is really: Can one record in the
RecordSource be made to create 4 report pages?
Or, is there enough facility in SQL to create a
query wherein 4 discrete records be created
that correspond to what I've shown in the above
example? If the latter, can you offer some help
in getting such a query started?

(In this case, one page is a business card size
name tag.)

 

[KARL DEWEY]

Try this using your table name --
SELECT 1 AS Row, IIf([CurName] Is Null,[FName] & " " & [LName],[FName] & "
(" & [LName] & ") " & [CurName]) AS Name
FROM Bill
UNION ALL SELECT 3 AS Row, IIf([CurName] Is Null,[SPName] & " " &
[LName],[SPName] & " " & [CurName]) AS Name
FROM Bill;

You need to join your menu table to the query to display their selections.

 

[Bill]

Karl,
I give this a try later today (California, USA) and post back.
Thanks,
Bill

Try this using your table name --
SELECT 1 AS Row, IIf([CurName] Is Null,[FName] & " " & [LName],[FName] & "
(" & [LName] & ") " & [CurName]) AS Name
FROM Bill
UNION ALL SELECT 3 AS Row, IIf([CurName] Is Null,[SPName] & " " &
[LName],[SPName] & " " & [CurName]) AS Name
FROM Bill;

You need to join your menu table to the query to display their selections.
--
KARL DEWEY
Build a little - Test a little

I have an application that supports class
reunions. Within each classmate's record,
there are fields of information that potentially
lead to a situation where four (4) different
name tags need to be sequentially printed.

I can approach this situation by writing a
general module that will create additional
tables that I can subsequently Union Select
to create the report's RecordSource. But,
if it's possible to dynamically create such
records "on the fly" within the report's code
sheet at runtime, I would prefer to do that.

For example: (1 record)
LName FName CurName SPName ChCM ChSP
Smith Carol Brown Bill 1 3

Expand to 4 sequential name tags: (Appropriately
formatted in the OnFormat sub.)

1 - Carol (Smith) Brown
2 - Prime Rib
3 - Bill Brown
4 - Chicken

There's some code required to recognize that
when there's a female classmate, her spouses
name tag needs to use "CurentName" (CurName)
as the last name on the name tag of the spouse.

So, the question is really: Can one record in the
RecordSource be made to create 4 report pages?
Or, is there enough facility in SQL to create a
query wherein 4 discrete records be created
that correspond to what I've shown in the above
example? If the latter, can you offer some help
in getting such a query started?

(In this case, one page is a business card size
name tag.)

 

[Bill]

I'm trying to understand your use of the term "Row" and the
numerical prefix I see, e.g., "1 AS Row" and "3 AS Row".
I don't see any reference to such expressions anywhere in
the SQL Help file.

Can you explain that a bit please?

Thanks,
Bill

Try this using your table name --
SELECT 1 AS Row, IIf([CurName] Is Null,[FName] & " " & [LName],[FName] & "
(" & [LName] & ") " & [CurName]) AS Name
FROM Bill
UNION ALL SELECT 3 AS Row, IIf([CurName] Is Null,[SPName] & " " &
[LName],[SPName] & " " & [CurName]) AS Name
FROM Bill;

You need to join your menu table to the query to display their selections.
--
KARL DEWEY
Build a little - Test a little

I have an application that supports class
reunions. Within each classmate's record,
there are fields of information that potentially
lead to a situation where four (4) different
name tags need to be sequentially printed.

I can approach this situation by writing a
general module that will create additional
tables that I can subsequently Union Select
to create the report's RecordSource. But,
if it's possible to dynamically create such
records "on the fly" within the report's code
sheet at runtime, I would prefer to do that.

For example: (1 record)
LName FName CurName SPName ChCM ChSP
Smith Carol Brown Bill 1 3

Expand to 4 sequential name tags: (Appropriately
formatted in the OnFormat sub.)

1 - Carol (Smith) Brown
2 - Prime Rib
3 - Bill Brown
4 - Chicken

There's some code required to recognize that
when there's a female classmate, her spouses
name tag needs to use "CurentName" (CurName)
as the last name on the name tag of the spouse.

So, the question is really: Can one record in the
RecordSource be made to create 4 report pages?
Or, is there enough facility in SQL to create a
query wherein 4 discrete records be created
that correspond to what I've shown in the above
example? If the latter, can you offer some help
in getting such a query started?

(In this case, one page is a business card size
name tag.)

 

[Bill]

DUH!!! (I've just never used that before)

The SQL didn't quite return what was needed. I think if I
worked at it for awhile, I could get the query to return
the records I need already deciphered. However, because
of the SQL syntax nightmare, I can more quickly create
the "ordered pairs" of records in the correct order and
have the Detail_OnFormat perform the logic required
to manipulate the controls appropriate to the "type" of
record returned by the query.

Your suggestion certainly got me going in the right
direction, even though I didn't use the embedded logic
you used in forming the "Name".

Thanks much,
Bill

I'm trying to understand your use of the term "Row" and the
numerical prefix I see, e.g., "1 AS Row" and "3 AS Row".
I don't see any reference to such expressions anywhere in
the SQL Help file.

Can you explain that a bit please?

Thanks,
Bill

Try this using your table name --
SELECT 1 AS Row, IIf([CurName] Is Null,[FName] & " " & [LName],[FName] &
"
(" & [LName] & ") " & [CurName]) AS Name
FROM Bill
UNION ALL SELECT 3 AS Row, IIf([CurName] Is Null,[SPName] & " " &
[LName],[SPName] & " " & [CurName]) AS Name
FROM Bill;

You need to join your menu table to the query to display their
selections.
--
KARL DEWEY
Build a little - Test a little

I have an application that supports class
reunions. Within each classmate's record,
there are fields of information that potentially
lead to a situation where four (4) different
name tags need to be sequentially printed.

I can approach this situation by writing a
general module that will create additional
tables that I can subsequently Union Select
to create the report's RecordSource. But,
if it's possible to dynamically create such
records "on the fly" within the report's code
sheet at runtime, I would prefer to do that.

For example: (1 record)
LName FName CurName SPName ChCM ChSP
Smith Carol Brown Bill 1 3

Expand to 4 sequential name tags: (Appropriately
formatted in the OnFormat sub.)

1 - Carol (Smith) Brown
2 - Prime Rib
3 - Bill Brown
4 - Chicken

There's some code required to recognize that
when there's a female classmate, her spouses
name tag needs to use "CurentName" (CurName)
as the last name on the name tag of the spouse.

So, the question is really: Can one record in the
RecordSource be made to create 4 report pages?
Or, is there enough facility in SQL to create a
query wherein 4 discrete records be created
that correspond to what I've shown in the above
example? If the latter, can you offer some help
in getting such a query started?

(In this case, one page is a business card size
name tag.)