Query question
in [Help]
|
Prev: A good developer's tool?
Next: mehrere berufsunfähigkeitsversicherungen, berufsunfähigkeitsversicherung direktversicherung, bereicherungsverbot berufsunfähigkeitsversicherung, checkliste versicherung, checkliste versicherungen,
From: emanning on 25 Feb 2010 15:29 Using A2007. I've got a query that pulls 2 tables together. One table is for a person's name and some demographic data. The 2nd is for addresses. A person can have more than one address. If a person has multiple addresses, one of them is usually marked as the primary address by way of a yes/no field. There are times though when it's not known what the primary address is. So a person could have, say, 2 addresses, neither of which are primary. How do I develop the query to pull the primary address if it's marked, otherwise just pull the first address available? Thanks for any help or advice.
From: hbinc on 25 Feb 2010 16:23 On Feb 25, 9:29 pm, emanning <emann...(a)kumc.edu> wrote: > Using A2007. I've got a query that pulls 2 tables together. One > table is for a person's name and some demographic data. The 2nd is > for addresses. A person can have more than one address. If a person > has multiple addresses, one of them is usually marked as the primary > address by way of a yes/no field. There are times though when it's > not known what the primary address is. So a person could have, say, 2 > addresses, neither of which are primary. > > How do I develop the query to pull the primary address if it's marked, > otherwise just pull the first address available? > > Thanks for any help or advice. Hi Emanning, In a couple of applications I had the same problem. I solved it by forcing that every person has exactly one primary address. If the person could not decide, or had no preference, you choose one. At least the person will then be adressed on always the same adress. HBInc.
From: Rich P on 25 Feb 2010 17:20 hello, My suggestion assumes that you have an Identity field in the Address table and a Foreign key field to link to the Person table. This being the case -- you can proceed as follows: (note: I have a transact sql statement that can perform this in one shot (sql server sql), but it is not supported by Jet sql -- so here is the workaround) You will have to loop through your address table for each person who does not have a designated Primary address using a DAO loop. Then use a Top clause and Order By to retrieve the first Address Record. Here is a sample Dim DB As DAO.Database, RS As DAO.RecordSet DoCmd.SetWarnings False DoCmd.RunSql "Select ForeignKeyfld Into AtmpTbl From AddressTbl Where YesNo = 'Yes'" DoCmd.RunSql "Select t1.* Into BtmpTble From AddressTbl t1 Where Not Exists ("Select ForeignKeyfld from AtmpTbl Where AtmpTbl.ForeignKeyfld = t1.ForeignKeyfld) Set DB = CurrentDB Set RS = DB.OpenRecordset("Select ForeignKeyfld From BtmpTbl OrderBy ForeignKeyfld") Do While Not RS!.EOF DoCmd.RunSql "Insert Into CtmpTbl Select Top 1 * From AddressTbl Where ForeignKeyField = " & RS!ForeignKeyfld & " Order By AddressTblIdentityColumn" RS.MoveNext Loop DoCmd.SetWarnings True So -- First retrieve the ForeignKeys for each Person Who has a desinaged PrimaryAddress. Then retrieve the rest of the Persons without a Primary Address Where Not Exists the Foreign keys of the Persons who do have a Primary Address. Then loop through the table of People you just retrieved who do not have a Primary address by their ForeignKeys. Say Joe Smith has 4 addresses - pick the first one. Which one is the first one? That is where the Identity column of your Address table comes in. Order by this Identity column and then select Top 1 * from the AddressTbl where foreignKey = the foreignKey you are currently looping through and Order By the Identity column. (this mess is two lines in tsql, but this is the workaround for Jet sql - at least there is a workaround). Rich *** Sent via Developersdex http://www.developersdex.com ***
From: emanning on 25 Feb 2010 17:22 On Feb 25, 3:23 pm, hbinc <j.van.g...(a)hccnet.nl> wrote: > On Feb 25, 9:29 pm, emanning <emann...(a)kumc.edu> wrote: > > > Using A2007. I've got a query that pulls 2 tables together. One > > table is for a person's name and some demographic data. The 2nd is > > for addresses. A person can have more than one address. If a person > > has multiple addresses, one of them is usually marked as the primary > > address by way of a yes/no field. There are times though when it's > > not known what the primary address is. So a person could have, say, 2 > > addresses, neither of which are primary. > > > How do I develop the query to pull the primary address if it's marked, > > otherwise just pull the first address available? > > > Thanks for any help or advice. > > Hi Emanning, > > In a couple of applications I had the same problem. I solved it by > forcing that every person has exactly one primary address. If the > person could not decide, or had no preference, you choose one. > At least the person will then be adressed on always the same adress. > > HBInc. In a perfect world, where customers actually listened to database developers, that would be the perfect solution.
From: emanning on 25 Feb 2010 17:28
On Feb 25, 4:20 pm, Rich P <rpng...(a)aol.com> wrote: > hello, > > My suggestion assumes that you have an Identity field in the Address > table and a Foreign key field to link to the Person table. This being > the case -- you can proceed as follows: > > (note: I have a transact sql statement that can perform this in one shot > (sql server sql), but it is not supported by Jet sql -- so here is the > workaround) > > You will have to loop through your address table for each person who > does not have a designated Primary address using a DAO loop. Then use a > Top clause and Order By to retrieve the first Address Record. Here is a > sample > > Dim DB As DAO.Database, RS As DAO.RecordSet > > DoCmd.SetWarnings False > > DoCmd.RunSql "Select ForeignKeyfld Into AtmpTbl From AddressTbl Where > YesNo = 'Yes'" > > DoCmd.RunSql "Select t1.* Into BtmpTble From AddressTbl t1 Where Not > Exists ("Select ForeignKeyfld from AtmpTbl Where AtmpTbl.ForeignKeyfld = > t1.ForeignKeyfld) > > Set DB = CurrentDB > Set RS = DB.OpenRecordset("Select ForeignKeyfld From BtmpTbl OrderBy > ForeignKeyfld") > Do While Not RS!.EOF > DoCmd.RunSql "Insert Into CtmpTbl Select Top 1 * From AddressTbl Where > ForeignKeyField = " & RS!ForeignKeyfld & " Order By > AddressTblIdentityColumn" > RS.MoveNext > Loop > > DoCmd.SetWarnings True > > So -- First retrieve the ForeignKeys for each Person Who has a desinaged > PrimaryAddress. Then retrieve the rest of the Persons without a Primary > Address Where Not Exists the Foreign keys of the Persons who do have a > Primary Address. Then loop through the table of People you just > retrieved who do not have a Primary address by their ForeignKeys. Say > Joe Smith has 4 addresses - pick the first one. Which one is the first > one? That is where the Identity column of your Address table comes in. > Order by this Identity column and then select Top 1 * from the > AddressTbl where foreignKey = the foreignKey you are currently looping > through and Order By the Identity column. > > (this mess is two lines in tsql, but this is the workaround for Jet sql > - at least there is a workaround). > > Rich > > *** Sent via Developersdexhttp://www.developersdex.com*** Thanks Rich. I thought I'd have to do some coding. I'll do this tomorrow and let you know how it worked. |