multiple fields in separate records

[Fredo0709]

Hi everyone... I'm new to the forum. I have a problem with sorting records in an existing table.

Each record in the table has vibration data in "amplitude" and "phase" fields. The problem is that each record is associated with an "accelerometer" location name. To make matters worse, there is also a "date/time" field.

Example:
date/time accelerometer amplitude phase
12/16/05 alpha 0.05 180
12/16/05 beta 0.10 270
12/16/05 gamma 0.08 135
12/10/05 alpha 0.04 200
12/10/05 beta 0.11 245
12/10/05 gamma 0.15 100

Is there a way to put alpha, beta, and gamma in the same record? So that a form will be able to simultaneously show alpha amplitude, beta amplitude, and gamma amplitude levels for each given date?

Or is there another way to read the existing table in forms?



Thanks for any inputs!

Austin

[matthewspatrick]

Austin,

Actually, the layout of your table is the preferred layout. Setting up different fields for each device would be a bad idea: what if later on you added a fourth device?

You might try a form with a control source such as this query:

SELECT [date/time], Sum([amplitude]), 0, 0
FROM [yourtable]
WHERE [accelerometer] = "alpha"
GROUP BY [date/time]
UNION
SELECT [date/time], 0, Sum([amplitude]), 0
FROM [yourtable]
WHERE [accelerometer] = "beta"
GROUP BY [date/time]
UNION
SELECT [date/time], 0, 0, Sum([amplitude])
FROM [yourtable]
WHERE [accelerometer] = "gamma"
GROUP BY [date/time]

Alternatively, you could use:

SELECT [date/time], DSum("[amplitude]", "[yourtable]", "[accelerometer]='alpha'"), DSum("[amplitude]", "[yourtable]", "[accelerometer]='beta'"), DSum("[amplitude]", "[yourtable]", "[accelerometer]='gamma'")
FROM yourtable
GROUP BY [date/time]

Finally, of course, try not to put spaces or special characters (such as the forward slash, which is the division operator) in the names of database objects :wink:

[Fredo0709]

Hi Patrick,

Thank you so much for your reply! I will go ahead and try this

[Fredo0709]

So, using the above SQL string, I was able to get the data to look like this:

datetime Alpha Beta Gamma
12/10/2005 0 0 0.15
12/10/2005 0 0.11 0
12/10/2005 0.04 0 0
12/16/2005 0 0 0.08
12/16/2005 0 0.1 0
12/16/2005 0.05 0 0

Is there a way to collapse the rows to show Alpha, Beta, and Gamma on each record for each day? Like this:

datetime Alpha Beta Gamma
12/10/2005 0.04 0.11 0.15
12/16/2005 0.05 0.10 0.08

Thanks!
Austin

[matthewspatrick]

Austin,

Which query was that, the UNION query of the simpler query using DSum? If that was the UNION query, try the DSum and see if it's recordset is more suitable.

[Fredo0709]

Hi Patrick,

That was the union query. The DSum query didn't yield the data I was hoping for. I've tried again to create a select query with a Sum() for each field column based off of the union query and it seems like it collapsed the rows.

Now I just have to find a way to plot each column for each record. MS Chart isn't very fun to use. Any suggestions for where to turn to learn more about MS Chart?

Thanks!

[matthewspatrick]

OK, I found he problem in the DSum query: the criteria weren't right. Try this:

SELECT [date/time], DSum("[amplitude]", "[yourtable]", "[accelerometer]='alpha' And [date/time] = #" & [date/time] & "#"), DSum("[amplitude]", "[yourtable]", "[accelerometer]='beta' And [date/time] = #" & [date/time] & "#"), DSum("[amplitude]", "[yourtable]", "[accelerometer]='gamma' And [date/time] = #" & [date/time] & "#")
FROM yourtable
GROUP BY [date/time]

As far as charting goes...to be honest, I rarely try to chart from Access, and every time I try to, it makes me want to cry from frustration :cry:

[Fredo0709]

Thanks Patrick!

Worked beautifully. I managed to find a polar charting thing under pivotcharts, chart type. I've just begun messing around with Charts in Access and yes, I want to shoot myself in the face.

Thanks again for all your help!

Austin

[matthewspatrick]

Glad to help Austin, and good luck with your project.

[thart21]

I posted something earlier but am thinking that this thread may get me started. I'm hoping there a way to modify the DSum for my purposes.

Instead of creating the field names from the row data (alpha, beta, gamma) I need to create new fields, constraint1, constraint2, constraint3 & constraint4)
I'm thinking I need to do something lke

SELECT Prod_ID, Constraint from tblConstraints
Count(IIF([tblConstraints].[Constraint] =1, constraint1, "")
Count(IIF([tblConstraints].[Constraint] =2, constraint2, "")
Count(IIF([tblConstraints].[Constraint] =3, constraint3, "")
Count(IIF([tblConstraints].[Constraint] =4, constraint4, "")

Constraint is a Text data type

I'm trying to get multiple rows of Prod_ID's into one row with 4 "constraint" columns in my query.

Prod_ID Constraint
100 Funding
100 Weather
100 Timing
200 Weather
200 Misc
300 Resources
300 Personnel

To...
Prod_ID Constraint1 Constraint2 Constraint3 Constraint4
100 Funding Weather Timing
Am I on the right track and just need to figure out the syntax?

Thanks!