linked tables query
in [General]
|
Prev: LeeAnn
Next: A little SQL
From: Ioia on 18 Mar 2010 09:16 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 <Ioia(a)discussions.microsoft.com> > 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] > . >
From: John W. Vinson on 18 Mar 2010 11:44 On Thu, 18 Mar 2010 06:16:01 -0700, Ioia <Ioia(a)discussions.microsoft.com> 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 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. -- John W. Vinson [MVP]
From: Ioia on 23 Mar 2010 06:43 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 <Ioia(a)discussions.microsoft.com> > 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 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. > -- > > John W. Vinson [MVP] > . >
From: John W. Vinson on 23 Mar 2010 11:40 On Tue, 23 Mar 2010 03:43:03 -0700, Ioia <Ioia(a)discussions.microsoft.com> 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 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. -- John W. Vinson [MVP]
From: Ioia on 24 Mar 2010 08:37
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 <Ioia(a)discussions.microsoft.com> > 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 > 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. > -- > > John W. Vinson [MVP] > . > |