Access Exercises
Exercise 1
- Inventory 45
- Query - "InvNo" + "Place"
Exercise 2
- Inventory 45
- Create a query with InvNo, tavolettaId, ArmadioId
Exercise 2
Let's split the steps!
- Let's find the blank space
- Let's try with "Hello World".
- Find the position of the "d" letter
Exercise 2
Let's split the steps!
2. Extract the value on the left before the space
Exercise 2
2. Can anyone read this function?
Tipo: Left([Place], Instr([Place], " "))Exercise 2
3. IIF function
Tavoletta: IIf([tipo]="Tavoletta","AA","BB")Exercise 2
4. IIF function and Mid
Armadio: IIf([tipo]="Armadio",Mid([Place],[expr2]),"")Exercise 2
5. IIF function and Mid
Tavoletta:
IIF(
LEFT([Place],
INSTR([Place]," ")) = "Tavoletta",
MID([Place],
INSTR([Place]," ")),
"")
Exercise 2
6. Let's find for Armadio:
Tavoletta:
IIF(
LEFT([Place],
INSTR([Place]," ")) = "Tavoletta",
MID([Place],
INSTR([Place]," ")),
"")
Exercise 3
1. Extract everything that has the world "compresso", otherwise write Null
Result:
IIF(INSTR([Descrizione], "compresso "),
[Descrizione],
null
)Exercise 3
2. let's extract strings after "compresso" (if it exists).
Result:
IIF(INSTR([Descrizione], "compresso "),
Mid([Descrizione], INSTR([Descrizione], "compresso ") + 10),
null
)Exercise 3
3. Let's save a column called "preCompresso".
preCompresso:
IIF(INSTR([Descrizione], "compresso "),
Mid([Descrizione], INSTR([Descrizione], "compresso ") + 10),
null
)Exercise 3
4. Let's add a column called "finale" with the string before "," in the preCompresso column
finale:
IIF(INSTR([preCompresso], ","),
Left([preCompresso], INSTR([preCompresso], ",") - 1),
[preCompresso]
)Exercise 3
5. Instead of using "compresso ", let's use Trim.
Result:
IIF(INSTR([Descrizione], "compresso"),
Trim(Mid([Descrizione], INSTR([Descrizione], "compresso") + 9)),
null
)Exercise 3
6. Trim
Result:
IIF(INSTR([Descrizione], "compresso"),
Trim(Mid([Descrizione], INSTR([Descrizione], "compresso") + 9)),
null
)Exercise 3
6. Or
IIf(InStr([Final], "alquanto") Or
Instr([final], "tutto") Or
Instr([final], "molto") Or
Instr([Final], "quasi"),
Null,
IIf(InStr([Final], " "),
Left([Final], InStr([Final]," ")-1,
[Final]
)
)Exercise 4
For this exercise, "Papyri_taken/given" contains the list of the "known" paperies (like the index of a library). "acktion" contains the list of events (taken, given, and when).
Let's ignore [Papyri_taken/given].[type] !
Exercise 4
Ex: extract all the actions for the known papyries (and only them). We want to see:
1. inventory number;
2. event type (from aktions);
3. date (joining day/month/year);
4. receiver
Exercise 4
For any known papyries, let's extract the current status and the event date (if exists)
1. inventory number;
2. receiver;
3. event type;
3. date (joining day/month/year) - if the status is unknown (no aktions records), write "unknown".
date: IIf([Aktions].[EventDay],[Aktions].[EventDay] & "-" & [Aktions].[EventMonth] & "-" & [Aktions].[EventYear],"unknown")
Exercise 4
For any aktions I want to know the receiver if exists.
1. inventory number;
2. receiver;
3. event type;
3. date (joining day/month/year) - if the status is unknown (no aktions records), write "unknown".
deck
By Tiziana Mancinelli
deck
- 451
