Limiting Records on a Query

[Curtis Stevens]

I have:

Main Table - Called Customers - has main info about account
Sub Table - Called MerchantInfo - has relationship to main tbl - has owners
info, you can have multiple owners.

Now when I run a query, like to send out thank you cards to newly approved
accounts, if there is more than one owner, each one is listed, creating more
than one thank you card for the same business. How do I limit this?

Any help or suggestions? If you need more data, let me know.

Thanks
Curtis

 

[Jeff L]

Right click the top window in query design and select properties.
Change Unique Values to Yes.

Hope that helps!

 

[Curtis Stevens]

I opened it in design view, right click on the top bar (where the close,
minimize button is) and went to properties. But it only has Descrip, Format,
input & caption options under general and display control under lookup tab.

Curtis

 

[Jeff L]

No, click in the window where your tables are listed. Another way to
do it would be to click View, Properties in the menu bar.

 

[Curtis Stevens]

Gotcha, I tried uniquve values & records, neither works, still shows both.....

 

[Curtis Stevens]

Visual aid: http://www.gotmerchant.com/ty.gif

 

[Curtis Stevens]

I don't know if this helps, but I'm talking about when you have multiple
records in merchantinfo table, which are related to the main table by the
customer ID, so a particular record in customers, may have three owners,
which would be three seperate records in merchantinfo table. That is the
problem, does it for each record in merchantinfo, in other words, for each
owner. Follow me?

Thanks
Curtis

 

[Jeff L]

Yes, I believe I do. When you are joining your tables together and
outputting your customer information, if there is more than 1 owner,
the same customer info is showing multiple times. Correct?

The Unique values solution will work, but only if the date you are
outputting is the same. For example, CustomerName, CustomerAddress,
etc. If you are outputting something like the OwnerName as well, then
your output will no longer be unique because the OwnerName will be
different on each one.

Does that help?

 

[Curtis Stevens]

You have it a little backwards. Customers is the main table, only ONE record
per business. Merchantinfo table can have multiple records per ONE customer
record, as multiple owners in merchantinfo for the same business or customer
record.

What I want when displaying a customer table record, only show ONE owner
from merchant info table. But it displays each owner as a seperate record,
creating two records when doing a query. Like a customer with ID in Customer
table 1000 would have two records in merchant info table, Joe 1 and Joe 2,
but will display Joe1 with all the biz info from customers table as one
records or listing and Joe 2 as another. I can't address my thank you
letters to all owners as I can only use the fields in that table, like
merchantfname, merchantlname, see screen shot. But it only lists the first
record listed for that biz or customer record from customer tble.

Follow me?

 

[Curtis Stevens]

Hi Jeff,

Does this explain what I'm talking about?

http://www.gotmerchant.com/tynew.gif

Thanks
Curtis

 

[Jeff L]

Put this in the criteria of the MerchantID
=DMin("MerchantID","Merchants","CustomerId = " &
[Customers].[CustomerId])

Hope that helps!

 

[Curtis Stevens]

That gave me an error that wouldn't go away, this worked. I had to kill the
program to get out of it. Can you please explain this to me a little bit, so
I can take this & implement it elsewhere like in reports, etc?

DMin("MerchantInfoID","MerchantInfo","CustomerId = " &
[Customers].[CustomerId])

Thanks!

 

[Jeff L]

The function is written DMin(FieldName, TableName, Criteria). The
criteria is optional. So, if you wanted to write out the statement in
English it would be:

Give me the minimum MerchantInfoID from the MerchantInfo table where
the CustomerId in the MerchantInfo table is equal to the CustomerID
from the Customers table.

The statement will only work if you are using it in a query where
MerchantInfo and Customers are joined together. You cannot refer to
Customers in this way without joining them.

There are several other functions that act the same way. They are
DSum, DCount, DMax, DLookup, and DAvg. There may be more, but that's
all I can remember off the top of my head.

Hope that helps!