If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Subform only showing 1 record instead of all records
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 "drewship" wrote: 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!! "Dale Fye" wrote: 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 "drewship" wrote: 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.RepairInforma tion_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" wrote: 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 "drewship" wrote: 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!! |
#12
|
|||
|
|||
Subform only showing 1 record instead of all records
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. "Dale Fye" wrote: 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 "drewship" wrote: 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!! "Dale Fye" wrote: 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 "drewship" wrote: 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.RepairInforma tion_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" wrote: 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 "drewship" wrote: 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!! |
|
Thread Tools | |
Display Modes | |
|
|