subforms How to create a multiple rows for one record?

[Charlie]

I have a record with many fields. It is the "many" record. I would like to
use it in a sub form so that the many records can be displayed on multiple
rows. This will allow the user to see the entire record with out having to
scroll. It would make for easier inputting and we will only have two-three
records in the one-many relationship.

Thanks.

 

[Ken Snell \(MVP\)]

If the datatypes of the many fields are conducive to this, you could write a
UNION query that gets the first 6 fields (for example) for a row, then the
next 6 fields for a second row, etc. and then use that UNION query as the
RecordSource query for the subform. However, in order to do this, all the
fields that are in the "first column" must be of the same datatype, all the
fields in the "second column" must be of the same datatype, etc.

Otherwise, you'll need to rethink how you want to do the display. Perhaps
use tab pages to show the different fields?

 

[Larry Linson]

I am not certain what you mean by "it is the 'many' record". One-to-many is
a relationship between tables, not between two records. It sounds as if you
thought there is a "one" record and then a "many" record with "many" fields
of identical definition. That is not relational design, it's not
one-to-many, and it is not how Access (or any relational database) is
intended to work.

If you don't redesign, according to relational rules, sooner or later (most
likely far sooner that you believe), you'll rue the decision not to do so.

In a one-to-many relationship you have a table on the "one" side... with one
record per "whatever the subject is", and you have another table, with
"multiple" records related to the records in the one table by a foreign key
that identifies the one record's subject. For example, in a business you
have a Customers Table that has a record for each of your Customer
Companies, each identified by a CustomerID; related to that you have a
Contacts Table with information about People who work for those companies
(one record per person)... each record identified by a Contact ID, and
related to the Customers Table by a foreign key containing the value of a
Customer ID in the Customers Table.

To obtain a list of a Company and all its Contacts, you would create a
Query, which would return the requested information from both Customers and
Contacts, one record per Contact. Believe me when I say that will be far,
far easier to work with than a single Contacts Record per Company containing
many Contacts.

Larry Linson
Microsoft Office Access MVP