Update values in table based on values in another

[AlexD]

Hi,

I've got 2 linked SQL tables, linked by an ID number, and am trying to write a query (not very successfully) that will update a series of Y/N flag fields in one table based on values entered in another. The relationship between flag fields table and the values table is one-many as there can be multiple different values per ID.

That probably doesn't make much sense, so I'll give an example...

Values table has

ID Code
1 S01
2 S01
2 S02
3 S02
3 S03
etc

Where S01 = 'ASP', S02='Consultancy', S03='Contract'

I want each code to update the corresponding flag fields in the
flat flag field table

ID ASP CONSULTANCY CONTRACT
1 Y
2 Y Y
3 Y Y

Hopefully, this makes sense. Messed around with IIf statements and SQL but can't get it to work.

Any help appreciated.

cheers,

Alex

 

[namliam]

I used below sql and stored it in a query called MyCrosstab

TRANSFORM Count(values.Code) AS CountOfCode
SELECT values.ID
FROM [values]
GROUP BY values.ID
PIVOT values.Code;

Then created this query
SELECT
myCrosstab.ID,
IIf([S01],"Y","N") AS ASP,
IIf([S02],"Y","N") AS CONSULTANCY,
IIf([S03],"Y","N") AS CONTRACT
FROM myCrosstab;

Which translates more or less into what you want. I think you can expand on it to create exactly what you want...

Regards

The Mailman

 

[AlexD]

Thanks for that, Mailman.

The first crosstab part is fine, as it displays each ID record with S01, S02 etc fields with flags where relevant.

But the for the 2nd part I need to update the flat file fields named 'ASP', 'CONSULTANCY', CONTRACT' with the corresponding S01, S02, S03 flags.

Apologies if this isn't making sense.

Can anyone help?

cheers,

Alex

 

[namliam]

change the second query to an update query... All there is to it...

Regards

 

[AlexD]

But if I just change it to an update query, won't I just be attempting to update the crosstab?

I need to link the crosstab back to the flat file so I can update ASP, CONTRACT, etc with the the values produced in the first crosstab.

 

[namliam]

Link the flat file into your DB, then update the linked table...

Regards

 

[AlexD]

Update values in table based on values in another (w/ example)

OK, I think enclosing an example may help me to explain.

TABLE 1 has

LKTL_CLIENTNO (a unique ID field) plus flag fields
S_ASP
S_CSTAFF
S_CONS

TABLE 2 has

CIREF (unique ID that directly relates to record in TABLE 1) plus
TYPE_CODE

In this example TYPE_CODE contains the S01, S02 and S03 for 3 different records.

The Codes relate to a specific flag field:
S01 = S_ASP
S02 = S_CSTAFF
S03 = S_CONS

I need to link the 2 tables by rec no and to update the relevant flag field TABLE 1. For example:

LKTL_CLIENTNO = 710594
Should have a 'Y' under S_ASP

because the corresponding record in TABLE 2 was S01.

Simple, eh?

Again, any help appreciated.

Alex

 

[namliam]

Just shotting thru the forum right now... And dont really have time to look at it at this moment... But i will

Regards

 

[AlexD]

Thanks.

Does anyone have any ideas about this? I'm really stuck.

cheers,

Alex

 

[namliam]

Alex,

I have been so bussy you just would not believe. But lucky for you(and as i prommised) i had some spare time on my hands today. So i created attached example from yours.... It includes 3 ! working solutions. where i think number 3 is the one you want but its up to you.

1) works with a crosstab query and a temp table. First run 1qryMakeTemp, then
1qryUpdateTable1FromTemp
It works but will bloat your DB
2) 3 seperate update queries for each of your categories. It works but not verry nice
3) run 1 update query: 3qryUpdateTable1

Run qryResetTable1 to reset them all to "N"

Regards

The Mailman

 

[namliam]

Hmz Forgot to Attach... Here you are...

Once again (and allways will be)

The Mailman

 

[jereece]

In the update query attached above, if I am reading it correct, the field "update to" is really update from? For example the first field for Table 1 [S_ASP] is being updated from the SO1 field in the Temp table. However the SO1 field is listed in the update query as the "update to" field. Seems backwards to me or am I not understanding this?

Thanks,
Jim

 

[AlexD]

Not sure which one of the queries you're looking at but it definitely appears to update the flag fields I wanted in Table 1 based on the corresponding values in Table 2.

Mailman - thank you very much for the time you've put into this. Query 3 works like a charm. You've helped me out big style.

Thanks again.

Alex

 

[namliam]

Jim,

Yes might seem backwards but its the way it works....

Regards