multiple phone numbers producing multiple details

[conwayap]

I have three phone numbers for a particular organization...and the entire detail is repeated for each unique number...how can I tweak this to just show the primary number (field PrimaryNum with value 1) and thus one entry per company?

I thought this would work:
=IIf([DataOrgPhone]![PhonePrimary]="1",[DataOrgPhone]![PhoneNumber],"")

but it doesn't!

and yes, I am a noob

 

[SoCalAccessPro]

Well, yeah, with that conditional statement it's still gonna return a record for each phone number, only if it's not the primary it'll return empty string "".

What you really probably want to do is make sure each customer has a phineprimary with a value of one, and inlclude that in your where clause.

Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[Remou]

Do you have one or two tables (eg tblCompany, tblPhoneNos) or do you have a table with several almost identical records for companies with more than one number?

Have you read

http://www.r937.com/relational.html?

 

[conwayap]

Remou,
separate tables

 

[Remou]

Perhap a query on these lines:

[tt]SELECT c.ID, c.Blah, t.PhoneNo
FROM tblCompany c
LEFT JOIN (SELECT CompanyID, PhoneNo
FROM tblPhoneNos
WHERE PhonePrimary=1) t
ON c.ID=t.CompanyID[/tt]

 

[conwayap]

why wouldn't this work:

SELECT DataOrgPhone.PhoneNumber
FROM DataOrgPhone
WHERE ((DataOrgPhone.PhonePrimary)=1);

 

[Remou]

It would work. I though you needed company details as well.

 

[conwayap]

I'm pulling the address from another table that will only accept one address per company...my phone table allows for numerous numbers.

Strangely, when I enter the query in to a field on my report (and run the report) I am prompted for the parameter

 

[Remou]

You cannot set a control (reports and forms have controls, tables have fields) to an SQL string, you can use DlookUp

=Dlookup("PhoneNumber","DataOrgPhone","PhonePrimary=1")

But this will simply show the first phone numebr found that matches the criteria, regardless of the company to which it belongs. You could add criteria that references the company, but if you wish to create a report with one address and one phone number, it is generally best to use a query that combines the two tables. The query I showed is over-complicated for your purpose. It should be possible to build a suitable query based on the two tables in the query design window.