linked tables query

  • Thread starter Thread starter Ioia
  • Start date Start date
I

Ioia

I’m really new at queries and I usually manage with the query wizard however
it doesn’t work with the queries I’ve been asked
I have two tables linked by CLIENTID field.
The CLIENTDETAILS tbl has CLIENTID field and the LocalAuthority field.
The other table linked by the CLIENTID field is ENQUIRIES tbl and has all the
different kind of enquiries: BEDS, ARMCHAIRS, HOIST, etc.. (they are a
YES/NO field)
I need a query/ies to sort the following:
The total number of enquiries by each Local Authority
The total number for each type of enquiries, by each Local Authority
Thanks
Ioia
 
You are have trouble because the ENQUIRIES table structure is wrong.

Instead of going across with BEDS, ARMCHAIRS, HOIST, etc, you should be
going down like so:

CLIENTID ENQUIRIE
1 Beds
1 Armchairs
1 Hoist
2 Beds
2 Hoist
3 Armchairs

Then you could a Totals queries grouped by LocalAuthority and the counting
the ENQUIRIE for the first one.

Next a Totals queries grouped by LocalAuthority and ENQUIRIE then counting
the ENQUIRIE for the first one.
 
Ioia said:
I’m really new at queries and I usually manage with the query wizard
however
it doesn’t work with the queries I’ve been asked
I have two tables linked by CLIENTID field.
The CLIENTDETAILS tbl has CLIENTID field and the LocalAuthority field.
The other table linked by the CLIENTID field is ENQUIRIES tbl and has all
the
different kind of enquiries: BEDS, ARMCHAIRS, HOIST, etc.. (they are a
YES/NO field)
I need a query/ies to sort the following:
The total number of enquiries by each Local Authority
The total number for each type of enquiries, by each Local Authority
Thanks
Ioia
Click to expand...
 
Thank you.
I have a real problem now. The table was set in that way, because every
Enquiry usually is regarding to more than one item.
What can I do?
 
I’m really new at queries and I usually manage with the query wizard however
it doesn’t work with the queries I’ve been asked
I have two tables linked by CLIENTID field.
The CLIENTDETAILS tbl has CLIENTID field and the LocalAuthority field.
The other table linked by the CLIENTID field is ENQUIRIES tbl and has all the
different kind of enquiries: BEDS, ARMCHAIRS, HOIST, etc.. (they are a
YES/NO field)
I need a query/ies to sort the following:
The total number of enquiries by each Local Authority
The total number for each type of enquiries, by each Local Authority
Thanks
Ioia
Click to expand...

I would strongly suggest changing your table structure. You can create a new,
normalized table and use one or more Append queries to migrate your existing
data into it. It would help to have a list of all of the fields in your table
and a bit more information about what is meant by an "enquiry", and the
meaning of "local authority" - I'm not sure I understand the business
situation.
 
I’m working for a charity that gives advice to disable people. When a client
calls s/he can ask for a lot of different questions regarding his/her
impairment. We need to keep record of the advice we gave them in each of area
Eg: BEDS, HOIST, WHEELCHAIRS, (there are up to 35 different areas),
When we set the database theidea is to follow the paper form as much as
possible so we create two tables, and the forms in two tabs:
1. CLIENT DETAILS TAKEN TABLE
CLIENTID, and address, city, postcode, phone, e-mail, etc, and the local
authority. Local authority refers to which city council they are registered

ENQUIRIES
CLEINTID (is is populated automatically, with the CLIENT ID number of the
CLIENT DETAILS TAKEN TABLE) the other fields are Enquirydate, memberstaff.
There are 35 different YES/NO fields with BEDS, HOSIT, WHEELCHAIRS, etc that
are filled as they talk over the phone with the client
 
I’m working for a charity that gives advice to disable people. When a client
calls s/he can ask for a lot of different questions regarding his/her
impairment. We need to keep record of the advice we gave them in each of area
Eg: BEDS, HOIST, WHEELCHAIRS, (there are up to 35 different areas),
When we set the database theidea is to follow the paper form as much as
possible so we create two tables, and the forms in two tabs:
1. CLIENT DETAILS TAKEN TABLE
CLIENTID, and address, city, postcode, phone, e-mail, etc, and the local
authority. Local authority refers to which city council they are registered

ENQUIRIES
CLEINTID (is is populated automatically, with the CLIENT ID number of the
CLIENT DETAILS TAKEN TABLE) the other fields are Enquirydate, memberstaff.
There are 35 different YES/NO fields with BEDS, HOSIT, WHEELCHAIRS, etc that
are filled as they talk over the phone with the client
Click to expand...

Ok... you have a classic "many to many" relationship (each client can enquire
about many services, each service can be sought by many clients); and you've
made a classic mistake setting up the tables!

I'd suggest a different table structure. Create a table (with 35 rows at
present; surely over time there will be additional areas!) of Areas - a row
for BEDS, a row for HOIST, and so on. Your ENQUIRIES table would have two
fields - ClientID and Area. To collect the data, you could have a form with a
Multiselect Listbox rather than checkboxes; the person on the phone can just
tick off one row for each item they ask about. You'll need a little VBA code
to move the data from the form to the normalized table.

If you really like the checkboxes, you could have them all unbound and use
some code to move them likewise.

Now you'll have a much more searchable table structure: you can easily run a
query counting the number of clients who have requested each type of item, or
get the average number of items requested, or whatever you would like.

Post back if you would like help with the code.
 
I created a table with Client ID and Areas. Can I add to this table other
related fields (date/staff and group). How do I establish “many to manyâ€
relationship? Do I need a junction table? If so which fields should be on?
How the following tables should be related:
1. CLIENT DETAILS TAKEN TABLE
2. AREAS
3. JUNCTION TABLE???? (if needed)
I really appreciate your help, I’m really newbie at Access and in our
charity we do not have many resources
Thank you ever so much
Ioia
 
I created a table with Client ID and Areas. Can I add to this table other
related fields (date/staff and group). How do I establish “many to many”
relationship? Do I need a junction table? If so which fields should be on?
How the following tables should be related:
1. CLIENT DETAILS TAKEN TABLE
2. AREAS
3. JUNCTION TABLE???? (if needed)
I really appreciate your help, I’m really newbie at Access and in our
charity we do not have many resources
Thank you ever so much
Click to expand...

You know your data, you know your procedures... I DON'T! So I can't tell you
what information you need to store, other than an uninformed guess. For
instance, you mention "date/staff and group". When you encounter a client, is
it a one-time-only contact? Or do you have multiple contacts over time with an
individual? If it's one time only, then you might put a ContactDate and
StaffID field in the CLIENT DETAILS TAKEN TABLE (which I'd name tblClients);
if the same client might be contacted repeatedly you should have another
table.

For the specific instance about clients and areas, I'd suggest:

tblClients
ClientID<autonumber primary key>
LastName
FirstName
<other biographical details>
StaffID
ContactDate
<other info about this contact>

tblAreas
AreaID <long integer primary key>
Area <Text, e.g. "Beds">
<any other info about this area - special restrictions, quantity in stock,
again you would know better than I>

tblAreasRequested
RequestID <autonumber primary key>
ClientID <long integer link to tblClients>
AreaID <long integer link to tblAreas>
<any info about THIS client's request for THIS area, e.g. quantity needed,
maybe a Memo field for freeform notes>

If you'll be keeping track of staff members you'll want tblStaff with a staff
ID, name and contact information, and so on.

Post back if this isn't clear.
 
Thank you. I created the tbls as you told.
we really like the checkboxes, how could I have them all unbound and use
some code to move them to the tables?
Thnank you so much your help is being great
Ioia
 
Back
Top