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!

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

Combining multiple record data into single record

Status
Not open for further replies.
POSAPAY

POSAPAY

IS-IT--Management
Jul 27, 2001
192
HU
Hi,
I'm having some difficulty creating a SQL SELECT statement to combine multiple records into one.

Basically using two tables, call'm table1 and table2.
For each record in Table1, there are 6 records in Table2.
Table1 has a bunch of fields amongs ForecastMonth, ForecastYear (both being integers).

Table2 has few fields and each record for a record in Table1 has 6 different months of forecast marked with MonthMM, and YearYYYY.

So for example Table1.Record1 has 6 months of forecast in Table2.Record1, Table2.Record2, Table2.Record3, Table2.Record4, Table2.Record5, Table2.Record6

Would it be possible to create a SELECT statement with nested Select statements where the returned records would be like:
ReturnedRecord1.Table1.Record1.Field1
ReturnedRecord1.Table1.Record1.Field2
ReturnedRecord1.Table1.Record1.Field3
ReturnedRecord1.Table1.Record1.Field4
ReturnedRecord1.Table2.Record1.Field1
ReturnedRecord1.Table2.Record1.Field2
ReturnedRecord1.Table2.Record2.Field1
ReturnedRecord1.Table2.Record2.Field2
ReturnedRecord1.Table2.Record3.Field1
ReturnedRecord1.Table2.Record3.Field2
ReturnedRecord1.Table2.Record4.Field1
ReturnedRecord1.Table2.Record4.Field2
ReturnedRecord1.Table2.Record5.Field1
ReturnedRecord1.Table2.Record5.Field2
ReturnedRecord1.Table2.Record6.Field1
ReturnedRecord1.Table2.Record6.Field2
and then ReturnedRecord2..comes.

By the way, Table1's unique record identifier (SalesForecastID) also exists in Table2, so that is what they could be lookup on. I'm just not familiar with SQL enough to figure out how to combine it all in one statement.
 
Sort by date Sort by votes
Use UNION ALL and JOIN Tabl2 with Table1 in the selection from Tab;e2. W/o example data and desired result that is the most I could help :)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
 
Upvote 0 Downvote
Thanks for the tip, I'm just not familiar with it enough to match these up.

So here is my scenario:
Table1 name is: tblSalesForecasts
Table2 name is: tblSalesForecastsQtyData

tblSalesForecasts has fields:
SalesForecastID, ForecastMonth, ForecastYear, CustomerName, BaseModel

tblSalesForecastsQtyData has fields:
SFQD_ID, SalesForecastID, MonthMM, YearYYYY, ActualQty, ForecastQty, Probability

I need to get a table with the following columns:
tblSalesForecasts.ForecastMonth
tblSalesForecasts.ForecastYear
tblSalesForecasts.CustomerName
tblSalesForecasts.BaseModel
tblSalesForecastsQtyData.MonthMM
tblSalesForecastsQtyData.YearYYYY
tblSalesForecastsQtyData.ActualQty
tblSalesForecastsQtyData.ForecastQty
tblSalesForecastsQtyData.Probability
tblSalesForecastsQtyData.MonthMM
tblSalesForecastsQtyData.YearYYYY
tblSalesForecastsQtyData.ActualQty
tblSalesForecastsQtyData.ForecastQty
tblSalesForecastsQtyData.Probability
tblSalesForecastsQtyData.MonthMM
tblSalesForecastsQtyData.YearYYYY
tblSalesForecastsQtyData.ActualQty
tblSalesForecastsQtyData.ForecastQty
tblSalesForecastsQtyData.Probability
tblSalesForecastsQtyData.MonthMM
tblSalesForecastsQtyData.YearYYYY
tblSalesForecastsQtyData.ActualQty
tblSalesForecastsQtyData.ForecastQty
tblSalesForecastsQtyData.Probability
tblSalesForecastsQtyData.MonthMM
tblSalesForecastsQtyData.YearYYYY
tblSalesForecastsQtyData.ActualQty
tblSalesForecastsQtyData.ForecastQty
tblSalesForecastsQtyData.Probability
tblSalesForecastsQtyData.MonthMM
tblSalesForecastsQtyData.YearYYYY
tblSalesForecastsQtyData.ActualQty
tblSalesForecastsQtyData.ForecastQty
tblSalesForecastsQtyData.Probability


The last set of 5 fields repeat based on 6 different month of forecasts.
But essentially I only want to display to the user these fields:
Customer Name
Base Model
Last Month ActualQty
This Month ActualQty
Next Month ActualQty
Next Month ForecastQty
Next Month Probability
2nd Month ActualQty
2nd Month ForecastQty
2nd Month Probability
3rd Month ActualQty
3rd Month ForecastQty
3rd Month Probability
4th Month ActualQty
4th Month ForecastQty
4th Month Probability


Basically the entire report is focused around the ForecastMonth and ForecastYear variables.
The 6 different month actual or forecast data is as follows:
Last Month Actual = ForecastMonth - 1
This Month Actual = ForecastMonth (same month)
Next Month = ForecastMonth + 1
2nd Month = ForecastMonth + 2
3rd Month = ForecastMonth + 3
4th Month = ForecastMonth + 4


So this is what I have right now in a classic ASP code, where if I could reduce the number of SQL calls to a single statement, it would help a lot.

Code: 
<table>
<%
         set GetSalesForecardRcrds = Server.CreateObject("ADODB.Recordset")
         strSQL_GetSalesForecardRcrds = "SELECT * FROM tblSalesForecasts WHERE UserID LIKE '" & Request("UserID") & "' AND ForecastMonth LIKE '" & ForecastMonth & "' AND ForecastYear LIKE '" & ForecastYear & "' ORDER BY CustomerName, BaseModel"
         GetSalesForecardRcrds.Open strSQL_GetSalesForecardRcrds ,ConnObj,adOpenKeyset,adCmdTable
         If NOT GetSalesForecardRcrds.EOF then
              GetSalesForecardRcrds.MoveFirst
              do while not GetSalesForecardRcrds.EOF
               %><tr>
                   <td nowrap><%=trim(GetSalesForecardRcrds.Fields("CustomerName")) %></td>
                   <td nowrap><%=trim(GetSalesForecardRcrds.Fields("BaseModel")) %></td>

                   <%'+++ PAST MONTH +++
                     PastMonthQty = 0
                     If trim(GetSalesForecardRcrds.Fields("ForecastMonth")) > 1 then
                        Find_PM = trim(GetSalesForecardRcrds.Fields("ForecastMonth")) - 1
                        Find_PY = trim(GetSalesForecardRcrds.Fields("ForecastYear"))
                     Else
                        Find_PM = 12
                        Find_PY = (trim(GetSalesForecardRcrds.Fields("ForecastYear"))-1)
                     End if
                     set GetForecastQtyRcrd_PM = Server.CreateObject("ADODB.Recordset")
                     strSQL_GetForecastQtyRcrd_PM = "SELECT * FROM tblSalesForecastsQtyData WHERE Active = 'T' and Deleted = 'F' AND SalesForecastID = '" & trim(GetSalesForecardRcrds.Fields("SalesForecastID")) & "' AND MonthMM LIKE '" & Find_PM & "' AND YearYYYY LIKE '" & Find_PY & "'"
                     GetForecastQtyRcrd_PM.Open strSQL_GetForecastQtyRcrd_PM ,ConnObj',adOpenKeyset,adCmdTable
                     If NOT GetForecastQtyRcrd_PM.EOF then
                         PastMonthQty = GetForecastQtyRcrd_PM.Fields("ActualQty")
                     end if
                     GetForecastQtyRcrd_PM.close
                     set GetForecastQtyRcrd_PM = nothing%>
                   <td><%=PastMonthQty%></td>
                   <%'+++ CURRENT MONTH +++
                     CurrentMonthQty = 0
                        Find_CM = trim(GetSalesForecardRcrds.Fields("ForecastMonth"))
                        Find_CY = trim(GetSalesForecardRcrds.Fields("ForecastYear"))
                     set GetForecastQtyRcrd_CM = Server.CreateObject("ADODB.Recordset")
                     strSQL_GetForecastQtyRcrd_CM = "SELECT * FROM tblSalesForecastsQtyData WHERE Active = 'T' and Deleted = 'F' AND SalesForecastID = '" & trim(GetSalesForecardRcrds.Fields("SalesForecastID")) & "' AND MonthMM LIKE '" & Find_CM & "' AND YearYYYY LIKE '" & Find_CY & "'"
                     GetForecastQtyRcrd_CM.Open strSQL_GetForecastQtyRcrd_CM ,ConnObj',adOpenKeyset,adCmdTable
                     If NOT GetForecastQtyRcrd_CM.EOF then
                         CurrentMonthQty = GetForecastQtyRcrd_CM.Fields("ActualQty")
                     end if
                     GetForecastQtyRcrd_CM.close
                     set GetForecastQtyRcrd_CM = nothing%>
                   <td><%=CurrentMonthQty %></td>
                   <td width="2" bgcolor="#C0C0C0"></td>
                   
                   <%'+++ NEXT MONTH +++
                     NextMonth1QtyActual = 0
                     NextMonth1QtyForecast = 0
                     NextMonth1QtyProbability = 0
                     If trim(GetSalesForecardRcrds.Fields("ForecastMonth")) < 12 then
                        Find_NM1 = trim(GetSalesForecardRcrds.Fields("ForecastMonth"))+1
                        Find_NY1 = trim(GetSalesForecardRcrds.Fields("ForecastYear"))
                     Else
                        Find_NM1 = 1
                        Find_NY1 = (trim(GetSalesForecardRcrds.Fields("ForecastYear"))+1)
                     End if
                     set GetForecastQtyRcrd_NM1 = Server.CreateObject("ADODB.Recordset")
                     strSQL_GetForecastQtyRcrd_NM1 = "SELECT * FROM tblSalesForecastsQtyData WHERE Active = 'T' and Deleted = 'F' AND SalesForecastID = '" & trim(GetSalesForecardRcrds.Fields("SalesForecastID")) & "' AND MonthMM LIKE '" & Find_NM1 & "' AND YearYYYY LIKE '" & Find_NY1 & "'"
                     GetForecastQtyRcrd_NM1.Open strSQL_GetForecastQtyRcrd_NM1 ,ConnObj',adOpenKeyset,adCmdTable
                     If NOT GetForecastQtyRcrd_NM1.EOF then
                         NextMonth1QtyActual = GetForecastQtyRcrd_NM1.Fields("ActualQty")
                         NextMonth1QtyForecast = GetForecastQtyRcrd_NM1.Fields("ForecastQty")
                         NextMonth1QtyProbability = GetForecastQtyRcrd_NM1.Fields("Probability")
                     end if
                     GetForecastQtyRcrd_NM1.close
                     set GetForecastQtyRcrd_NM1 = nothing%>
                   <td><%=NextMonth1QtyActual %></td>
                   <td><%=NextMonth1QtyForecast %></td>
                   <td><%=NextMonth1QtyProbability %>%</td>
                   <td width="2" bgcolor="#C0C0C0"></td>
                   
                   <%'+++ NEXT MONTH 2 +++
                     NextMonth2QtyActual = 0
                     NextMonth2QtyForecast = 0
                     NextMonth2QtyProbability = 0
                     If trim(GetSalesForecardRcrds.Fields("ForecastMonth")) < 11 then
                        Find_NM2 = trim(GetSalesForecardRcrds.Fields("ForecastMonth"))+2
                        Find_NY2 = trim(GetSalesForecardRcrds.Fields("ForecastYear"))
                     Else
                        Select Case trim(GetSalesForecardRcrds.Fields("ForecastMonth"))
                        CASE "11" Find_NM2 = 1
                        CASE "12" Find_NM2 = 2
                        CASE ELSE Find_NM2 = 2
                        END SELECT
                        Find_NY2 = (trim(GetSalesForecardRcrds.Fields("ForecastYear"))+1)
                     End if
                     set GetForecastQtyRcrd_NM2 = Server.CreateObject("ADODB.Recordset")
                     strSQL_GetForecastQtyRcrd_NM2 = "SELECT * FROM tblSalesForecastsQtyData WHERE Active = 'T' and Deleted = 'F' AND SalesForecastID = '" & trim(GetSalesForecardRcrds.Fields("SalesForecastID")) & "' AND MonthMM LIKE '" & Find_NM2 & "' AND YearYYYY LIKE '" & Find_NY2 & "'"
                     GetForecastQtyRcrd_NM2.Open strSQL_GetForecastQtyRcrd_NM2 ,ConnObj',adOpenKeyset,adCmdTable
                     If NOT GetForecastQtyRcrd_NM2.EOF then
                         NextMonth2QtyActual = GetForecastQtyRcrd_NM2.Fields("ActualQty")
                         NextMonth2QtyForecast = GetForecastQtyRcrd_NM2.Fields("ForecastQty")
                         NextMonth2QtyProbability = GetForecastQtyRcrd_NM2.Fields("Probability")
                     end if
                     GetForecastQtyRcrd_NM2.close
                     set GetForecastQtyRcrd_NM2 = nothing%>
                   <td><%=NextMonth2QtyActual %></td>
                   <td><%=NextMonth2QtyForecast %></td>
                   <td><%=NextMonth2QtyProbability %>%</td>
                   <td width="2" bgcolor="#C0C0C0"></td>
                   
                   <%'+++ NEXT MONTH 3 +++
                     NextMonth3QtyActual = 0
                     NextMonth3QtyForecast = 0
                     NextMonth3QtyProbability = 0
                     If trim(GetSalesForecardRcrds.Fields("ForecastMonth")) < 10 then
                        Find_NM3 = trim(GetSalesForecardRcrds.Fields("ForecastMonth"))+3
                        Find_NY3 = trim(GetSalesForecardRcrds.Fields("ForecastYear"))
                     Else
                        Select Case trim(GetSalesForecardRcrds.Fields("ForecastMonth"))
                        CASE "10" Find_NM3 = 1
                        CASE "11" Find_NM3 = 2
                        CASE "12" Find_NM3 = 3
                        CASE ELSE Find_NM3 = 3
                        END SELECT
                        Find_NY3 = (trim(GetSalesForecardRcrds.Fields("ForecastYear"))+1)
                     End if
                     set GetForecastQtyRcrd_NM3 = Server.CreateObject("ADODB.Recordset")
                     strSQL_GetForecastQtyRcrd_NM3 = "SELECT * FROM tblSalesForecastsQtyData WHERE Active = 'T' and Deleted = 'F' AND SalesForecastID = '" & trim(GetSalesForecardRcrds.Fields("SalesForecastID")) & "' AND MonthMM LIKE '" & Find_NM3 & "' AND YearYYYY LIKE '" & Find_NY3 & "'"
                     GetForecastQtyRcrd_NM3.Open strSQL_GetForecastQtyRcrd_NM3 ,ConnObj',adOpenKeyset,adCmdTable
                     If NOT GetForecastQtyRcrd_NM3.EOF then
                         NextMonth3QtyActual = GetForecastQtyRcrd_NM3.Fields("ActualQty")
                         NextMonth3QtyForecast = GetForecastQtyRcrd_NM3.Fields("ForecastQty")
                         NextMonth3QtyProbability = GetForecastQtyRcrd_NM3.Fields("Probability")
                     end if
                     GetForecastQtyRcrd_NM3.close
                     set GetForecastQtyRcrd_NM3 = nothing%>
                   <td><%=NextMonth3QtyActual %></td>
                   <td><%=NextMonth3QtyForecast %></td>
                   <td><%=NextMonth3QtyProbability %>%</td>
                   <td width="2" bgcolor="#C0C0C0"></td>
                   
                   
                   <%'+++ NEXT MONTH 4 +++
                     NextMonth4QtyForecast = 0
                     NextMonth4QtyProbability = 0
                     If trim(GetSalesForecardRcrds.Fields("ForecastMonth")) < 9 then
                        Find_NM4 = trim(GetSalesForecardRcrds.Fields("ForecastMonth"))+4
                        Find_NY4 = trim(GetSalesForecardRcrds.Fields("ForecastYear"))
                     Else
                        Select Case trim(GetSalesForecardRcrds.Fields("ForecastMonth"))
                        CASE "9" Find_NM4 = 1
                        CASE "10" Find_NM4 = 2
                        CASE "11" Find_NM4 = 3
                        CASE "12" Find_NM4 = 4
                        CASE ELSE Find_NM4 = 4
                        END SELECT
                        Find_NY4 = (trim(GetSalesForecardRcrds.Fields("ForecastYear"))+1)
                     End if
                     set GetForecastQtyRcrd_NM4 = Server.CreateObject("ADODB.Recordset")
                     strSQL_GetForecastQtyRcrd_NM4 = "SELECT * FROM tblSalesForecastsQtyData WHERE Active = 'T' and Deleted = 'F' AND SalesForecastID = '" & trim(GetSalesForecardRcrds.Fields("SalesForecastID")) & "' AND MonthMM LIKE '" & Find_NM4 & "' AND YearYYYY LIKE '" & Find_NY4 & "'"
                     GetForecastQtyRcrd_NM4.Open strSQL_GetForecastQtyRcrd_NM4 ,ConnObj',adOpenKeyset,adCmdTable
                     If NOT GetForecastQtyRcrd_NM4.EOF then
                         'NextMonth4QtyActual = GetForecastQtyRcrd_NM4.Fields("ActualQty")
                         NextMonth4QtyForecast = GetForecastQtyRcrd_NM4.Fields("ForecastQty")
                         NextMonth4QtyProbability = GetForecastQtyRcrd_NM4.Fields("Probability")
                     end if
                     GetForecastQtyRcrd_NM4.close
                     set GetForecastQtyRcrd_NM4 = nothing%>
                   <td><%=NextMonth4QtyForecast %></td>
                   <td><%=NextMonth4QtyProbability %>%</td>
              </tr>
                <%
               GetSalesForecardRcrds.MoveNext
             loop
             %></table><%
         end if
         GetSalesForecardRcrds.close
         set GetSalesForecardRcrds = nothing
%>
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

peac3
  • Locked
  • Question
Consolidate multiple rows into single row 1
Replies
4
Views
842
Chris Miller
Chris Miller
SashaBuilder3
  • Locked
  • Question
select a single row based on conditions in multiple rows 1
Replies
3
Views
719
SashaBuilder3
SashaBuilder3
peac3
  • Locked
  • Question
split one row into multiple rows... help pls 2
Replies
12
Views
1K
Chris Miller
Chris Miller
Cpreston
  • Locked
  • Question
Group results into one row 3
2
Replies
37
Views
1K
Chris Miller
Chris Miller
WomanPro
  • Locked
  • Question
Insert into query adds records to unreadable format, how do I change that? 1
Replies
5
Views
428
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top