reverse query??

  • Thread starter Thread starter Pommy_g
  • Start date Start date
P

Pommy_g

Hi i have a union query which transfers this table:

range/colour1/qnt1ofcolour1/qnt2ofcolour1/qnt3ofcolour1/colour2/qnt1ofcolour2/qnt2ofcolour2/qnt3ofcolour2
-------->all the way up to colour 20

into this query:

range/colour/size/quantity

with the (spoon fed)help of some cool people on deja :-)

from this i then made a query that shows all records that have the
quantity below a certain value (user defined).

now is there a way to transfer it back into the very first layout i
had to start with, bearing in mind that there will not be the same
amount of colours as there was in the very first table.

TIA
 
Why? As in, why do you need to transfer it back?

I'll point out that your new fields
range/colour/size/quantity
Click to expand...
don't seem to have a one-for-one correspondence to the original fields
(unless "size" means qty1, 2, or 3). Without a clear "map" back, how could
you?

But back to my first question. If you're going to use Access, a relational
database, what is your business need to format the data in a
spreadsheet-like manner (your original data layout)?
 
thx for the reply
I'll point out that your new fields
don't seem to have a one-for-one correspondence to the original fields
(unless "size" means qty1, 2, or 3). Without a clear "map" back, how could
you?
Click to expand...

size does mean qty 1, 2 and 3, so this:

range/colour1/qnt1ofcolour1/qnt2ofcolour1/qnt3ofcolour1
apple/ red / 56 / 34 / 53

and lets say that:
qnt 1 = quantity of 50x50 size
qnt 2 = quantity of 20x20 size
qnt 3 = quantity of 10x10 size

would be transfered as:

range/colour/size/quantity
apple/red /50x50/56
apple/red /20x20/34
apple/red /10x10/53

- which would allow for (much) easier querying for other things
But back to my first question. If you're going to use Access, a relational
database, what is your business need to format the data in a
spreadsheet-like manner (your original data layout)?
Click to expand...

the same reason that the table was originally in that strucure - to
make it look nicer. As one range has many different colours and sizes
in it, it would be easier for the user to understand what is goin on.
if instead of it displaying as:

apple/red /50x50/56
apple/red /20x20/34
apple/red /10x10/53

it would display as:

apple
red /50x50/56
red /20x20/34
red /10x10/53

then it would make more sense to the user, as this is how their stock
is understood by them.

sorry i didnt make it clear

thx
 
Dear Pommy:

I believe I worked with you earlier on this, in making the first
transformation. However, you have omitted the column I suggested,
which showed the ColorNumber giving the order in which they originally
occurred.

If you retain this value, or if you choose to order them
alphabetically, you can generate a ranking of them. Either way, this
can provide a way to sequentially produce a "crosstab like" series of
sets for the 20 repetitive sets of columns.

While this technique would be useful in producing a report, it is
seriously discouraged as a table structure. The table structure of
Range/ColorNumber/Color/Size/Quantity should be used for your storage
in any case. When you need to see the data in the other form, with
repetitive sets of columns, you can produce that from the data anytime
it is needed. But it should not be stored that way.

To create the form you asked about, here's a start.

SELECT range,
(SELECT colour FROM YourTable T1
WHERE T1.range = T.range
AND (SELECT COUNT(*) FROM YourTable T2
WHERE T2.range = T1.range AND T2.colour < T1.colour) = 0)
AS colour1,
(SELECT size FROM YourTable T1
WHERE T1.range = T.range
AND (SELECT COUNT(*) FROM YourTable T2
WHERE T2.range = T1.range AND T2.colour < T1.colour) = 0)
AS size1,
(SELECT quantity FROM YourTable T1
WHERE T1.range = T.range
AND (SELECT COUNT(*) FROM YourTable T2
WHERE T2.range = T1.range AND T2.colour < T1.colour) = 0)
AS quantity1,
(SELECT colour FROM YourTable T1
WHERE T1.range = T.range
AND (SELECT COUNT(*) FROM YourTable T2
WHERE T2.range = T1.range AND T2.colour < T1.colour) = 1)
AS colour2,
(SELECT size FROM YourTable T1
WHERE T1.range = T.range
AND (SELECT COUNT(*) FROM YourTable T2
WHERE T2.range = T1.range AND T2.colour < T1.colour) = 1)
AS size2,
(SELECT quantity FROM YourTable T1
WHERE T1.range = T.range
AND (SELECT COUNT(*) FROM YourTable T2
WHERE T2.range = T1.range AND T2.colour < T1.colour) = 1)
AS quantity2,
REPEAT FOR UP TO 20 colours!
FROM YourTable T

With a small change, this could order the colour/size/quantity sets
according to their original order, assuming you retain ColorNumber as
I proposed earlier. Each subquery would look like this instead of as
above:

(SELECT colour FROM YourTable T1
WHERE T1.range = T.range
AND (SELECT COUNT(*) FROM YourTable T2
WHERE T2.range = T1.range
AND T2.ColourNumber < T1.ColourNumber) = 0)
AS colour1,

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
i have just tried it for the first 3 fields and this comes up:

"Reserved error (-3025); There is no message for this error."

am i supposed to do somthing with the T1s? and T2s?

thx
 
Dear Pommy:

The query work I provided isn't particularly simple, and it would be
no great surprise if I make a mistake somewhere. Without setting up
your data (for which I would not have complete information anyway) it
is not possible for me to test before posting. I usually hit a fairly
high proportion, though, and I don't see where this query may have
gone wrong.

Leave T, T1, and T2 alone. These are assigned as aliases inside the
query. The only thing you need to change is the name of YourTable.

Could you try this one column at a time, please, and see at what point
you get a problem? At first try:

SELECT range,
(SELECT colour FROM YourTable T1
WHERE T1.range = T.range
AND (SELECT COUNT(*) FROM YourTable T2
WHERE T2.range = T1.range AND T2.colour < T1.colour) = 0)
AS colour1
FROM YourTable T

Also, do not leave a comma on the end of the last column. In the
above, I have removed the comma after AS colour1, but it would have to
be reinserted before adding the next column.

An alternative might be if you sent me an abbreviated database, with
only one or two tables, but with your actual data, and I could try to
craft the query here. If you were to decide to send this, the zipped
attachment to an email would have to be less than 1 MB. I think I
would recommend you send only your original table, plus the empty
derived table and the query that fills it.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top