Criteria in a specific entry (string) -iif,select(?),update(?)....

[kosmazzz]

Hello,
i'm almost concluded processing passengers' lists through Access(2007) so that everytime i insert a list through saved imports, goes from :
TABLE:PASSENGERS500
[NAME] [AGE] [HOTEL][TYPEOFROOM][NUM_DAYS]


NAME:"Michael Addams" AGE:"Adult" HOTEL:"Samaria Hotel" TYPEOFROOM:"DLX" NUM_DAYS:"14"
NAME:"Sarah Addams" AGE:"Adult" HOTEL: "Samaria Hotel" TYPEOFROOM:"DLX" NUM_DAYS:"14"
NAME:"Jack Vice" AGE:"Adult" HOTEL:"Kriti Hotel" TYPEOFROOM:"DLI" NUM_DAYS:"7"
NAME:"Mia Sage"AGE:"Adult " HOTEL:"Kriti Hotel" TYPEOFROOM:"DLV" NUM_DAYS:"7"
NAME:"Peter Sage" AGE:"Infant" HOTEL:"Kriti Hotel" TYPEOFROOM:"DLV" NUM_DAYS:"7"
NAME:"Hans Tiel " AGE:"Adult" HOTEL:"Santa Marina B" TYPEOFROOM:"DL2" NUM_DAYS:"7"
NAME:"Terens Mann" AGE:" Adult" HOTEL:"Santa Marina P" TYPEOFROOM:"DRS" NUM_DAYS:"14"
NAME:"Michelle York" AGE:"Adult" HOTEL:"Santa Marina P" TYPEOFROOM:"DV1" NUM_DAYS:" 7"


TO THIS kind of final table:
TABLE: HOTEL/PEOPLE

[HOTEL] [7d_adults ] [7d_Inf] [14d_adults] [14d_Inf]

[HOTEL:]Samaria Hotel [7d_adults:]0 [7d_Inf:]0 [14d_adults:]2 [14d_Inf:]0
[HOTEL:]Kriti Hotel [7d_adults:]2 [7d_Inf:]1 [14d_adults:]0 [14d_Inf:]0
[HOTEL:]Santa Marina B [7d_adults:]1 [7d_Inf:]0 [14d_adults:]0 [14d_Inf:]0
[HOTEL:]Santa Marina P [7d_adults:]1 [7d_Inf:]0 [14d_adults:]1 [14d_Inf:]0

The only problem is that there is an exception FOR A SPECIFIC entry: when or in case there are names in the passengers list
that go to "Santa Marina P" (and only in that case)
it may mean that some must be added to "Santa Marina B" and others should remain under


the name "Santa Marina P", depending on the type of room(column 4).
So for example people that go to "Santa Marina P" and have ie. "TYPEOFROOM" DV1 or DRS,
must be added to the ones of "Santa Marina B" and the final table should be like that:




[HOTEL:]Samaria Hotel [7d_adults:]0 [7d_Inf:]0 [14d_adults:]2 [14d_Inf:]0
[HOTEL:]Kriti Hotel [7d_adults:]2 [7d_Inf:]1 [14d_adults:]0 [14d_Inf:]0
[HOTEL:]Santa Marina B [7d_adults:]2 [7d_Inf:]0 [14d_adults:]0 [14d_Inf:]0
[HOTEL:]Santa Marina P [7d_adults:]0 [7d_Inf:]0 [14d_adults:]1 [14d_Inf:]0


I think the most suitable solution is creating a query using UPDATE and IIF:
UPDATE [PASSENGERS500][HOTEL] SET IIF ([HOTEL]="Santa Marina P" And [TYPOFROOM]=DV1 Or [TYPOFROOM]=DRS,
[HOTEL]="Santa Marina B",[HOTEL]="Santa Marina P"....?


BUT it cant seem to work
Plz help!

[kosmazzz]

I also tried in a query that exports the number of the 7 - day adults this additional: IIf([HOTEL]='Santa Marina P' Eqv [TYPEOFROOM]='DD1/A' Or [TYPEOFROOM]='DDS/A' Or [TYPEOFROOM]='DLX/A' Or [TYPEOFROOM]='DV1/A' Or [TYPEOFROOM]='DV2/A' Or [TYPEOFROOM]='DVP/A',[HOTEL]='Santa Marina B',[HOTEL]='Santa Marina P')
-"DDS/A" , "DD1/A", "DLX/A ,...": type of rooms that when you find them in entries with "santa Marina p" should add them with the ones of "Santa marina B" (the opposite of what the DV1, DRS type of rooms do..) ,
the result is that it divides the number "Santa Marina P" into 2 different but it does not change the name ...so for example if i have 4 people for "Santa Marina P" that 2 of them must be added to Santa Marina B and 2 to Santa marina P it shows "Santa Marina P": 2 and "Santa Marina P":2 (NOT changing/adding the 2 to "Santa Marina B").

[isladogs]

Some issues:
1. You shouldn't use special characters like / in table names - HOTEL/PEOPLE
2. Name is an access reserved word. Change to e.g. FullName
3. Your Hotel/People count should have separate records for each count e.g.

ID
Hotel
Age
Num_Days
People
TypeOfRoom

1
Samaria
Adult
7
0



2
Samaria
Infant
7
0



3
Samaria
Adult
14
2



4
Samaria
Adult
14
0

I've included TypeOfRoom in the above but it might be better as a separate table with fields Hotel ; TypeOfRoom

The best solution would be to append those records to the correct hotel in the first place
i.e. setup filter conditions for type of room in the initial append query
If Hotel Like SantaMarina* And (TypeOfRoom = 'DV1' or TypeOfRoom ='DRS') Then
Hotel = 'Santa Marina B'
Else
Hotel = 'Santa Marina P'
End If

If not done like that, good database design would prevent them being added to the wrong hotel

However, assuming you've already added them to Santa Marina P, then the update query should look something like this

UPDATE [Hotel/People] SET [Hotel/People].Hotel = 'Santa Marina B' WHERE ((([Hotel/People].Hotel)='Santa Marina P') AND (([Hotel/People].TypeOfRoom)='DV1' Or ([Hotel/People].TypeOfRoom)='DRS'));

BTW the hotel names are familiar - is this Sorrento?

EDIT: I wrote the above based on post 1 - didn't see post 2 till I'd finished mine so haven't referred to that

[isladogs]

Cross posted at https://www.access-programmers.co.uk...te=1&p=1571720
Please follow forum guidelines when cross posting

[kosmazzz]

Cross posted at https://www.access-programmers.co.uk...te=1&p=1571720
Please follow forum guidelines when cross posting

Sorry for not knowing the rules for cross posting ,
regarding the place i'm from Greece!

[isladogs]

Yes I realised afterwards - I walked the Gorge of Samaria many years ago down to Hora Sfakion if my memory is correct then a boat trip to the net village ad there is (or was) no road.