Subform only showing 1 record instead of all records

[drewship]

Hello all.
I have created a subform from a query based on 2 tables. The main form is
also based on the same query. I need to select a tracking number on the main
form and have all the corresponding records displayed on the subform. I then
need to use this to display cost totals on the main form. Currently, only one
record is displayed in the subform at a time. Can someone provide some
assistance?

Thanks in advance!!

 

[Dale Fye]

What does the query look like?

Not sure why you would use the same query on the main form and the subform.
Generally, the main forms recordset would only contain a single record for
each PK value, and the subform would be linked (master/child) on the PK field.

 

[drewship]

Thanks for replying Dale!!

Here is the query:

SELECT ProductTracking.ID AS ProductTracking_ID, ProductTracking.[File
Ref#], ProductTracking.[SERIAL NUMBER], ProductTracking.[FROM CUSTOMER],
ProductTracking.[DATE RECEIVED], ProductTracking.[DATE SHIPPED BACK TO
CUSTOMER], ProductTracking.[END USER RECEIVED DATE], RepairInformation.ID,
RepairInformation.PRODUCT, RepairInformation.[REPAIR TICKET #],
RepairInformation.[DATE SERVICE CLAIM FILED], RepairInformation.[SERVICE
TYPE] AS [RepairInformation_SERVICE TYPE], RepairInformation.STATUS,
RepairInformation.[COMPLETED DATE or ESTIMATED COMPLETED DATE],
RepairInformation.COST, RepairInformation.[INVOICE #], RepairInformation.POC,
RepairInformation.[POC Contact info]
FROM (ProductOptionPeriodRepair INNER JOIN RepairInformation ON
ProductOptionPeriodRepair.ID=RepairInformation.ID) INNER JOIN ProductTracking
ON RepairInformation.ID=ProductTracking.RepairInformation_ID
ORDER BY ProductTracking.[SERIAL NUMBER];

I used the same query for both the main and subform because I thought I had
to in order to get the forms to provide the necessary information.

Basically, the ProductTracking table is where I need to select a [File
Ref#], [FROM CUSTOMER], or a [SERIAL NUMBER]. Then the associated records
from the RepairInformation table would be displayed in the subform. Is there
something that can be done with the above query or can you tell me how to
create it correctly?

Thanks,
Andrew

 

[Dale Fye]

Drew,

What are the relationships (1-to-1, 1-to-many, many-to-many) between each of
these tables?

It looks like your ProductTracking table contains products that have been
returned from customers.

What is in the RepairInformation table?

What is in the ProductOptionPeriodRepair table?

As I said in my earlier post, I think I would start out with you main form
containing the data from ProductTracking, with fields in the forms header to
search (or more likely filter) for the [File Ref #], [From Customer], or
[Serial Number].

Then, I would use the [ProductOptionPeriodRepair] and [RepairInformation]
tables to create the query for the subform. Once you have those fields, you
would link the main form and subform on the RepairInformation.ID field.

----
HTH
Dale

Thanks for replying Dale!!

Here is the query:

SELECT ProductTracking.ID AS ProductTracking_ID, ProductTracking.[File
Ref#], ProductTracking.[SERIAL NUMBER], ProductTracking.[FROM CUSTOMER],
ProductTracking.[DATE RECEIVED], ProductTracking.[DATE SHIPPED BACK TO
CUSTOMER], ProductTracking.[END USER RECEIVED DATE], RepairInformation.ID,
RepairInformation.PRODUCT, RepairInformation.[REPAIR TICKET #],
RepairInformation.[DATE SERVICE CLAIM FILED], RepairInformation.[SERVICE
TYPE] AS [RepairInformation_SERVICE TYPE], RepairInformation.STATUS,
RepairInformation.[COMPLETED DATE or ESTIMATED COMPLETED DATE],
RepairInformation.COST, RepairInformation.[INVOICE #], RepairInformation.POC,
RepairInformation.[POC Contact info]
FROM (ProductOptionPeriodRepair INNER JOIN RepairInformation ON
ProductOptionPeriodRepair.ID=RepairInformation.ID) INNER JOIN ProductTracking
ON RepairInformation.ID=ProductTracking.RepairInformation_ID
ORDER BY ProductTracking.[SERIAL NUMBER];

I used the same query for both the main and subform because I thought I had
to in order to get the forms to provide the necessary information.

Basically, the ProductTracking table is where I need to select a [File
Ref#], [FROM CUSTOMER], or a [SERIAL NUMBER]. Then the associated records
from the RepairInformation table would be displayed in the subform. Is there
something that can be done with the above query or can you tell me how to
create it correctly?

Thanks,
Andrew

What does the query look like?

Not sure why you would use the same query on the main form and the subform.
Generally, the main forms recordset would only contain a single record for
each PK value, and the subform would be linked (master/child) on the PK field.

 

[drewship]

Dale,

ProductOptionPeriodRepair and RepairInformation have an undetermined
relationship and the RepairInformation has a 1 to many with the
ProductTracking table.

I think I will take your advice and start from scratch and hope I can get it
right. Seems like it will be easier in the long run. Thanks!!

Drew,

What are the relationships (1-to-1, 1-to-many, many-to-many) between each of
these tables?

It looks like your ProductTracking table contains products that have been
returned from customers.

What is in the RepairInformation table?

What is in the ProductOptionPeriodRepair table?

As I said in my earlier post, I think I would start out with you main form
containing the data from ProductTracking, with fields in the forms header to
search (or more likely filter) for the [File Ref #], [From Customer], or
[Serial Number].

Then, I would use the [ProductOptionPeriodRepair] and [RepairInformation]
tables to create the query for the subform. Once you have those fields, you
would link the main form and subform on the RepairInformation.ID field.

----
HTH
Dale

Thanks for replying Dale!!

Here is the query:

SELECT ProductTracking.ID AS ProductTracking_ID, ProductTracking.[File
Ref#], ProductTracking.[SERIAL NUMBER], ProductTracking.[FROM CUSTOMER],
ProductTracking.[DATE RECEIVED], ProductTracking.[DATE SHIPPED BACK TO
CUSTOMER], ProductTracking.[END USER RECEIVED DATE], RepairInformation.ID,
RepairInformation.PRODUCT, RepairInformation.[REPAIR TICKET #],
RepairInformation.[DATE SERVICE CLAIM FILED], RepairInformation.[SERVICE
TYPE] AS [RepairInformation_SERVICE TYPE], RepairInformation.STATUS,
RepairInformation.[COMPLETED DATE or ESTIMATED COMPLETED DATE],
RepairInformation.COST, RepairInformation.[INVOICE #], RepairInformation.POC,
RepairInformation.[POC Contact info]
FROM (ProductOptionPeriodRepair INNER JOIN RepairInformation ON
ProductOptionPeriodRepair.ID=RepairInformation.ID) INNER JOIN ProductTracking
ON RepairInformation.ID=ProductTracking.RepairInformation_ID
ORDER BY ProductTracking.[SERIAL NUMBER];

I used the same query for both the main and subform because I thought I had
to in order to get the forms to provide the necessary information.

Basically, the ProductTracking table is where I need to select a [File
Ref#], [FROM CUSTOMER], or a [SERIAL NUMBER]. Then the associated records
from the RepairInformation table would be displayed in the subform. Is there
something that can be done with the above query or can you tell me how to
create it correctly?

Thanks,
Andrew

What does the query look like?

Not sure why you would use the same query on the main form and the subform.
Generally, the main forms recordset would only contain a single record for
each PK value, and the subform would be linked (master/child) on the PK field.

----
HTH
Dale



:

Hello all.
I have created a subform from a query based on 2 tables. The main form is
also based on the same query. I need to select a tracking number on the main
form and have all the corresponding records displayed on the subform. I then
need to use this to display cost totals on the main form. Currently, only one
record is displayed in the subform at a time. Can someone provide some
assistance?

Thanks in advance!!

 

[Dale Fye]

I assume you mean that there may be zero, one, or many records in
RepairInformation for each record in ProductTracking.

Not sure what you mean regarding "undetermined" relationship between
ProductOptionPeriodRepair and RepairInformation. In your query, you show
that they are related on the ID fields of each table, but this rarely makes
sense, unless there is a one-to-one relationship, and every time you insert a
record in one, you do so in the other. Even then, it is highly unlikely you
could maintain the one-to-one relationship on an Autonumber ID field
(although you have not indicated that these are autonumber fields).

Why don't you describe to me what your intended purposes is for each of
these tables, and what data you have stored in them, and we can proceed from
there.
----
HTH
Dale

Dale,

ProductOptionPeriodRepair and RepairInformation have an undetermined
relationship and the RepairInformation has a 1 to many with the
ProductTracking table.

I think I will take your advice and start from scratch and hope I can get it
right. Seems like it will be easier in the long run. Thanks!!

Drew,

What are the relationships (1-to-1, 1-to-many, many-to-many) between each of
these tables?

It looks like your ProductTracking table contains products that have been
returned from customers.

What is in the RepairInformation table?

What is in the ProductOptionPeriodRepair table?

As I said in my earlier post, I think I would start out with you main form
containing the data from ProductTracking, with fields in the forms header to
search (or more likely filter) for the [File Ref #], [From Customer], or
[Serial Number].

Then, I would use the [ProductOptionPeriodRepair] and [RepairInformation]
tables to create the query for the subform. Once you have those fields, you
would link the main form and subform on the RepairInformation.ID field.

----
HTH
Dale

Thanks for replying Dale!!

Here is the query:

SELECT ProductTracking.ID AS ProductTracking_ID, ProductTracking.[File
Ref#], ProductTracking.[SERIAL NUMBER], ProductTracking.[FROM CUSTOMER],
ProductTracking.[DATE RECEIVED], ProductTracking.[DATE SHIPPED BACK TO
CUSTOMER], ProductTracking.[END USER RECEIVED DATE], RepairInformation.ID,
RepairInformation.PRODUCT, RepairInformation.[REPAIR TICKET #],
RepairInformation.[DATE SERVICE CLAIM FILED], RepairInformation.[SERVICE
TYPE] AS [RepairInformation_SERVICE TYPE], RepairInformation.STATUS,
RepairInformation.[COMPLETED DATE or ESTIMATED COMPLETED DATE],
RepairInformation.COST, RepairInformation.[INVOICE #], RepairInformation.POC,
RepairInformation.[POC Contact info]
FROM (ProductOptionPeriodRepair INNER JOIN RepairInformation ON
ProductOptionPeriodRepair.ID=RepairInformation.ID) INNER JOIN ProductTracking
ON RepairInformation.ID=ProductTracking.RepairInformation_ID
ORDER BY ProductTracking.[SERIAL NUMBER];

I used the same query for both the main and subform because I thought I had
to in order to get the forms to provide the necessary information.

Basically, the ProductTracking table is where I need to select a [File
Ref#], [FROM CUSTOMER], or a [SERIAL NUMBER]. Then the associated records
from the RepairInformation table would be displayed in the subform. Is there
something that can be done with the above query or can you tell me how to
create it correctly?

Thanks,
Andrew

:

What does the query look like?

Not sure why you would use the same query on the main form and the subform.
Generally, the main forms recordset would only contain a single record for
each PK value, and the subform would be linked (master/child) on the PK field.

----
HTH
Dale



:

Hello all.
I have created a subform from a query based on 2 tables. The main form is
also based on the same query. I need to select a tracking number on the main
form and have all the corresponding records displayed on the subform. I then
need to use this to display cost totals on the main form. Currently, only one
record is displayed in the subform at a time. Can someone provide some
assistance?

Thanks in advance!!

 

[drewship]

I have been able to taylor another database to do what I need. I am still
working on it but my original question is no longer applicable.

Thanks Dale.

I assume you mean that there may be zero, one, or many records in
RepairInformation for each record in ProductTracking.

Not sure what you mean regarding "undetermined" relationship between
ProductOptionPeriodRepair and RepairInformation. In your query, you show
that they are related on the ID fields of each table, but this rarely makes
sense, unless there is a one-to-one relationship, and every time you insert a
record in one, you do so in the other. Even then, it is highly unlikely you
could maintain the one-to-one relationship on an Autonumber ID field
(although you have not indicated that these are autonumber fields).

Why don't you describe to me what your intended purposes is for each of
these tables, and what data you have stored in them, and we can proceed from
there.
----
HTH
Dale

Dale,

ProductOptionPeriodRepair and RepairInformation have an undetermined
relationship and the RepairInformation has a 1 to many with the
ProductTracking table.

I think I will take your advice and start from scratch and hope I can get it
right. Seems like it will be easier in the long run. Thanks!!

Drew,

What are the relationships (1-to-1, 1-to-many, many-to-many) between each of
these tables?

It looks like your ProductTracking table contains products that have been
returned from customers.

What is in the RepairInformation table?

What is in the ProductOptionPeriodRepair table?

As I said in my earlier post, I think I would start out with you main form
containing the data from ProductTracking, with fields in the forms header to
search (or more likely filter) for the [File Ref #], [From Customer], or
[Serial Number].

Then, I would use the [ProductOptionPeriodRepair] and [RepairInformation]
tables to create the query for the subform. Once you have those fields, you
would link the main form and subform on the RepairInformation.ID field.

----
HTH
Dale



:

Thanks for replying Dale!!

Here is the query:

SELECT ProductTracking.ID AS ProductTracking_ID, ProductTracking.[File
Ref#], ProductTracking.[SERIAL NUMBER], ProductTracking.[FROM CUSTOMER],
ProductTracking.[DATE RECEIVED], ProductTracking.[DATE SHIPPED BACK TO
CUSTOMER], ProductTracking.[END USER RECEIVED DATE], RepairInformation.ID,
RepairInformation.PRODUCT, RepairInformation.[REPAIR TICKET #],
RepairInformation.[DATE SERVICE CLAIM FILED], RepairInformation.[SERVICE
TYPE] AS [RepairInformation_SERVICE TYPE], RepairInformation.STATUS,
RepairInformation.[COMPLETED DATE or ESTIMATED COMPLETED DATE],
RepairInformation.COST, RepairInformation.[INVOICE #], RepairInformation.POC,
RepairInformation.[POC Contact info]
FROM (ProductOptionPeriodRepair INNER JOIN RepairInformation ON
ProductOptionPeriodRepair.ID=RepairInformation.ID) INNER JOIN ProductTracking
ON RepairInformation.ID=ProductTracking.RepairInformation_ID
ORDER BY ProductTracking.[SERIAL NUMBER];

I used the same query for both the main and subform because I thought I had
to in order to get the forms to provide the necessary information.

Basically, the ProductTracking table is where I need to select a [File
Ref#], [FROM CUSTOMER], or a [SERIAL NUMBER]. Then the associated records
from the RepairInformation table would be displayed in the subform. Is there
something that can be done with the above query or can you tell me how to
create it correctly?

Thanks,
Andrew

:

What does the query look like?

Not sure why you would use the same query on the main form and the subform.
Generally, the main forms recordset would only contain a single record for
each PK value, and the subform would be linked (master/child) on the PK field.

----
HTH
Dale



:

Hello all.
I have created a subform from a query based on 2 tables. The main form is
also based on the same query. I need to select a tracking number on the main
form and have all the corresponding records displayed on the subform. I then
need to use this to display cost totals on the main form. Currently, only one
record is displayed in the subform at a time. Can someone provide some
assistance?

Thanks in advance!!