Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Wanet Telecoms Ltd on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Fill in parent information on a one to many relationship query

Status
Not open for further replies.

stv0505

Technical User
Sep 7, 2007
12
US
I have a one to many relationship between multiple tables in Access. In one particular query I am pulling data from the parent table where the value of Results_ID_start is the direct relationship to Results_ID in the Results table. When I query the two tables I do a one to many relationship that allows me to have the file name, date, etc. of the parent table linked to the series of results in the Results table. The problem is that I have one line in the Query that has the parent information linked to the start of the results series and I want the parent information to be displayed on every line for that series. Is there a way to do this?

Again, the filename and date in the Query are only associated with the first Results_ID that matches the Results_ID_start in the parent table. So periodically I have three lines of data that have empty cells for the filename and date.

I know in Excel I can sort on blank cells in a column and then type in the expression =[uparrow, shift ctrl enter] to populate the cells with the information from the cell above.

Any suggestions?


Thanks in advance.
Steve
 
Is there a way to do this?
Is there a way to do WHAT ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
PH,
Sorry for the confusion. I may not know exactly how to ask the question. I don't know if I need to populate blank cells with the value of the cell above the blank cell or if I need to find out how to link a row of parent information to a series of child information when the relationship between the parent and child is only for the first data point in a seris.

I hope this helps.

I have two tables; a Parent table called measurement that gives the filename, Date and ID_Start of a measurement. The Child table gives the Results_ID and the Average of the measurement. For every filename and date there could be up to 4 different Results_IDs and Averages displayed. The relationship between my parent table and my results table is the ID_Start number. The ID_Start is always the first Results_ID for the series of data that correspond to a specific filename and Date.

When I perform a Query I can join the data using a one to many relationship that groups the data such that the series of data for a particular filename and Date are together.

For instance, my data in the Query looks like this:

Filename Date ID_Start Results_ID Average
XYZ 9/5/2007 4 4 20
_blank _blank _blank 5 10
_blank _blank _blank 6 50
_blank _blank _blank 7 40
ABC 9/9/2007 8 8 60
LMN 9/9/2007 9 9 14
_blank _blank _blank 10 88
_blank _blank _blank 11 55
_blank _blank _blank 12 44

Note: _blank is an actual empty cell in my Query, I am using _blank just to make sure my columns line up in the text box.

The Filename, Date and ID_Start come from the parent table called Measurement. The ID_Start is the first Results_ID number given to a series, usually 4 results in the series of data with sequential Results_IDs.

The relationship between the Parent and Child tables is the ID_Start value. But I need all of the Results_ID to have a filename and Date associated with them in the Query.

Is there a way to replace the empty (_blank) cells in my Query with the Filename and Date information from the cells above the blank cells? Or how can I draw the relationship between my Measurement and Results tables so that I can have my Query display the filename and Date of the entire series of Results_IDs?

An Example of what I want to display in my Query is:

Filename Date ID_Start Results_ID Average
XYZ 9/5/2001 4 4 20
XYZ 9/5/2001 4 5 10
XYZ 9/5/2001 4 6 50
XYZ 9/5/2001 4 7 40
ABC 9/9/2007 8 8 60
LMN 9/9/2007 9 9 14
LMN 9/9/2007 9 10 88
LMN 9/9/2007 9 11 55
LMN 9/9/2007 9 12 44

I hope that I've explained it better.

Again, sorry for the not so clear question initially.

Steve
 
What is your actual SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
I'm pretty new at Access and Database development so I hope I got the SQL code correctly for you. I opened My Query in question and I selected View>SQLView.

Here is the SQL code from my Query:

SELECT RPM_Measurement.Date, RPM_Measurement.Filename, RPM_Measurement.Wafer_ID, RPM_Measurement.First_Result, RPM_Results.Results_ID, RPM_Results.Meas_Type, RPM_Results.Data_Type, RPM_Results.Average, RPM_Results.Std_Dev, RPM_Results.Median, RPM_Results.Min_Value, RPM_Results.Max_Value, RPM_Results.[10perc_cutoff], RPM_Results.[25perc_cutoff], RPM_Results.[75perc_cutoff], RPM_Results.[90perc_cutoff]
FROM RPM_Results LEFT JOIN RPM_Measurement ON RPM_Results.Results_ID = RPM_Measurement.First_Result;

Thanks, Steve
 
A starting point:
SELECT A.Date, A.Filename, B.ID_Start, B.Results_ID, B.Average
FROM RPM_Measurement AS A INNER JOIN (
SELECT Max(M.First_Result) AS ID_Start, R.Results_ID, R.Average
FROM RPM_Results AS R INNER JOIN RPM_Measurement AS M ON R.Results_ID >= M.First_Result
GROUP BY R.Results_ID, R.Average
) AS B ON A.First_Result = B.ID_Start

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
PH,

Thanks! I'll try it and let you know.

Steve
 
Thanks PH. I couldn't get what I was looking for in your suggestion so I configured my parent table to have a starting ID and a final ID. In my query I use a Left Outer Join with a between starting ID And final ID. Works great. Thanks again for your help!
STV
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top