mark as available (1 Viewer)

Dear experts,

I'm trying to figure out how I can mark values as "available".
I have a list of 50 phonenumber which are available on our phone exchange.
I have this an excel where it shows the telephone numbers sorted and behind the number the name of the employee who has the number assinged.
There are 44 numbers assigned at the moment and 6 are available for future use and hence marked in the excel "available" instead of an employee name.
I'm tying to get this in Access but am struggling how to add this correctly as I made following tables for the communcition (to avoid blanc entries) and then ofcourse I can't select telephonenumbers from a preset table nor mark unassigned numbers as available....(??)

EmployeeT
EmployeeID
FirstNameFK
LastNameFK
UserIDFK
IsActive

CommunicationsT
EmployeeID
MethodID
Number

MethodT
MethodID
Method

Method being: HomeTel, WorkTel, MobileWork, MobilePrivate, Skype...

Can anyone shed a light?

Thanks.

Best regards,

Siegfried

Several methods possible including:
Create a table PhoneNumbersT listing all 50 numbers on your exchange and a boolean field Available with default value False.
Then create an unmatched query using that table and the CommunicationsT table.
For the latter table, set filter criteria, Number Is Null and if necessary MethodID = Work Tel.
Use a RIGHT (OUTER) JOIN for your query to get all records in PhoneNumbersT not in CommunicationsT.
Finally change it to an UPDATE query so the Available field for the identified numbers is set to True

Thanks Colin!
Would you agree that I also link the PhoneNumbersT to EmployeeT for easy entry of the WorkTel?

EmployeeT
EmployeeID
FirstNameFK
LastNameFK
UserIDFK
PhoneNumberID
IsActive

Best regards,
Siegfried

Never duplicate data so not if it's already in CommunicationsT

If you only had one PhoneNumber per employee you could add PhoneNumber and MethodID to EmployeeT table then delete the CommunicationsT table
BUT you have several numbers so you need to keep the current structure

Also, you need to rename Number field as that's a reserved word in Access

Sorry, but I'm a bit confused with "Create a table PhoneNumbersT listing all 50 numbers on your exchange" and the numbers then in CommunicationsT?


Best regards,

Siegfried

I wrote the bit in "" marks but not in the same sentence as the bit 'and the ...'
I don't understand what you wrote either

The table PhoneNumberT needs two fields
PhoneNumber -text PK - needs to be text so it can start with 0 or + and include spaces
Available -yes/no with default =false.

Does that make sense?

Hi Colin,

Thanks for helping me out whith this, very much appreciated!
I'm trying this out, think I need to actually test this out to really understand as I'm still a bit puzzled with the numbers reflecting in CommunicationsT for the employees. I'm thinking too much excel ;-)
Have a great weekend!

Kind regards,
Siegfried

You do need to lose the Excel mindset.
Each record in CommunicationsT should have one number so one employee will have several records with numbers for e.g home phone, work phone, mobile...

Feel free to come back again after you've had a chance to look at it.