Regular Callers / Demand Management - SOLVED

[insomnia79]

Hi everyone,

I am trying to put together a database that will show the number of times a person has been seen, the duration between those times and a count of how many times that person has been seen.

end result example:
Person 1 Seen on 01/04, 03/04 and .10/04
Person 2 seen on 02/04, 05/4

data example:
person 1, 01/04, abc123
person 1, 03/04, abc123
person 2, 02/04 qwe456
person 1, 10/04, abc123
person 2, 0504, qwe456

I have amongst other things the following fields:-
Person surname
person first name
date seen on
persons postcode

What I am attemptiing to do is to limit the Postcodes down to just those where there is more then one recorded value and put teh results in a query.

Next I attempted to reduce the number of surnames to where there was 2 or more of the same and put the results into a query.

Finally I matched up the postcode to the postcode field in teh original table, and did the same with the person surname.

This does sort of work, though I am geting a lot of duplicate entries in teh original table where the persons surname, firstname, date and postcode are all teh same (this is duplication of the entire row for that person, not such those fields mentioned above).

Am I going about this the right way?

How can I lose all records that are identical (duplicated), and then use what is left over to compile a report that will then identify reguler persons over time?

A pointer in the right direction would be ample information, I am not looking for anyone to do this for me!!

Many thanks.

P

 

[CT Witter]

See the following post on how to combine the records into 1 row http://www.mrexcel.com/board2/viewtopic.php?t=193978

HTH,
CT

 

[NumbersMax]

On the query, if the totals row is not visable, click the Greek symbol menu button (Sigma, I think) or right click in the grid and choose Totals.
Default is generally "Group By"

If you run the query with the totals row showing, try to change the date field from Group By to Count. You will need to change from Group By to some other Total function for each field in the query that will change across the various visits (e.g. for a dentists office, if they had a procedure done and you chose that column and there were 3 procedures over 5 visits, it would give you 3 instances unless you delete that column or change it to Where (which auto unchecks the "Show" box)) or some other function that return only 1 entry (MIN, MAX, First, Last, etc)

Changing to Count on the date will have the effect of allowing only one line per person/postcode regardless of the dates of visit and will give you the number of times they visited. You can use the result of the Count to sort and see who your most regular visitors are if necessary.

Is that something like what you had in mind?
Max

 

[insomnia79]

Thanks for the comments, I ran a make table query that outputted the basics of what I wanted to a new table. I then exported that to a spreadsheet, ran the formula on the cells I wanted and then linked the sheet back into database.

It at least proves teh information that I have until I can put something together entirely in access.

Thanks.