Display "Multiple" if parent contains more then 1 subrecords

[yeasir01]

Hello Community,

I have two tables (let's call Parrent & Child ) The parent contains information such as date and status of alarms, the child contains information that relates to that record via a relationship such as alarms that occurred that date.

What I am trying to accomplish is a form that displays summary information such as the following.
-Date
-Child Record = If one record exists then display that record otherwise display "Multiple Records".
-Status

I Understand this works through a query that counts the related records I just dont understand VBA to make this all work. Any help would be appreciated.

Also, for clarification I don't want to group the records, I would just like to display "Multiple" if more then one child record exists. Thanks!

 

[isladogs]

One way to do this is using DCount and a label visible only when more than one record exists with a subform for a single record
The idea would be something like this

If DCount("*","TableName",Filter criteria) > 1 Then
    Me.lblRecords.Caption="Multiple records"
    Me.lblRecords.Visible=True
    Me.subformName.Visible=False
Else
    Me.lblRecords.Visible=False
    Me.subformName.Visible=True 'show the record
End If

HTH

 

[yeasir01]

Thank you for the quick response, but I don't think that will give me the desired result. What I'm trying to achieve is summary information on one line per parent record that can be clicked open to show all the information. Is there a method for setting this up in a query? Kinda like an email app, you have a date a subject and so on when clicked you can view the content.

One way to do this is using DCount and a label visible only when more than one record exists with a subform for a single record
The idea would be something like this

If DCount("*","TableName",Filter criteria) > 1 Then
    Me.lblRecords.Caption="Multiple records"
    Me.lblRecords.Visible=True
    Me.subformName.Visible=False
Else
    Me.lblRecords.Visible=False
    Me.subformName.Visible=True 'show the record
End If

HTH

 

[Beetle]

Are you trying to create something like a Tree View?

 

[arnelgp]

Make a form.
Create a datasheet form for the child.
Create a Single form fir the parent.
Drag the child form to the parent form.
On the cild property-data, create master child link field.
Link on the common field of parent and child.
Google moreabiut linking subform to parent form for more info.

 

[yeasir01]

That is exactly what I have. The data is being pulled from a query, that is why I was asking if it can be done from there instead of the form?

Make a form.
Create a datasheet form for the child.
Create a Single form fir the parent.
Drag the child form to the parent form.
On the cild property-data, create master child link field.
Link on the common field of parent and child.
Google moreabiut linking subform to parent form for more info.

 

[yeasir01]

Sorry I don't think I'm using the right terminology, so here's a visual.

Parrent TABLE
ID DATE STATUS
1 1/1/18 Pending
2 1/2/18 Pending

CHILD TABLE
ID P_ID Warning
2 1 L5: Fuel Alaram
3 1 L6: Fuel Alarm
4 1 L7: Fuel Alarm
5 2 L5: Fuel Alarm

I would like it to be represented as followed.
1/1/18 Multiple Records Pending
1/2/18 L5: Fuel Alarm Pending

 

[Beetle]

Then you would need to do something along the lines of what Colin suggested in post #2. You could expand upon this with additional code to allow the user to click on the label, or the subform control, in order to open another form with details for the child record(s)

 

[yeasir01]

From what I see Colin recommends I use labels that gets hidden when record counts return 1?? But I Have txt boxes control, wouldn't that conflict with other data or slow performance? Is there a better way that the same thing can be achieved from within a query?

Then you would need to do something along the lines of what Colin suggested in post #2. You could expand upon this with additional code to allow the user to click on the label, or the subform control, in order to open another form with details for the child record(s)

 

[arnelgp]

Select [date], iif(dcount("*","child_table","p_id=" & id)>0,"mulitiple records pending",dlookup("warning","child_table","p_id=" & id) & " pending") as warning from parent_table;

 

[Beetle]

At the query level you would need a calculated field something like the following (this is air code so could have some typos);

IIf(DCount("*", "tblChild", "P_ID=" & [ID])>1,"Multiple Records Pending", Nz(DLookup("Warning", "tblChild", "P_ID=" & [ID]) & " Pending", "No Records Pending))

 

[Galaxiom]

Using DCount that runs for every record is not efficient. Better done with a subquery.

The data is too poorly presented to write the query.

I suspect there are normalization errors too.

 

[yeasir01]

I tried playing around with the codes & couldn't get them to work.

 

[yeasir01]

I don't have any normalization errors, once I have 10 Post I can post a pic of table structure.

 

[yeasir01]

Please see below

 

[arnelgp]

There is nothing below

 

[yeasir01]

https://drive.google.com/file/d/1Z0_DIQk9FWBIxQ7_4khg29_mHq9zwrWz/view?usp=sharing

What I need is 1 warning to be displayed from the ISD_WARNING Table if the ISD_SUBFORM_DETAILS contain one record. If it contains more than one record I would like to display "Multiple Records". Hope that clears things up.

Using DCount that runs for every record is not efficient. Better done with a subquery.

The data is too poorly presented to write the query.

I suspect there are normalization errors too.

 

[isladogs]

From what I see Colin recommends I use labels that gets hidden when record counts return 1?? But I Have txt boxes control, wouldn't that conflict with other data or slow performance? Is there a better way that the same thing can be achieved from within a query?

I suggested a label as the simplest way of displaying the text 'multiple records' and because your original explanation indicated you didn't want those records shown.
Whatever you do actually want, a label can't conflict with anything.
But it remains unclear what you do want and that's after looking at your query design.
You are now over 10 posts, so suggest you do post additional screenshots to clarify your requirements.
Please post to this forum not to external sites

 

[yeasir01]

I'm sorry, really at a loss, I don't know how to explain it any other way:banghead:. I linked an image attachment from Gdrive because this site doesn't connect or display the image. Anyways please see the additional image. The main form represents my end goal. When the magnifying glass is clicked the pop window displays all the details.

https://drive.google.com/drive/folders/1w_hy38zKyvvZaa9Gadhn-SrY6TlXtEXW?usp=sharing

I suggested a label as the simplest way of displaying the text 'multiple records' and because your original explanation indicated you didn't want those records shown.
Whatever you do actually want, a label can't conflict with anything.
But it remains unclear what you do want and that's after looking at your query design.
You are now over 10 posts, so suggest you do post additional screenshots to clarify your requirements.
Please post to this forum not to external sites

 

[yeasir01]

If a label is the only way to make this happen how would I set that up?

I suggested a label as the simplest way of displaying the text 'multiple records' and because your original explanation indicated you didn't want those records shown.
Whatever you do actually want, a label can't conflict with anything.
But it remains unclear what you do want and that's after looking at your query design.
You are now over 10 posts, so suggest you do post additional screenshots to clarify your requirements.
Please post to this forum not to external sites