Sub-query, or what?

[VJ-mike]

This is probably an odd request, stemming from some
raunchy source data. here's the scoop.

TableB is 500,000 records and 35 fields - a very ugly flat
file generated from a telemetry program (POS). what I'm
trying to clean up is that field "post 2" for 1 station
(out of 60 stations) may equate to a flow value, while
field "post2" for another station may equate to pump run
hours - very bad, right?

I have a key TableA that maps each station's flow (and
other) field.
<TableA:> (greatly simplified)
Station: FlowField: TankLevel: PumpRun:
"bismark" "post1" "post2" "post3"
"alder" "post2" "post3" "post33"

and so on for 60 stations...

so if I run a query for the FlowField of Alder the result
is "post2".

Now, <TableB>
Date: Station: Post1: Post2: Post3: (and so on)
"01/01/04" "bismark" "4200" "21.2" "2.2"
"01/02/04" "bismark" "4300" "21.1" "2.5"
"01/01/04" "alder" "" "2472" "x"
"01/02/04" "alder" "" "1356" "x"

and so on, 9000 records for each of 60 stations.

OK, now from the previous query, I know that the FlowField
of Alder the result is "post2".

The tricky part is how to put the correct FlowField
into a new query grid... Is it post1, post2, etc? we know
from the previous Query that we need "post2".

So in a new query grid where TableA is linked to TableB
via the Station fields in each, I request the:
Date: Station: Post2:
criteria-"alder"

the results would be:

"01/01/04" "alder" "2472"
"01/02/04" "alder" "1356"

I want that last field name(object, or whatever you call
it!) to come from the previous query, not look it up and
type it manually. maybe that doesn't seem like extra work
to some folks, but I thought it would serve as an
opportunity to learn something. Perhaps this can only
happen with a macro or VB.

If I could also get the station name ("alder") from the
pre-query as well, that'd be even better.

thanks,
mike.

 

[Michel Walsh]

Hi,


The actual table seems normalized, somehow, and it also seems you want
to de-normalize it (data becoming stored in field name). Generally, we use
crosstab to de-normalize data, so I suggest you take a look at that tool.

Sure the actual data may look horrible when we look at it, but it is
easy to WORK with it. Once the NAME of the FIELD will hold the data, that
will be a nightmare to work with such structure, but it will be easy to READ
it. Fortunately, in Access, you can do both: use tables to WORK, use forms
to READ, and to move from easy-work to easy-consultation, use the crosstab.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Sorry, I'm afraid you have it backwards, Michael.
I want to break this flat file into relational tables.
and if the same field in the flat file means 'flow' for
one record and 'pump run time' for another record, how can
you consider it normalized?

maybe i'm missing something. I've only done pivot tables
or crosstabs on individual tables, never on linked tables.

I'll reformat the two tables (see below) to be comma
delimited files that you could import and play with if you
want the challange.

many thanks, mike

-----Original Message-----
Hi,


The actual table seems normalized, somehow, and it also seems you want
to de-normalize it (data becoming stored in field name). Generally, we use
crosstab to de-normalize data, so I suggest you take a look at that tool.

Sure the actual data may look horrible when we look at it, but it is
easy to WORK with it. Once the NAME of the FIELD will hold the data, that
will be a nightmare to work with such structure, but it will be easy to READ
it. Fortunately, in Access, you can do both: use tables to WORK, use forms
to READ, and to move from easy-work to easy-

consultation, use the crosstab.

Hoping it may help,
Vanderghast, Access MVP

<TableA:> (greatly simplified)
Station, FlowField, TankLevel, PumpRun,
"bismark", "post1", "post2", "post3",
"alder", "post2", "post3", "post33",
Date, Station, Post1, Post2, Post3,
"01/01/04", "bismark", "4200", "21.2", "2.2",
"01/02/04", "bismark", "4300", "21.1", "2.5",
"01/01/04", "alder", "", "2472", "x",
"01/02/04", "alder", "", "1356", "x",

 

[Michel Walsh]

Hi,


Normalization is not really an absolute concept in itself, but more about
what we have to do with the data. As example:


Salesman When Amount
1010 January 10.10
1010 February 11.11
.....


is probably normalized while

Salesman January February March ....
1010 10.10 11.11 ...


is probably not, but not in absolute. To gauge the design is to know what
you want further do with your data.... that, I can't tell, you are in a
better position than me, but observe that the first design can EASILY answer
to questions like finding the sum of sales, per salesman, from month1 to
month2, having these two values a "parameter" supplied by the end user of by
a procedure (to compute moving average, as example). The same question will
be very hard to solve, with the second design. Unfortunately, the first
design is "harder to read", for a human. I can't tell, I repeat myself, your
exact needs, so, with 100% assurance, I can't say if your table is actually
normalized or not, but it looks like the first design, here up, and it
"seems" you seek something like the second one, that is why I suspected you
were wanting a de-normalized table, but that was just a "probability by
appearance", not a definitive judgment.



You can use a Crosstab query on linked table(s) or other queries. If you use
a parameter, you have to supply its datatype (in the menu, while the query
designer is open, it is under Query | the last item should be
Parameters... ). That is an OBLIGATION for crosstab (while it is optional,
for other query types). You can use the query wizard, or the graphical grid
to help you. As example, for the salesman data here up, it is

TRANSFORM SUM(Amount)
SELECT Salesman
FROM myTable
GROUP BY Salesman
PIVOT When


and the values that were under the column When are now fields, in the query
result.




Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Apples and oranges...
You're getting close but let me take a moment to modify
this example and see what you think.

Salesman Post1 Post2
1010 January apples
1010 February oranges
1020 apples January
1020 oranges February

And I have another table (a key) that shows that for:
Salesman 1020, values in the "Post1" field are
really "fruit types". Or that for salesman 1010
the "post1" field values are really "SalesMonth" Like this

Salesman Post1 Post2
1010 SalesMonth FruitVariety
1020 FruitVariety SalesMonth

Here's what I want returned with a maketable query:

Salesman FruitVariety SalesMonth
1010 Apples January
1010 Oranges February
1020 Apples January
1020 Oranges February

Do you think that's possible with a crosstab?
Seems to me that with cross tabs you have to be
actually calculating something (average, min, max) but in
excel you can use a "value" as well...

That's my final attempt - hope it illuminates the problem.
regards, mike.

 

[Michel Walsh]

Hi,



In this specific case, we will need to rearrange the columns a little bit:

-----------------------------
SELECT a.Salesman, a.Post1 As Value, b.Post1 As Variety
FROM a INNER JOIN b ON a.Salesman=b.Salesman

UNION ALL

SELECT a.Salesman, a.Post2, b.Post2
FROM a INNER JOIN b ON a.Salesman=b.Salesman
-------------------------------


That query should supply:

Salesman Value Variety
1010 January SalesMonth
1010 apples FruitVariety
1010 February SalesMonth
1010 oranges FruitVariety
1020 apples FruitVariety
1020 January SalesMonth
1020 oranges FruitVariety
1020 February SalesMonth


From that query, we can now built a crosstab. Here the final result can
vary, but, as example, we can GROUP on Salesman and on Variety, Pivot on
Value, and aggregate with COUNT(Value), as example:


SalesMan Variety January February Apples Oranges
1010 SalesMonth 1 1 null
null
1010 FruitVariety null null 1
1
1020 SalesMonth 1 1 null
null
1020 FruitVariety null null 1
1



we can even use some special tricks demonstrated by Steve Dassin, the
crosstab master, and eventually group by salesman, pivot on Variety, and
get a "list aggregate" on Value:


SalesMan SalesMonth FruitVariety
1010 January, February apples, oranges
1020 January, February apples, oranges



Part of the trick is to remember that the PIVOT expression ( or field )
would supply the new fields name. You know better than me what is the final
arrangement you want, but I am almost sure you have to build your solution
from the UNION ALL query here up.


Hoping it may help,
Vanderghast, Access MVP

 

[Jamie Collins]

Normalization is not really an absolute concept in itself, but more about
what we have to do with the data. As example:


Salesman When Amount
1010 January 10.10
1010 February 11.11
....


is probably normalized while

Salesman January February March ....
1010 10.10 11.11 ...


is probably not, but not in absolute.

Careful, you might end as quote of the week:

http://www.dbdebunk.com/

but I don't think MS Access registers a blip on their radars.

Jamie.

--

 

[VJ-mike]

THANKS!

the Union query's got me going - just what I needed.

YOU ROCK!