Split single field into multiple fieldss based on combined key

[RobXaos]

Access: 2003/2007 OS: XP SP3

My Background:
I am fairly experienced with Excel, including writing in VBA, but a somewhat of a novice with Access, SQL and the exact syntax of VBA for Access.

Problem Background:
I have a database that contains many multiple sets of data that will be plotted to charts. A chart will consist of 4 series selected by the user from the database. I believe have almost all the structure (tables and relationships) complete, and entering sample data seems to work as I expect. However if anyone points out ways to change the table structures then I am not adverse to taking advice.

The lowest level table (tblData_FPS) that holds all the chart data consists of 3 columns: Data_Set_FPS_ID, Time_ID, FPS

Data_Set_FPS_ID and Time_ID are used as a combined key

Time_ID is a series of consecutive sequential numbers; its values are non-unique within the table, but unique for a given Data_Set_FPS_ID.

Data_Set_FPS_ID comes from from tblData_Sets_FPS via the simple lookup: SELECT tblData_Sets_FPS.ID FROM tblData_Sets_FPS;

tblData_Sets_FPS is just a series of IDs via similar simple lookup relationship to other tables in the database such that selecting various parameters uniquely defines tblData_Sets_FPS.ID

To create my chart I now need to select 4 Data Sets from tblData_FPS simply by choosing 4Data_Set_FPS_IDs. I think the easiest way to do this is to construct a Query that produces a table with the following columns: Time_ID, Series1, Series2, Series3, Series4. Such a query can be assigned directly to the Chart RowSource property.

Problem:
As yet I have not been able to write a query to split my tblData_FPS into 5 columns based on 4 parameters. I have only found a way using multiple queries, which seems in-elegant and I feel sure is not the best way. However to illustrate what does produce the result I want, here are the queries:

qrySeries1:
PARAMETERS S1 Long;
SELECT Time_ID, FPS AS Series1
FROM tblData_FPS
WHERE Data_Set_FPS_ID=[S1];
...
qrySeries4:
PARAMETERS S4 Long;
SELECT Time_ID, FPS AS Series4
FROM tblData_FPS
WHERE Data_Set_FPS_ID=[S4];

Then the following produces a table that can be assigned to my chart:

qryAll_Series:
SELECT qrySeries1.*, qrySeries2.Series2, qrySeries3.Series3, qrySeries4.Series4
FROM qrySeries1, qrySeries2, qrySeries3, qrySeries4
WHERE ((qrySeries1.Time_ID=qrySeries2.Time_ID) And (qrySeries1.Time_ID=qrySeries3.Time_ID) And (qrySeries1.Time_ID=qrySeries4.Time_ID));

This was previously posted at:

http://social.answers.microsoft.com/Forums/en-US/addbuz/thread/d4cfdbff-c062-47db-b052-ac4ac1674f07

Reposting here as no response received.

 

[PHV]

What about this ?

PARAMETERS S1 Long, S2 Long, S3 Long, S4 Long;
SELECT F1.Time_ID, F1.FPS AS Series1, F2.FPS AS Series2, F3.FPS AS Series3, F4.FPS AS Series4
FROM ((tblData_FPS F1
INNER JOIN tblData_FPS F2 ON F1.Time_ID = F2.Time_ID)
INNER JOIN tblData_FPS F3 ON F1.Time_ID = F3.Time_ID)
INNER JOIN tblData_FPS F4 ON F1.Time_ID = F4.Time_ID
WHERE F1.Data_Set_FPS_ID=[S1] AND F2.Data_Set_FPS_ID=[S2] AND F3.Data_Set_FPS_ID=[S3] AND F4.Data_Set_FPS_ID=[S4]

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

 

[RobXaos]

Absolutely spot on. Thank you very much.

Since I'm still very new to SQL would you have the time to explain the syntax and how it actually works?