Iif statement in update query

[lookforsmt]

Hi! I have a crosstab query which gives me the expected result, but I am not sure how to use the same query in update query to update a new field with the details.

Below is sample query and attach snap shot of the query

TRANSFORM Count(tblUpdate.[UniqueRef (No Duplicates)]) AS [CountOfUniqueRef (No Duplicates)1]
SELECT tblUpdate.NewEntry, tblUpdate.EntryDate, Count(tblUpdate.[UniqueRef (No Duplicates)]) AS Total
FROM tblUpdate
GROUP BY tblUpdate.NewEntry, tblUpdate.EntryDate, tblUpdate.EntryDate
ORDER BY tblUpdate.NewEntry
PIVOT tblUpdate.UserCode;

 

[Ranman256]

use the crosstab query in a make table query.
then run your update on the created table.

 

[lookforsmt]

Thank you for your response, but I need to write the query in update query that will give me the result which will be updated in the "tblupdate" in the field "DepName"

 

[sxschech]

Are you trying to get all the fields that the crosstab displays into another table or certain cols or a specific value?

 

[lookforsmt]

Thanks for your response. Infact I have two tables one will be entire columns and the other table will be selected columns.

Also the attached images column headings (depName)3; 25; 44; 46; etc, represents as OP; BR; BR; CP; TG; etc.

how do I add this in the original table where the raw data uploaded into the original data is without the DepName, so I have to do a iif function in the update query to add the details.

I hope I am clear

 

[sxschech]

Not sure if I fully understand the issue, if you are looking to get the col headings into your crosstab query, and the headings you provided are always going to be the same, you could in design view, click in the gray area above the grid, open the properties window and type in the names into the Column Headings box as follows:

OP, BR, BR, CP, TG

If you'd rather do it in sql, it would look like this (from your query in original post):

TRANSFORM Count(tblUpdate.[UniqueRef (No Duplicates)]) AS [CountOfUniqueRef (No Duplicates)1]
SELECT tblUpdate.NewEntry, tblUpdate.EntryDate, Count(tblUpdate.[UniqueRef (No Duplicates)]) AS Total
FROM tblUpdate
GROUP BY tblUpdate.NewEntry, tblUpdate.EntryDate, tblUpdate.EntryDate
ORDER BY tblUpdate.NewEntry
PIVOT tblUpdate.UserCode[COLOR=Red] In ("OP", "BR", "BR", "CP", "TG")[/COLOR];

 

[lookforsmt]

HI! Sir, thanks for your response, I applied the sql in a new query and it gave me the below error: a) "BR" cannot be more than once on fixed column headings. So removed one "BR" and I get error: b) Data Type Mismatch in criteria expression.

I wanted to update the BR code in the master table field name "DepName".

This is when I download the excel sheet and it looks for the UserCode & UserId and determines whether it is ("OP", "BR", "BR", "CP", "TG") in a select query and
Then the same select query i would use the update query to update Master table under DepName

will this be possible
I have tried and no success on this

 

[sxschech]

Do you have a visual of the source data, what you want to grab from there and of how it should look in the master table?

 

[lookforsmt]

HI! I have uploaded file v6.2.
The tblDepName has field UserId & DeptName.
TblUpdate has field DepName which will check UserCode & UserId and will be updated through update query

I am not able to update the field DepName which is blank.

Once this is done the want to create cross-tab which will have headings as
("OP", "BR", "BR", "CP", "TG") and not User code (3, 25, 44, 46, etc)

 

[sxschech]

Here are some sample queries for you. DataUpdate_test_v6.2a

Depending on what you are doing, you may not need the update query, if you are only trying to report out data then Query1_Crosstab will do that for you. Query1 is used as the basis for Query1_Crosstab and contains a join that gets the DeptName from the tblDepName. I put in some logic so that it will show you cases where there is no match for the DeptName and in the crosstab will create a col called "Missing". Since not sure of the the crosstab output, I have it do a count of records rather than a sum.

If you really need to keep the deptname in the other table, I did include a query that will do that.

Also was it intentional that in one table you use depname and the other use deptname?

 

[lookforsmt]

This is exactly what I wanted to do. Thank you for the crosstab query and update query. Now I can use anyone from these.

No the depname was typo error. I did not do that intentional. What if the name was same will that matter?

I just wanted to ask one more question pertaining to this
When I download the report with append query I want to capture the time in one of the field which should be auto picked when I download.

The 2nd crosstab query will filter the ApprovedId data by time
Time column heading will be as:
Before 08:00 AM
08:01 AM - 09:00 AM
09:01 AM - 10:00 AM
10:01 AM - 10:30 AM
10:31 AM - 11:00 AM
After 11:01 AM

will this be possible if the tblupdate has a column UpdateTime with time as mentioned above.
I will be downloading the report in interval of above mentioned time and the result of crosstab will be saved in new table tblTime

 

[sxschech]

What if the name was same will that matter?

Names don't need to be identical, but might as well be consistent. When the names are the same, you will need to qualify them (tablename dot field name) when joined, which your queries are already doing. That is to say:

tblUpdate.DeptNo
tblEntry.DeptNo

ApprovedId data by time

For time interval buckets, take a look at post #2 by DCrake

http://www.access-programmers.co.uk/forums/showthread.php?t=164292

 

[lookforsmt]

Thanks for your reply on the time interval. I will have a look at it.

On the previous post, there is change in approach and I am not sure whether the same approach will be applicable

Under UserCode there will be multiple UserId which gets approved by mentioned under ApprovedId.

There are certain criteria for usercode 3 the userId pertains to 3 (called as CQ) and will be approved by 3 or left blank in some cases. (called as PQ)
And likewise all other usercodes have certain criteria. with different abbreviations.

My DB "tblUpdate" data is not accurate. I will update a fresh data and let you know the results.

Thanks for your support.

 

[lookforsmt]

Hi! sxschech

Below query is to identify for one user Id and replace it in Source field without looking for the Table, "tblDeptName"
I am not able to join all the queries together and replace it in the source.

will this be possible under one single query

BR:
SELECT tblUpdate.Source, tblUpdate.UserId, tblUpdate.UserCode
FROM tblUpdate
WHERE (((tblUpdate.Source)=IIf(IsNull([tblUpdate].[Source]),"BR",[tblUpdate].[Source])) AND ((tblUpdate.UserId) Is Null And ((tblUpdate.UserId) Is Null Or (tblUpdate.UserId) Like "*[!((E) or (0-9))]*") And ((tblUpdate.UserId)="isnull" Or (tblUpdate.UserId) Like "*[!((tgbr) or (000-999))]*")) AND ((tblUpdate.UserCode) Not Like 3 And (tblUpdate.UserCode) Not Like 99)) OR (((tblUpdate.UserId) Not Like "SystemUser" And ((tblUpdate.UserId)="isnull" Or (tblUpdate.UserId) Like "*[!((E) or (0-9))]*") And ((tblUpdate.UserId) Is Null Or (tblUpdate.UserId) Like "*[!((TGBR) or (000-999))]*")) AND ((tblUpdate.UserCode) Not Like 3 And (tblUpdate.UserCode) Not Like 99));

TGBR:
SELECT tblUpdate.Source, tblUpdate.UserId
FROM tblUpdate
WHERE (((tblUpdate.Source)=IIf(IsNull([tblUpdate].[Source]),"TGBR",[tblUpdate].[Source])) AND ((tblUpdate.UserId) Is Null)) OR (((tblUpdate.UserId) Not Like "*[!((TGBR) or (000-999))]*"));

CL:
SELECT tblUpdate.Source, tblUpdate.UserId, tblUpdate.UserCode
FROM tblUpdate
WHERE (((tblUpdate.Source)=IIf(IsNull([tblUpdate].[Source]),"CL",[tblUpdate].[Source])) AND ((tblUpdate.UserId) Is Null And ((tblUpdate.UserId) Is Null Or (tblUpdate.UserId) Like "*[!((E) or (0-9))]*") And ((tblUpdate.UserId)="isnull" Or (tblUpdate.UserId) Like "*[!((TGBR) or (000-999))]*")) AND ((tblUpdate.UserCode) Like 3 And (tblUpdate.UserCode) Like 99)) OR (((tblUpdate.UserId) Not Like "SYST" And ((tblUpdate.UserId)="isnull" Or (tblUpdate.UserId) Like "*[!((E) or (0-9))]*") And ((tblUpdate.UserId) Is Null Or (tblUpdate.UserId) Like "*[!((TGBR) or (000-999))]*")) AND ((tblUpdate.UserCode) Like 3 And (tblUpdate.UserCode) Like 99));

CP:
SELECT tblUpdate.Source, tblUpdate.UserId, tblUpdate.UserCode
FROM tblUpdate
WHERE (((tblUpdate.Source)=IIf(IsNull([tblUpdate].[Source]),"CP",[tblUpdate].[Source])) AND ((tblUpdate.UserId) Is Null And ((tblUpdate.UserId) Is Null Or (tblUpdate.UserId) Like "*[!((E) or (0-9))]*") And ((tblUpdate.UserId)="isnull" Or (tblUpdate.UserId) Like "*[!((TGBR) or (000-999))]*")) AND ((tblUpdate.UserCode) Like 3 And (tblUpdate.UserCode) Like 99)) OR (((tblUpdate.UserId) Not Like "SYST" And ((tblUpdate.UserId)="isnull" Or (tblUpdate.UserId) Like "*[!((E) or (0-9))]*") And ((tblUpdate.UserId) Is Null Or (tblUpdate.UserId) Like "*[!((TGBR) or (000-999))]*")) AND ((tblUpdate.UserCode) Like 3 And (tblUpdate.UserCode) Like 99));

CO:
SELECT tblUpdate.Source, tblUpdate.UserId, tblUpdate.UserCode
FROM tblUpdate
WHERE (((tblUpdate.Source)=IIf(IsNull([tblUpdate].[Source]),"CO",[tblUpdate].[Source])) AND ((tblUpdate.UserId) Is Null And ((tblUpdate.UserId) Is Null Or (tblUpdate.UserId) Like "*[!((E) or (0-9))]*") And ((tblUpdate.UserId)="Is Null" Or (tblUpdate.UserId) Like "*[!((TGBR) or (000-999))]*")) AND ((tblUpdate.UserCode) Like "SYST")) OR (((tblUpdate.UserId) Not Like "SYST" And ((tblUpdate.UserId)="Is Null" Or (tblUpdate.UserId) Like "*[!((E) or (0-9))]*") And ((tblUpdate.UserId) Is Null Or (tblUpdate.UserId) Like "*[!((TGBR) or (000-999))]*")) AND ((tblUpdate.UserCode) Is Null));

CD:
SELECT tblUpdate.Source, tblUpdate.UserId, tblUpdate.UserCode
FROM tblUpdate
WHERE (((tblUpdate.Source)=IIf(IsNull([tblUpdate].[Source]),"CD",[tblUpdate].[Source])) AND ((tblUpdate.UserId) Is Null And ((tblUpdate.UserId) Is Null Or (tblUpdate.UserId) Like "*[!((E) or (0-9))]*") And ((tblUpdate.UserId)="Is Null" Or (tblUpdate.UserId) Like "*[!((TGBR) or (000-999))]*")) AND ((tblUpdate.UserCode) Not Like "* [!((E) or (0-9))]*"));

 

[lookforsmt]

is there a way a function can be written to avoid all the lengthy queries instead
appreciate any support

 

[Uncle Gizmo]

You could reduce the length somewhat by removing the tables. They are unnecessary, see this video here at time index 1m45s:-

https://youtu.be/IorNg7ROemE?t=1m45s

make a backup copy first just in case something goes wrong...

Sent from my SM-G925F using Tapatalk