Showing all data related to one record in a single record

[Guest]

My main form is based off of a query that pulls all records from California.
In my subform, I want to be able to show all of the publications that
companies in California have received by company. My subform is set up from
two tables that have been linked in the subform because I need to be able to
update the tables directly from the subform.

Right now, I'm showing each publication associated with a company as an
individual record. For example I have three records for Smith Company
showing one publication per record. I would like to be able to show Smith
Company with all three publications showing in one record. How do I achieve
this?

 

[Dirk Goldgar]

My main form is based off of a query that pulls all records from
California. In my subform, I want to be able to show all of the
publications that companies in California have received by company.
My subform is set up from two tables that have been linked in the
subform because I need to be able to update the tables directly from
the subform.

Right now, I'm showing each publication associated with a company as
an individual record. For example I have three records for Smith
Company showing one publication per record. I would like to be able
to show Smith Company with all three publications showing in one
record. How do I achieve this?

There's a function on the web page below that you can use to get a
concatenated list of subrecord values:

http://www.mvps.org/access/modules/mdl0004.htm

I think that's what you need.

 

[Guest]

I looked at the function you referenced but I'm not sure it will solve my
problem. I need to be able to modify the sub-records for each record. For
example, the record for Smith Company will show Publication A, Publication B
and Publication C as separate records in the subform. These fields will have
combo boxes which will allow me to change them as needed. For example, I
need to be able to change Publication A to Publication R, and I also need to
be able to delete publications.

If I concatenate the fields, I don't think I'll be able to do that. Right?

 

[Guest]

It sounds like you need to modify the design on your subform to be a
continuous form. You can change this on the form properties setion under
format, and then you will have to change the layout for it to be properly
displayed, or you can recreate the form using the wizard and select Tabular
for the layout.

 

[Dirk Goldgar]

I looked at the function you referenced but I'm not sure it will
solve my problem. I need to be able to modify the sub-records for
each record. For example, the record for Smith Company will show
Publication A, Publication B and Publication C as separate records in
the subform. These fields will have combo boxes which will allow me
to change them as needed. For example, I need to be able to change
Publication A to Publication R, and I also need to be able to delete
publications.

If I concatenate the fields, I don't think I'll be able to do that.
Right?

Yes, that's right. I think I must not be understanding what you're
trying to achieve. What you say above sounds like a standard main
form/subform arrangement, with the subform in continuous form view. Is
it that you need to nest one subform inside another, so as to have main
form, subform, and subsubform, but both you want both subforms to be in
continuous form view? Access won't let you do that, but there are
tricks you can use to get around that. For example, you can have a main
form with two separate subforms, one of which is linked to the main
form, and one of which is linked to whatever record is current in the
first subform.

 

[Guest]

I currently have a standard main form/subform arrangement set up. The
problem is that all of the records in the the subform that are related to the
main form are showing up as individual records. For example, instead of
having one record for Smith Company in my main form, it's coming up three
times because of the subform. My main form is showing 601 total records when
I should only have 410 records. A lot of the records are duplicates because
I can't group the information in the subform.

I need to be able to update the records is the subform so my subform is
linked directly to a table. Do you still think I'd need two separate
subforms to achieve this result? My main form is based off of a query
because I only want selected companies to show in this form.

 

[Dirk Goldgar]

I currently have a standard main form/subform arrangement set up. The
problem is that all of the records in the the subform that are
related to the main form are showing up as individual records. For
example, instead of having one record for Smith Company in my main
form, it's coming up three times because of the subform. My main
form is showing 601 total records when I should only have 410
records. A lot of the records are duplicates because I can't group
the information in the subform.

I need to be able to update the records is the subform so my subform
is linked directly to a table. Do you still think I'd need two
separate subforms to achieve this result? My main form is based off
of a query because I only want selected companies to show in this
form.

I'm afraid this isn't making sense to me, Katie. Would you please
post -- in text -- the definitions of the tables involved, plus the
RecordSource properties of the main form and the subform? If the
recordsources are queries, please post the SQL of the queries.

 

[Guest]

Here is the SQL from my query. This is the query I use for my Main form.

SELECT Variable.VarID, Variable.VarName, Variable.VarDesc, LRU.LRUID,
LruType.LruTypeId, LRU.Instance, LRU.InstanceDesc, LRU.ComponentID, LRU.Zone,
LRU.Seat, LruType.ShortDesc, LruType.LongDesc, LruType.AtaChapterId,
LruType.OEMPN, EqValues.EQID, EqValues.VarID
FROM Variable INNER JOIN (((LruType INNER JOIN LRU ON
LruType.LruTypeId=LRU.LRUTypeID) INNER JOIN Equation ON
LRU.LRUID=Equation.LRUID) INNER JOIN EqValues ON Equation.EQID=EqValues.EQID)
ON Variable.VarID=EqValues.VarID
WHERE (((Variable.VarDesc) Like "*ACPDE*" Or (Variable.VarDesc) Like
"*DCPDE*"));

The main form is a query made up of the following tables: Variable, LRU,
LRU Type, Equation, and EqValues. The query pulls all LRUIDs that have a
Variable Description of ACPDE or DCPDE.

My subform is linking to the main form by LRUID and EqValues.VarID. I want
to list all VARID's for each LRUID in my subform. Right now, it's showing
one VARID for each LRU.

The subform is made up of the EqValues and Equation tables. I need to be
able to update the VarID in the EqValues table in the subform. I only need
the Equation table to link the subform back to the main form but am not
updating any records in that table.

 

[Dirk Goldgar]

Here is the SQL from my query. This is the query I use for my Main
form.

SELECT Variable.VarID, Variable.VarName, Variable.VarDesc, LRU.LRUID,
LruType.LruTypeId, LRU.Instance, LRU.InstanceDesc, LRU.ComponentID,
LRU.Zone, LRU.Seat, LruType.ShortDesc, LruType.LongDesc,
LruType.AtaChapterId, LruType.OEMPN, EqValues.EQID, EqValues.VarID
FROM Variable INNER JOIN (((LruType INNER JOIN LRU ON
LruType.LruTypeId=LRU.LRUTypeID) INNER JOIN Equation ON
LRU.LRUID=Equation.LRUID) INNER JOIN EqValues ON
Equation.EQID=EqValues.EQID) ON Variable.VarID=EqValues.VarID
WHERE (((Variable.VarDesc) Like "*ACPDE*" Or (Variable.VarDesc) Like
"*DCPDE*"));

The main form is a query made up of the following tables: Variable,
LRU, LRU Type, Equation, and EqValues. The query pulls all LRUIDs
that have a Variable Description of ACPDE or DCPDE.

My subform is linking to the main form by LRUID and EqValues.VarID.
I want to list all VARID's for each LRUID in my subform. Right now,
it's showing one VARID for each LRU.

The subform is made up of the EqValues and Equation tables. I need
to be able to update the VarID in the EqValues table in the subform.
I only need the Equation table to link the subform back to the main
form but am not updating any records in that table.

So then, all this you've been saying about Companies, Publications, and
the state of California has been just your own made-up example. In the
future, please don't do that -- it only confuses matters.

You haven't posted the table definitions as I asked, so I can only guess
at what relationships you're trying to model here. It would help if you
posted the table definitions and the relationships between them.

It appears to me that you have linked at least one too many table into
the main form's query, and probably more than one -- that would be why
you are getting more records in the main form than you think you should.
If the subform is based on a query joining Equations and EqValues, then
it makes no sense to include those tables in the main form's
recordsource query, since you're going to show them in the subform.

It also looks to me like you should not be linking the subform to the
main form by both LRUID and VarID. That would force the subform to only
ever show at most one VarID. Instead, the subform should be linked by
LRUID alone, so that all the VarIDs for that LRUID will appear on the
subform. If I was right in my conclusions above, you'll be removing
VarID from the main form anyway.

Those changes ought to improve matters.