Select just one record

[Guest]

Hi,

I have a table that has fileds called customer_id and month_invoice.

One customer can be many months of invoice, but I want just the customer
that has one month of invoice.

Is this is possible using the query in access ?

 

[Allen Browne]

Use a Totals query:

1. Create a query using your month_invoice table.

2. Depress the Total button on the toolbar.
Access adds a Total row to the query design grid.

3. Drag Customer_ID field into the grid.
Accept Group By under this field.

4. Drag the primary key field into the grid.
In the Group By row under this field, choose Count
In the Criteria row enter:
1

 

[John W. Vinson]

Hi,

I have a table that has fileds called customer_id and month_invoice.

One customer can be many months of invoice, but I want just the customer
that has one month of invoice.

Is this is possible using the query in access ?

Create a Query based on the table; select the Customer_ID and month_invoice
fields. Make it a Totals query by clicking the Greek Sigma icon (looks like a
sideways M).

Select "Group By" on the Customer_id field, and change it to "Count" on the
month_invoice field. Put a criterion on this field of 1.


John W. Vinson [MVP]

 

[Guest]

I have a similar table and I create this solution, adding this condition in
the date field. Change the name of table and fields with yours. If you
change the the number 1 for >= or = to the number of times you have the
invoice you want and you can get different information according to the
number of invoices.

In (SELECT [DVisit] FROM [PatientRegistry] As Tmp GROUP BY [DVisit],[MedRec]
HAVING Count(*)>1 And [MedRec] = [PatientRegistry].[MedRec])