Can a query do this?

[schof99]

Attached is a very simplified version of what I'm working with - privacy rules and all that!



I have no control over the way the data dumps into tblEMRDataDump (unfortunately).

What I'm trying to do is figure out a way to show how long has lapsed between the last contact with one customer and the first contact with the next customer.

This needs to be done for each employee each day. There are 300 employees, with up to 10 customers each a day, and I need to track the data back 2 years - that's a lot of records!

I thought about maybe using a query to create a new table that has one record for each employee, each day, with Cust1Contact1, Cust1Contact2, Cust2Contact1, Cust2Contact2 etc etc...

However, I got myself really confused when trying to do this with queries so scratched what I had and went back to the drawing board.

Am I approaching this the right way? If so, any suggestions on where to start. Or any of you guys have a suggestion for a better approach?

[Micron]

and the first contact with the next customer

Are you sure you don't mean the next contact with the next customer? Having to distinguish between that and what you're asking (the first) is a different matter.
If you input the max per day I think that's about 15000 records per week. With Access, you will probably find your database approaches the limit in about 3 to 5 years, depending. You may have to break it up into periods. I didn't download your file, preferring to see if you change your requirements first. I suspect you'll need a "tblContact" table that has fields for employee id (FK to employee table PK) and a customer id (FK to customer table PK) and Start and End date/time fields. A form will have to trigger the start and end of a contact, writing the first 3 fields as a new record at start. When the contact is complete, an update query will be needed to write the end value. To get the elapsed times for anyone will require queries.

Others here might have better ideas.

[schof99]

Thanks for the response. Sorry, the question probably isn't very clear.

Basically if employee Joe Smith has four points of contact with a customer Fred Jones then moves on to customer Mary Williams I need to know how much time elapsed between him finishing with Fred and moving on to starting with Mary. This needs to be done for each gap between customers each day.

The wheels have begun to turn to get this data captured in the original report I receive. It'll take a while so this database is a stop gap and a way to go back into the historical data which cannot now be reformatted at source.

Does that make it any clearer?

[schof99]

I should probably add that each employee deals exclusively with one customer at a time. There will never be an instance where the same employee is dealing with more than one customer at a time.

[Micron]

Thought so. Then the method I outlined (you'll need a "tblContact" table...) would be how I would approach it. A form button would create the end of a contact and another one would start the next. I suppose you'd need to prevent someone from creating concurrent starts - probably with an invisible form checkbox that you check/uncheck upon starting/ending.

[schof99]

I wasn't planning on using any forms. This data comes from one .txt report and I just need to manipulate using queries to produce the reports I need. Why would I need to use a form if there isn't going to be any data entry?

[Micron]

Well maybe it's a combination of me missing that it was only a data dump, plus your database example has a form in it. As for your datadump table example, the structure is like a spreadsheet with information stored in columns (Contact#) which is not conducive to the way databases should be structured. What I don't get about this layout is that if there are 300 employees who may have to make 10 contacts daily, it would require 3000 contact fields in a table, which is not possible. If this is what you're stuck with because of a data dump, I see no way of getting what you want. I could suggest a table structure that might help, but it would be pointless if you're bound by the layout of this data dump. It would require that the data is stored in rows.

[ssanfu]

I have no control over the way the data dumps into tblEMRDataDump

Q) Every day the table "tblEMRDataDump" has data added to it (or on some frequency)? Or is the data deleted before the new data is added?

Q) What format does the data come in? .txt file, .csv file, ...?? How do you get the data into the table?

Q) You have 4 contact time fields. Will every record ALWAYS have 4 times?


I don't think a query will be able to handle this easily. My preference would be to use VBA.

[ssanfu]

Attached is an example of the VBA code to calculate the elapsed time in minutes.

The code assumes that all 4 contact (time) fields have values.