If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
linked tables query
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 |
#2
|
|||
|
|||
linked tables query
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. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Ioia" wrote: 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 |
#3
|
|||
|
|||
linked tables query
"Ioia" wrote in message ... 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 |
#4
|
|||
|
|||
linked tables query
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? "Jerry Whittle" wrote: 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. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Ioia" wrote: 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 |
#5
|
|||
|
|||
linked tables query
On Fri, 12 Mar 2010 05:27:01 -0800, Ioia
wrote: Im really new at queries and I usually manage with the query wizard however it doesnt work with the queries Ive 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 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. -- John W. Vinson [MVP] |
#6
|
|||
|
|||
linked tables query
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 "John W. Vinson" wrote: On Fri, 12 Mar 2010 05:27:01 -0800, Ioia wrote: 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 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. -- John W. Vinson [MVP] . |
#7
|
|||
|
|||
linked tables query
On Thu, 18 Mar 2010 06:16:01 -0700, Ioia
wrote: Im 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 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 structu 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. -- John W. Vinson [MVP] |
#8
|
|||
|
|||
linked tables query
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 "John W. Vinson" wrote: On Thu, 18 Mar 2010 06:16:01 -0700, Ioia wrote: 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 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 structu 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. -- John W. Vinson [MVP] . |
#9
|
|||
|
|||
linked tables query
On Tue, 23 Mar 2010 03:43:03 -0700, Ioia
wrote: 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, Im really newbie at Access and in our charity we do not have many resources Thank you ever so much 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 ClientIDautonumber 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. -- John W. Vinson [MVP] |
#10
|
|||
|
|||
linked tables query
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 "John W. Vinson" wrote: On Tue, 23 Mar 2010 03:43:03 -0700, Ioia wrote: 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 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 ClientIDautonumber 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. -- John W. Vinson [MVP] . |
Thread Tools | |
Display Modes | |
|
|