Linking 2 tables through a query involving a time

[emorris1000]

I have 2 tables, TRundata and TGCData linked many <-> through a 3rd table (TGenDat) by field PolyID

TRundata records the flow of Gas A, data logged every 0.3 seconds or so. 2 Fields of interest, Time and Flow

TGCdata records the concentration of Gas A, data logged every 5 minutes. 2 Fields of interest, Time and Concentration

I want to be able to look at these two values together plotted over time. My guess is to use a Union query and just put these two tables together. This will give me a table with a lot of blank values, which I don't like, but I can't think of any other way to do it. This is what it would look like:

T F C
x y _
x y _
x y _
x y _
x _ z
x y _
x y _
x y _
x y _
x _ z
x y _
x y _
x y _

Is this the proper way to do this? My ultimate goal is to be able to graph these together.

Edit: Had some difficulty getting the example table to show.

 

[MarkK]

Hey there. Welcome to the forum.
What's "many <->"? If that's many-to-many then why would you need a union query? Can't you join the data on the existing relationships? If you want to make a chart can't you just plot each series against a single time dimension?
Cheers,

 

[emorris1000]

Thanks for the reply. Yeah its many to many.

The problem I have with a simple join query is that I get two fields for time (1 from each table) instead of 1. What I want is for the time field to merge from 2 fields into 1. Like shuffling cards. It will retain the flow/concentration values from the appropriate table the time came from, and put null values in the other field.

For the simple Join query, let me see if I am doing what you are talking about.

General Data has field
PolyID

TRundata has fields
PolyID, Time, Flow

TGCData Has Fields
PolyID, Time, Concentration

If I join something like:

------------
SELECT GeneralData.PolyId
, TRundata.Time
, TRundata.Flow
, TGCData.RunTime
, TGCData.Concentration

FROM GeneralData

INNER JOIN TGCData ON GeneralData.PolyId = TGCData.PolyID
INNER JOIN TRundata ON GeneralData.PolyId = TRundata.PolyID

-----------

I get a table with fields
PolyID, Time, Runtime, Flow, Concentration.

Now, I could get away with using {Time,Flow} on Axis 1, and {RunTime,Concentration} on Axis 2, and hide the second X axis, but that seems like duct tape. Seems to me there should be a way to merge the fields time and runtime into a single field.

I am 90% sure this is something really easy and I am just thinking about it wrong. Access does that to me so much....


Edit:
I tested this earlier and I can't figure out how to do it with a single UNION query as teh fields in the originating tables don't match (although I am a bit weak on Unions.)

I just tested this using 2 Append queries to build a new table, and it works, but having to append the table, delete it, and rebuild it each time I look at a new piece of data isn't appealing.
just tested this and I can

 

[MarkK]

- That's not a Many-To-Many relationship. Everything is joined on the single field PolyID so at best it's One-To-Something.
- If all GeneralData contributes is an ID field, you don't need it.
- Why union the data if you have two axes? You have two series' so plot each series on the same time scale. You want two lines on the chart right?
- Am I making sense?
- Also, if your brain feels like melted cheese you're on the right path.

 

[emorris1000]

- That's not a Many-To-Many relationship. Everything is joined on the single field PolyID so at best it's One-To-Something.
- If all GeneralData contributes is an ID field, you don't need it.
- Why union the data if you have two axes? You have two series' so plot each series on the same time scale. You want two lines on the chart right?
- Am I making sense?
- Also, if your brain feels like melted cheese you're on the right path.

My brain feels like melted cheese, so that's good.

I should be a bit clearer on the primary/foreign keys etc.

[PolyID] is a unique identifier for an experiment
[PolyID] is the Primary Key for TGeneralData.

TGeneralData has one record per experiement, and records 1 dimensional details about the experiment (date etc.)

TRunData has an autonumber Primary Key
TRunData is linked to TGeneralData by [PolyID] in a Many -> One

TGCData has an autonumber primary key
TGCData is linked to TGeneralData by [PolyID] in a Many -> One

so for [PolyID]

TRundata many<->one TGeneralData one<->many TGCData

I should be more descriptive on the tables as well, they may look like this

TGeneralData
Polyid____Otherstuff like date, ingredients etc.
120-1____xxxx
120-2____xxxx
120-3____xxxx


TRundata

PolyID____ Flow____ Time
120-1____ 0.5 _____ 1
120-1____ 0.6 _____ 3
120-1____ 0.7 _____ 6
120-1____ 0.75 ____ 9
.....
120-1____ 0.1 _____160
120-2____ 0.4 _____1
120-2____ 0.8 _____3
120-2____ 0.9 _____6
....
120-2____ 0.1 _____160
120-3____ 0.1 _____1
...


TGCData
PolyID___Concentration____Time
120-1_______ 0.1 __________5
120-1_______ 0.9 __________10
120-1_______ 0.20 _________25
...
120-1_______ 0.4 __________160
120-2_______ 0.3 __________5
...



What I would want for an end result would look like this

PolyID____Time_____Flow___Concentration
120-1____1________0.4_____ NULL
120-1____3________0.6_____ NULL
120-1____5_______NULL_____ 0.8
120-1____6______ 0.8 _____ NULL
120-1____9______ 0.9 _____ NULL
120-1 ___10 _____ NULL_____ 1.4
120-1 ___12 _____ 1.3 ______ NULL
...

Best solution would be to let me have a query that would only pull a single POLY-ID, because a full append of the data I need would create WAY too many null values (The Concentration field is really like 15 different fields, as is the FLOW field)

 

[MarkK]

To have a many-to-many relationship you need a join table that contains two different foreign keys, one for each of the 'many' tables.
Did you read the other stuff I posted?
Cheers,

 

[emorris1000]

First, thank you very much for your patience. I can sometimes be a bit of a brick wall when people are explaining stuff to me. But I promise I am trying to understand.

I'll go back through your previous comments to see if I undestood them.
--------

---Many to many comment.

I've always had some difficulty understanding many to many, but I see what you mean about 2 foreign IDs. This isn't many to many, although to me it feels that way as the two tables I am interested in have many duplicate records of a [polyID] value.

---Don't need General Data since it only contributes an ID field.

I thought I needed it since the [PolyID] field wasn't a primary key/unique field in the other two tables. In those 2 tables theres 10-100 records with the same [PolyID] value, and I thought I couldn't relate 2 tables correctly if there were duplicates records of the relation field.

- Why union the data if you have two axes? You have two series' so plot each series on the same time scale. You want two lines on the chart right?

The problem is I can't get the two sets of time records (from the different tables) in the same field. If they aren't in the same field I can't have them share an axis, right? I *think* I could bypass this by having each time series run on different x-axes, but that seems like duct tape, and I'm not sure how to write a query for a chart that includes 2 different tables and not have them related.

This is the main problem. Every query I write gives me all sorts of screwy results. I'll link some (didn't realize I could copy directly into the board. So much easier.

EDIT: Tried to link the tables, but I give up on that. I can't link them right.

SELECT GCData.GCTime
, GCData.GCConc
, TRundata.Time
, TRundata.FlowTime
, GCData.PolyID
FROM GCData
INNER JOIN TRundata ON GCData.PolyID = TRundata.PolyID

I think the problem is that I am using an Inner join and I need to be using an Outer join, and I need to join on Time.

I *think*

 

[MarkK]

K, one last time. You are having trouble joining those data because they are not related. OK, some of them share a parent, but data from the parent is outside the scope of the current problem and therefore has no bearing.
Your data share one thing, they exist in time. That is not enough to form a database relationship, but if you have two sets of data that vary in time at least you can plot them both on one graph. But to do that you must NOT join them. If you want two lines on the graph, one for each series of data, you must have two series' of data.
I hope this this helps,
Cheers,

 

[emorris1000]

Clearly I am missing something. I totally get it if you are done here, but here we go.

What you're saying makes perfect sense if I was using Excel. I would select the ranges for each {Time,Response} series and plot them.

But I don't see how you can plot 2 discreet series with 2 discreet coordinates in Access. There is a time field in both, but they are different fields.

And you say I can't join them. How can you make a chart without a query? The chart object requires a query. Any query I feed the chart object that involves 2 unrelated time fields will be a worthless full factorial combination (CROSS JOIN). If there is a way to select 2 seperate dynasets/recordsources for a single chart and have them be unrelated by a query of some sort, that is news to me.

Ultimately, my only problem is that there are 2 discreet time fields. If I can create a single time field which contains sets of time which then relates to the response factors then yes, I can create 2 series. But time must exist in a single field.

I'm pretty sure a FULL OUTER JOIN will give me the table I outlined in the first post, but its going to require some very ugly SQL. We'll see how it goes.

 

[emorris1000]

Ok, sorry to double post but I got it.

It is REALLY ugly though and I am sure there is a better way to do it. Ah well. Here's the solution:

So, I have the 2 source tables (in this case actually a dynamic query, easier that way) GCData and RunData. Both contain Time values, but neither time value matches up.

First I have to do a Full Outer Join. This isn't supported by access, but you can write the SQL yourself. Looks like this:

QRUNTIME
SELECT QGCdata.RunTime
FROM QGCdata LEFT JOIN [QRundata] ON QGCdata.RunTime = [QRundata].Time
UNION
SELECT [QRundata].Time
FROM QGCdata RIGHT JOIN [QRunData] ON QGCdata.RunTime = [QRundata].Time
WHERE QGCdata.RunTime is Null;

What this does is it gives me all of the time values from both queries.

Now I can do a second query where the 2 tables can be related through the [time] value in the QRuntime outer join I just did. This query requires the QRundata and QGCData to right join from the new QRunTime. It it Right Joins them. One of those.

SELECT QRuntimes.RunTime, QRunData.EthFlow, QGCdata.C6RatGC
FROM QGCdata RIGHT JOIN
(QRunData RIGHT JOIN QRuntimes ON QRunData.Time=QRuntimes.RunTime)
ON QGCdata.RunTime=QRuntimes.RunTime;

Gives me what I want.

Incredibly ugly. I'm sure there's a less ugly way to do it. Drives me nuts that almost every programming language was an easy way to merge arrays, yet SQL/Access don't have easy ways to merge tables.

 

[MarkK]

Well, way to go. Good keep-at-it-ness.