Need help in query, please.

[MSherfey]

I'm really stuck on this one and need your expertise. I have a simple query which checks to see if someone is current on their maintenance for the year. The problem is sometimes they purchase more than one item and don't always purchase maintenance for the other items.

Maintenance: IIf([Invoice Data]![Maint End:]<Date() Or [Invoice Data]![Maint End:] Is Null,"Expired","Current")

How can I return a 'current' value for all their purchases regardless of the other values. In short, if they have even one item as current, the whole field needs to read current.

It appears to be working for customers who only make one purchase, but customers who make more than one it returns a value for each purchase. I need to group all these as one and if even one of them is current then all are current.

Does that make sense?

 

[MSAccessRookie]

You look like you are trying to get the information from a Form instead of the Table that supplies the data to the Form. You need to build a Query using the Function Max() along with a GROUP BY clause. Max(InvoiceDate) (or whatever you call it) will get the maximum value (most recent) based on the GROUP BY filtering parameters. You can then use the results of this query as the source for the form.

 

[MSherfey]

Actually, this is just for a query. I need to find out how many customers meet certain criteria. As a secondary question, they want to know what customers, which meet the criteria, are current on their maintenance. I was hoping to get this done in one query. Have a basic query with an added field for whether or not they are current on their maintenance.

I'll play around with your Max function so see if that is it. Thanks!!

 

[Brianwarnock]

If you make your query an aggregate query, grouping on name and MIN on Maintenace if there is a current it will show that, current being less than expired.

Brian

 

[MSherfey]

I just found that out and had a question about that. I did the aggregate and made the Total a 'Max' and it removed all the duplicates and made them unique with 'expired'. I thought that may have to do with the ASCII value for 'current' vs 'expired' so I changed it to 'Min'. Now the dups are removed and it shows them as 'current'.

Is it because of the ASCII value? If so, is it still accurate?

 

[MSherfey]

I have a question on the accuracy of the 'min' total.

When I run the query without the 'min', it returns duplicate lines for years where a customer made more than one purchase and one of them does not have maintenance.

IDA Current 2008
IDA Expired 2008
etc...

Now I filter the items listed as current and get a count. Now when I add the 'min' total on the maintenance field I get unique values for the ones which had dups and they're listed as current. This is as expected and seems correct.

However, when I filter again on 'current' I get a different count!? Wouldn't/shouldn't they be the same count of 'current' items? Duplicates or not, there is still only a certain number of customers marked as current. This makes me think there is something else going on.

Ideas?

 

[Brianwarnock]

Are you grouping on customer here

However, when I filter again on 'current' I get a different count!? Wouldn't/shouldn't they be the same count of 'current' items? Duplicates or not, there is still only a certain number of customers marked as current. This makes me think there is something else going on.

or just counting current? If the latter then customers with multiple current will increase the count.

Brian

 

[Brianwarnock]

We do not have the whole picture but if you just want to know how many customers have 1 current maintenacethen

SELECT Count([custname]) AS countcust
FROM (select distinct custname
         From tablename
        Where maintenance="current"). AS q;

Note that tablename could be a query

Brian

 

[MSherfey]

Are you grouping on customer here or just counting current? If the latter then customers with multiple current will increase the count.Brian

I'm limiting the year to only 2008 and doing a filter on 'Current'. The rest of the fields are grouped (Geo, CustID, etc) Since this is only for one year, there shouldn't be any dups.....shouldn't

SELECT [Invoice Data].[EndUser POCN:], [Invoice Data].[EndUser OCN:], [Invoice Data].[Customer Name:], [Invoice Data].[Geo1:], "Core Desktop" AS [Product Lvl 3], 1 AS [Product Value], Min(IIf([Invoice Data]![Maint End:]<Date(),"Expired","Current")) AS Maintenance, IIf(DatePart('m',[Invoice Data].[Invoice Date:])>10,DatePart('yyyy',[Invoice Data].[Invoice Date:])+1,DatePart('yyyy',[Invoice Data].[Invoice Date:])) AS FY
FROM [Invoice Data]
GROUP BY [Invoice Data].[EndUser OCN:], [Invoice Data].[Customer Name:], [Invoice Data].[Geo1:], "Core Desktop", 1, IIf(DatePart('m',[Invoice Data].[Invoice Date:])>10,DatePart('yyyy',[Invoice Data].[Invoice Date:])+1,DatePart('yyyy',[Invoice Data].[Invoice Date:])), [Invoice Data].[EndUser POCN:]
HAVING (((Min(IIf([Invoice Data]![Maint End:]<Date(),"Expired","Current")))="Current") AND ((IIf(DatePart('m',[Invoice Data].[Invoice Date:])>10,DatePart('yyyy',[Invoice Data].[Invoice Date:])+1,DatePart('yyyy',[Invoice Data].[Invoice Date:])))=2008));

I'll try your sample code and see what changes. Thanks!