New User Question

[tedrummond]

I am a very novice access user. I took a class when I was in business school that covered the basics, but I have never used it since. I am a very advanced excel user, and it had always done everything I needed. I have, however, come across a problem that I can not solve with excel. I think access may offer the solution. So, here it is:



We have switched managment systems at the company I work for. Our old system had a report programmed into it that would pull the names of the customers that had not been in for service in a predetermined amount of time. For example, if I came in for a service appointment on Feb 22nd 2012 and I hadn't been in since, my name would be on the list if I ran it today.

I can have our current managment system download a .csv file to my desktop with all of the service customer data from the previous day every night. If I dumped that list into an access database everyday is there a way that access could recognize duplicated customer numbers and delete the previous rows with the same customer number automatically? With excel I can delete duplicates, but it saves the first entries in the column and deletes the newest. That doesn't work for me, because I want the last date that that customer has been in to the store.

Here's and example:

Customer Number Repair Order Date
123 12 1/1/13
456 13 1/5/13
789 14 1/16/13
123 15 2/1/13
123 16 2/15/13

The bold entries would automatically be deleted, leaving the most current date of service for customer 123.
Then when I wanted to run the report, I could simply download the database into excel, enter a current date formula into a column, enter a difference formula and delete out all the customers who have been in within the last 365 days, or whatever amount of days I choose.

[pbaldy]

It's certainly possible, but generally you'd leave the older transactions so that you had a history of the customer's activity. It's a simple matter to find the last transaction to calculate the overdue customers.