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: Bob Quintal on 25 Feb 2010 17:39 emanning <emanning(a)kumc.edu> wrote in news:0fe06db2-1015-4322-84a9-d6c8cc471a09(a)19g2000yqu.googlegroups.com : > 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. Create a Totals query on the addresses table that groups by the foreign key of this table and then uses the 'first' option of all other fields. Have it sort on the primary address flag so that is the first record if the flag exists. Then use this query instead of the addresses table in the query that joins the name and address. You could actually embed the second query into the top-level one, but that type of SQL is only for the brave and experienced programmer. -- Bob Quintal PA is y I've altered my email address.
From: paii, Ron on 25 Feb 2010 18:12 "emanning" <emanning(a)kumc.edu> wrote in message news:3d3379a3-8ae1-412d-b46d-0a4b235d4364(a)k17g2000yqb.googlegroups.com... 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. You as the developer can help the user/customer make the application behave like it's a perfect world. Have you form make the 1st address entered primary. If the user selects another as primary; have your code update the other record(s).
From: Salad on 25 Feb 2010 18:32
paii, Ron wrote: > "emanning" <emanning(a)kumc.edu> wrote in message > news:3d3379a3-8ae1-412d-b46d-0a4b235d4364(a)k17g2000yqb.googlegroups.com... > 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. > > > You as the developer can help the user/customer make the application behave > like it's a perfect world. Have you form make the 1st address entered > primary. If the user selects another as primary; have your code update the > other record(s). > > With A2007, you can send out an email for data collection. So one could send out an email to those that didn't have a primary address, ask them if the one selected by the query is correct, and provide a textbox to change if not/false. When they reply, it automatically updates the table. This might be an option for updating the database. |