Side by Side (1 Viewer)

[aziz rasul]

I have a table which contains information like so: -

PCD SPUR CODE
AAH17 A 5894
AAH17 B 6987
FRL654 A 2589
FRL654 B 4239
etc.

Is it possible to create another table using a query or series of queries which produces: -

PCD SPUR_A CODE_A SPUR_B CODE_B
AAH17 A 5894 B 6987
FRL654 A 2589 B 4239
etc.

 

[SMG]

Create a new table as required then create an UPDATE query to update the new table from the old one. The update field for the SPUR_A field will look something like [OLD_TABLE_NAME]![SPUR] with the CRITERIA field set to A. Then do the same for the SPUR_B field with the criterai set to B. This will update the new table with the old data.

 

[aziz rasul]

Thanks SMG. Simple when you know how.

 

[David R]

Why are you denormalizing your data?

 

[aziz rasul]

This is actually how the BOSS wants it as a pre-requisite for work that requires the initial information to be structered this way.

 

[David R]

As long as you realize that you're going to create updating headaches for yourself if this is to become your new datasource, and bloat problems if these are temporary tables.

You can do the exact same thing as SMG suggested, but with normal SELECT queries, and still have your original data intact and no particular bloating problems. If you need to analyze the data in a particular way, try suggesting this (including a demonstration of how the database size changes if you put in new tables like your boss suggested).

If you're in charge of DB design, then your boss should leave that to you, in a perfect world.

 

[aziz rasul]

DAvid, how would I use select queries to accomplish this? I have tried, but without success.



[This message has been edited by aziz rasul (edited 05-21-2002).]

 

[David R]

I suppose that depends on whether you need the data editable or not. Since you were going to pull it into a new table anyway, I'm going to guess "not".

Option 1:
Use a Crosstab Query (you can do this in design view or with the crosstab wizard).
Base it on your table, we'll call it tableSpurCodes.
Row Heading: PCD, Group By
Column Heading: Spur, Group By
Value: Code, First
(if you use the wizard, it will do all of this for you except that you'll have to uncheck the box "Yes, sum the rows for me").

You'll end up with this:

__PCD____|___A___|___B___|
 AAH17   |  5894 |  6987 |
 FRL654  |  2589 |  4239 |

Option 2:
If you need it to look the other way, it will take a couple of nested queries.

Query_A:
Fields:
PCD
Spur_A: Spur, Criteria: "A"
Code_A: Code

Query_B:
Fields:
PCD
Spur_B: Spur, Criteria: "B"
Code_B: Code

QueryAll:
Tables: Your main table (the one with PCD as a Primary Key, not tableSpurCodes), plus Query_A and Query_B.
Fields:
PCD (from tablePCDs)
Spur_A, Code_A (from Query_A)
Spur_B, Code_B (from Query_B)

Then it will look exactly like you have above. The drawback of this method is it requires reworking your query if you ever gain new spur codes (C, D, etc).
It will also not be updateable, but if you're just looking to analyze the data from here that won't matter...

Good luck,
David R

[This message has been edited by David R (edited 05-21-2002).]

 

[aziz rasul]

Thanks David. I managed to get option 1 to work OK.
However Option 2 only works for records that have an A and B record for the same PCD value. Hence records with single A's and B's dont appear in the QueryAll query. I can solve the problem by creating 3 make-table queries (for A's and B's, A's only and B's only) and amalgamating them together.

[This message has been edited by aziz rasul (edited 05-23-2002).]

[This message has been edited by aziz rasul (edited 05-23-2002).]

 

[David R]

Sorry, didn't know that was a possibility.

Option 2 will work for all records, if you change the joins between the tables/queries to LEFT JOINS.
Click on the two relationships (PCD-PCD in Query_A, PCD-PCD in Query_B) and change to the second option, "Include ALL values of tablePCDs and only those values..."

Again, I urge you not to use make-table queries if you can avoid it.

[This message has been edited by David R (edited 05-23-2002).]

 

[aziz rasul]

My apologies. I should have made that clear at the outset. Option 2 is now looking good. Thanks for your patience.

 

[David R]

No problem, glad you got it working.