linked tables query
in [General]
|
Prev: LeeAnn
Next: A little SQL
From: Ioia on 12 Mar 2010 08:27 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
From: Jerry Whittle on 12 Mar 2010 09:06 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 > >
From: De Jager on 13 Mar 2010 12:22 "Ioia" <Ioia(a)discussions.microsoft.com> wrote in message news:C17432FE-316F-4708-A84B-730FDE54F8B5(a)microsoft.com... > 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 > >
From: Ioia on 17 Mar 2010 06:02 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 > > > >
From: John W. Vinson on 17 Mar 2010 17:57
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] |