date tracking

[coogami]

Our database tracks projects by customer, date, etc. I recently generated a report listing projects started since March of this year, and my boss asked for a column on the report showing which projects are new customers; i.e., which of those customers had no projects BEFORE March 1. I can't figure out how to write a query that, within this report, will flag customers that have no projects before 3/1/11. Can anybody point me in the right direction?

[Robeen]

Here's one way of doing it:

1. Create a new query [QryExistingCustomers] that shows customers who have projects before March 1 of this year.

Eg:

 
Select Distinct CustomerName 
From TableName
Where ProjectDate < #3/01/2011#

2. Create another new query that uses the above [QryExistingCustomers] and whatever the data source os for your report [is it a table or a query?].

In Design View:
Select the Table/Query that is the data source for your report.
Select [QryExistingCustomers] that you just created.
Join CustomerName between the two.
Right-click the line joining the two sources.
Click Join Properties.
Select 2 to make sure you get all records from your report data source and matching rows from [QryExistingCustomers],
Select all your fields from your existing report data source.
Select CustomerName from [QryExistingCustomers].
Where it says 'CustomerName' - type this:
NewCustomer: IIF([QryExistingCustomers].[CustomerName] Is Null, "New Customer", "")

Run your query.
Wherever there was no project for a customer before March 1, 2011 - it will now say "New Customer" - otherwise - nothing will show.

Hope this helps!

[coogami]

Thanks, Robin. It works, but the report repeats the current project(s) multiple times for customers that DO have projects prior to 3/1/11; it appears to match the number of projects prior to 3/1/11, as if it repeats the current project(s) for every Not Null return from QryExistingCustomers. Makes the report REALLY big, and awfully repetitive.

[Robeen]

I think your join is wrong [I might be to blame].
The join you have should give you all the records that are in the table/query that is the data source for the report - and no extras.

Any chance that you can post the SQL of your query here?

[Robeen]

The only difference should be that there will be an extra field on the query that shows either 'New Customer" - or an Empty String.

That's the nature of the Join [Show all records from the left table . . .].

[coogami]

Thanks, Robeen (sorry I misspelled your name before; I'm a little harried today).

Here's the SQL from the date query:
SELECT [Project Information].[Customer ID]
FROM [Project Information]
WHERE ((([Project Information].[Date RFQ Recd])<#3/1/2009#));

from the original report query (CPG RFQs Submitted):
SELECT [Project Information].[New RFQ ID], [Project Information].[Date RFQ Recd], [Customer Information].[Customer Name], [Project Information].[Actual Date Sent], [Project Information].[Project Comments], [Project Information].[B/L/D/U], [Project Information].AE, [Project Information].RSM, [Project Information].[Project Name], [Customer Information].[Customer ID]
FROM [RIA Codes] RIGHT JOIN ([Customer Information] LEFT JOIN (([Project Information] LEFT JOIN [Cost Tracking by RFQ 2] ON [Project Information].[New RFQ ID] = [Cost Tracking by RFQ 2].[New RFQ ID]) LEFT JOIN [Employee Information] ON [Project Information].AE = [Employee Information].Initials) ON [Customer Information].[Customer ID] = [Project Information].[Customer ID]) ON [RIA Codes].[Application Code] = [Project Information].[Robotic Application Code]
WHERE ((([Project Information].[New RFQ ID]) Not Like "*.*") AND (([Project Information].[Date RFQ Recd]) Between [Enter beginning date (format ##/##/##)] And [Enter end date (format ##/##/##)]) AND (([Project Information].RSM) Like "DWM" Or ([Project Information].RSM) Like "TJP" Or ([Project Information].RSM) Like "SGS" Or ([Project Information].RSM) Like "DSM" Or ([Project Information].RSM) Like "WJM"))
ORDER BY [Project Information].[Date RFQ Recd], [Project Information].AE;

and the join query:
SELECT [CPG RFQs Submitted].[New RFQ ID], [CPG RFQs Submitted].[Date RFQ Recd], [CPG RFQs Submitted].[Customer Name], [CPG RFQs Submitted].[Actual Date Sent], [CPG RFQs Submitted].[Project Comments], [CPG RFQs Submitted].[B/L/D/U], [CPG RFQs Submitted].AE, [CPG RFQs Submitted].RSM, [CPG RFQs Submitted].[Project Name], [CPG RFQs Submitted].[Customer ID], IIf([Customer Dates].[Customer ID] Is Null,"Yes","") AS NewCustomer
FROM [Customer Dates] RIGHT JOIN [CPG RFQs Submitted] ON [Customer Dates].[Customer ID] = [CPG RFQs Submitted].[Customer ID];