duplicate data

[hinchi1]

I have 2 tables, one for staff info and one for researcher info. Staff and researchers can be issued desk space in rooms that are available. is there a way to check that the desk has already been allocated? Can the check be made across the 2 tables?

[orange]

Seems you would also have a Desk table.
And some logic to assign/unassign Staff/Researcher to a Desk.
You can determine if a desk is available or not by checking whether it has been assigned.

Could possibly use a form and the technique shown in this free video.

Good luck.

[hinchi1]

Seems you would also have a Desk table.
And some logic to assign/unassign Staff/Researcher to a Desk.
You can determine if a desk is available or not by checking whether it has been assigned.

Could possibly use a form and the technique shown in this free video.

Good luck.

I do have a desk table also. I have created a unique key in this table to ensure the desk is not duplicated in the table when a new desk is being added to the database. The problem then is how do i check that the desk has not been allocated. The easiest way i guess would be to combine the staff and researcher tables. The problem here is the fields in both tables are different.

[Gicu]

Use a union query:
"SELECT StaffID As ID,'Staff' As TableSource, DeskId as Desk FROM tblStaff
UNION ALL
SELECT ResearcherID as ID,'Researcher' As TableSource, DeskID as Desk FROM tblResearcher;"

Cheers,
Vlad

[orange]

In the desk table you could include a field "IsAssignedTo" and when a Desk is assigned, you could put the ID of the Staff or Researcher. When that desk is unassigned, you remove the ID.

In general to see which desks are unassigned at any time, you would run a query

Select * from tblDesk where IsAssignedTo Is Null

[ArviLaanemets]

OP's problem is, that for registering staff and researchers are different tables. So some staff member can have same ID as some researcher, as both tables are independent. When e.g. in desks table is field AssignedTo, which contains either staff or researcher ID, then there is no way to say, which of them. Either there must be an additional field determining from which table the ID value originates, or ID's in both registry tables must be distinguished somehow (e.g. all staff ID's start with "S" and all researches ID's start with "R", or OP must have an additional table, where all ID's from both tables are collected and some genereal ID (probably autonumeric) is created, or OP has to redesign the database so, that staff and researchers are registered in same table. Probably having common fields in this table, and fields filled for one group only either in same table too (which results in lot of empty fields for rows from same group), or in separate table(s) with same ID value for mathcing row in main registry table.

[Gicu]

That is why I suggested using Union ALL and adding the source table field ("Staff" and "Researcher").
Cheers,
Vlad