update query

[mcalex]

Hi, I'm trying to do an update query but I'm getting something wrong

Table1(ID, Name, TypeID, Count, TypeACount, TypeBCount)
Table2(TypeID, TypeName)

update Table1
set TypeACount = Count
where TypeID in (select TypeID
from Table2
where TypeName like "*Type A*")

I want to put the value from 'Count' into TypeACount, if the TypeID relates to 'Type A's

The query runs successfully, and shows the TypeACount column with the correct number of rows that _should_ be changed, but nothing gets put into the column. The select part works, and when run by itself, returns a single column of numbers. I tried first with 'where TypeID = (select TypeID ...' but Access said query must return at most one value.

what am i doing wrong?

tia
mcalex

 

[namliam]

Why do an update at all?? This is a perfect example of what should be a cross tab query...

Try making a crosstab and counting the values 'on the fly' instead of doing an update each time, save you a lot of headaches

Good luck !

 

[mcalex]

namliam said:
Why the update?

We've found that the Count field is misleading, and needs to be separated into two Count 'types'. My plan is to run this query to get the TypeA's into TypeACount, and then run another update query that gets all the TypeB's into TypeBCount, and then rip the Count field out of the table (or maybe change it to TotalCount, but more likely rip it out).

Also, I know nothing about CrossTabs

 

[namliam]

Also, I know nothing about CrossTabs

I would say, time to learn...
Crosstabs can dynamicaly make columns as you need types even make totals for you... All automagicaly and on the fly thus always up to date...

Learn to use/trust crosstabs, they are a great and strong tool.

 

[mcalex]

oic, like excel's PivotTables. I don't think this is the solution for my problem. I'm not going to be analysing the data I'm working with, I'm trying to refactor a table. Once the change is made, I won't be interested in the two types again except for entering data, and having the two columns will make that process loads easier.

 

[namliam]

Yes pivot in Excel

But when inputting the count will change, no?

 

[mcalex]

sorry for late reply, afk over the weekend.

But when inputting the count will change, no?

No, from now on, input will either be into TypeA or TypeB, depending on the inputter. There are some rare cases where an inputter will add to both types, and that is the reason for the change.

 

[namliam]

Thats sounds to me like adding a row = changing the total count (+1)