Re: restriction due to other users

Tech-Archive recommends: Fix windows errors by optimizing your registry

The error message you are getting suggests that the field you are joining on in this query are of differing type or size i.e.
Salary Number is different in one or more of these tables:
Medical, Addresses, Next of Kin Contact1, Next of Kin Contact2, Kids.

Fix that and it should solve your error, however your design is flawed, in my opinion, and because you are using inner joins in this query, you are likely missing records in the result set.
i.e. if a person has only one next of kin, there won't be a record in the Next of Kin contact2 table, and the inner join will cause that person to be missing from the result. You need to use an outer join. The same is true for the Kids table - what if the person has no children? (or baby goats for that matter <g>)

You should have only one Next of Kin Contact table - if there are two contacts, there'll be two records in it; otherwise only one.

You could (perhaps even should), have a single table for all the 'people' in your setup. Just add a column to indicate the type of person (patient, next of kin contact, child) (um, isn't a child 'next of kin'?). Then your querying becomes even easier.


--
Joan Wild
Microsoft Access MVP
"Jo" <Jo@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:64C4F16C-8C9C-4C02-B628-BBAB4C665EFC@xxxxxxxxxxxxxxxx
Thank you Joan - your time is appreciated
SELECT Addresses.SalaryNumber AS Addresses_SalaryNumber, Addresses.FirstName
AS Addresses_FirstName, Addresses.Surname, Addresses.DateOfBirth AS
Addresses_DateOfBirth, Addresses.StatusLookup, Addresses.DateOfHire,
Addresses.Status, Addresses.Workplace, Addresses.Position,
Addresses.SupervisorManager, Addresses.Address AS Addresses_Address,
Addresses.Town, Addresses.PC, Addresses.State, Addresses.Phone AS
Addresses_Phone, Kids.ID, Kids.Name AS Kids_Name, Kids.DateOfBirth AS
Kids_DateOfBirth, Kids.Gender, Kids.SalaryNumber AS Kids_SalaryNumber,
Medical.[Salary Number] AS [Medical_Salary Number], Medical.[Medical
Information], [NEXT OF KIN CONTACT 1].NAME AS [NEXT OF KIN CONTACT 1_NAME],
[NEXT OF KIN CONTACT 1].SalaryNumber AS [NEXT OF KIN CONTACT 1_SalaryNumber],
[NEXT OF KIN CONTACT 1].ADDRESS AS [NEXT OF KIN CONTACT 1_ADDRESS], [NEXT OF
KIN CONTACT 1].PHONE AS [NEXT OF KIN CONTACT 1_PHONE], [NEXT OF KIN CONTACT
1].MOBILE AS [NEXT OF KIN CONTACT 1_MOBILE], [NEXT OF KIN CONTACT 1].WORK AS
[NEXT OF KIN CONTACT 1_WORK], [NEXT OF KIN CONTACT 1].[WORK PHONE] AS [NEXT
OF KIN CONTACT 1_WORK PHONE], [NEXT OF KIN CONTACT 1].COMMENT AS [NEXT OF KIN
CONTACT 1_COMMENT], [NEXT OF KIN CONTACT 1].[salary number] AS [NEXT OF KIN
CONTACT 1_salary number], [NEXT OF KIN CONTACT 2].NAME AS [NEXT OF KIN
CONTACT 2_NAME], [NEXT OF KIN CONTACT 2].NewSalaryNumber, [NEXT OF KIN
CONTACT 2].ADDRESS AS [NEXT OF KIN CONTACT 2_ADDRESS], [NEXT OF KIN CONTACT
2].PHONE AS [NEXT OF KIN CONTACT 2_PHONE], [NEXT OF KIN CONTACT 2].MOBILE AS
[NEXT OF KIN CONTACT 2_MOBILE], [NEXT OF KIN CONTACT 2].WORK AS [NEXT OF KIN
CONTACT 2_WORK], [NEXT OF KIN CONTACT 2].[WORK PHONE] AS [NEXT OF KIN CONTACT
2_WORK PHONE], [NEXT OF KIN CONTACT 2].COMMENT AS [NEXT OF KIN CONTACT
2_COMMENT], [NEXT OF KIN CONTACT 2].SalaryNumber AS [NEXT OF KIN CONTACT
2_SalaryNumber], Partners.SalaryNumber AS Partners_SalaryNumber,
Partners.FirstName AS Partners_FirstName, Partners.RSVP
FROM ((((Addresses INNER JOIN Medical ON Addresses.SalaryNumber =
Medical.[Salary Number]) INNER JOIN [NEXT OF KIN CONTACT 2] ON
Addresses.SalaryNumber = [NEXT OF KIN CONTACT 2].SalaryNumber) INNER JOIN
Partners ON Addresses.SalaryNumber = Partners.SalaryNumber) INNER JOIN [NEXT
OF KIN CONTACT 1] ON Addresses.SalaryNumber = [NEXT OF KIN CONTACT 1].[salary
number]) INNER JOIN Kids ON Addresses.SalaryNumber = Kids.SalaryNumber;


"Joan Wild" wrote:

Is it possible that you are that 'other user' - do you have the table open
in design view? As for the other error message, that is unrelated, however
post the SQL statement for it (open the query in design view, hit View, SQL
and copy/paste that statement in reply.

--
Joan Wild
Microsoft Access MVP
"Jo" <Jo@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:ED290449-5A34-4D28-BE1A-CD6558B7C719@xxxxxxxxxxxxxxxx
> Can someone help with Access messages please. The Query Wizard cannot
> view a
> new query in data*** view "possibly because another user has a source
> table
> open in exclusive mode", which I don't think is the case. (After > closing
> the
> query, attempt to reopen causes another error message "type mismatch in
> expression".)



.

Quantcast