Results 1 to 15 of 15
  1. #1
    New Lounger
    Join Date
    Aug 2009
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Could you possible tell me how I can build a query that tells me how many unique clients I have, identified by a unique client number? There are lots of repeats in this dataset. I have tried using the group function to no avail. I need to find out what they have been prescribed on a first visit..so want to create a table of first visits in the year only, have tried using first function in the total row without success. I then want a query that craetes a table of repeat visits only.

    sigh

    I think a problem maybe that I have client no, age, prescription, gender and age may have changed during the year? Prescription will have changed many times for the same client too.

    Would appreciate any help

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Welcome to the Lounge!

    To get a count of unique client numbers, you need two steps:

    1. Create a query based on your table.
    Add only the client number field to the query grid.
    Select View | Totals to make it a totals query.
    This query will produce a list of unique client numbers.
    Save it as (for example) qryUniqueClients.

    2. Create a query based on qryUniqueClients.
    Add the client number field to the query grid.
    Select View | Totals.
    Change the Total option for the client number field to Count.
    This query will return the count of unique client numbers.

    To get the first prescription during a year, you need several steps too. Do you want to do this only for the current year, or for all years?

  3. #3
    New Lounger
    Join Date
    Aug 2009
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='788602' date='12-Aug-2009 14:17']Welcome to the Lounge!

    To get a count of unique client numbers, you need two steps:

    1. Create a query based on your table.
    Add only the client number field to the query grid.
    Select View | Totals to make it a totals query.
    This query will produce a list of unique client numbers.
    Save it as (for example) qryUniqueClients.

    2. Create a query based on qryUniqueClients.
    Add the client number field to the query grid.
    Select View | Totals.
    Change the Total option for the client number field to Count.
    This query will return the count of unique client numbers.

    To get the first prescription during a year, you need several steps too. Do you want to do this only for the current year, or for all years?[/quote]

    Thank you. Will try now (apols for typos in first message) I'm actually looking at the first six months this year but do have the data from 2008 and would be interested to compare? Not sure if this answers your question. Thank you very much for taking the time.

  4. #4
    New Lounger
    Join Date
    Aug 2009
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='788602' date='12-Aug-2009 14:17']Welcome to the Lounge!

    To get a count of unique client numbers, you need two steps:

    1. Create a query based on your table.
    Add only the client number field to the query grid.
    Select View | Totals to make it a totals query.
    This query will produce a list of unique client numbers.
    Save it as (for example) qryUniqueClients.

    2. Create a query based on qryUniqueClients.
    Add the client number field to the query grid.
    Select View | Totals.
    Change the Total option for the client number field to Count.
    This query will return the count of unique client numbers.

    To get the first prescription during a year, you need several steps too. Do you want to do this only for the current year, or for all years?[/quote]

    Hans I do also need the gender and age with the client number, I need to see what age and gender clients are being prescibed what substance and how this changes with repeats. Is this possible?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    It depends on how generic you want to make your queries.

    You could do the following:
    - Create a query based on your table.
    - Add all fields (or all those you need) to the query grid.
    - Add the following condition to the date column (assuming you're using UK date format):

    Between #01/01/2009# And #30/06/2009#

    - Save this query as (for example) qry2009First.

    - Create a new query based on qry2009First.
    - Add all fields to the query grid.
    - In the first empty column, enter a calculated field:

    SeqNo: DCount("*", "qry2009First", "[ClientNo]=" & [ClientNo] And [DateField]<=#" & Format([DateField],"mm/dd/yyyy") & "#")

    where ClientNo is the name of the client number field and DateField is the name of the relevant date field.

    - The SeqNo field will be 1 for the first record of a client, 2 for the second record etc., so you can use it to distinguish first and repeat visits.

    You can do the same for the second half of 2008.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='whale' post='788609' date='12-Aug-2009 15:51']Hans I do also need the gender and age with the client number, I need to see what age and gender clients are being prescibed what substance and how this changes with repeats. Is this possible?[/quote]
    My first reply just tells you how to get a count of unique clients - your first question. I have posted another reply in the meantime that tells you how to distinguish first and repeat visits. That should help with the other questions.

  7. #7
    New Lounger
    Join Date
    Aug 2009
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='788611' date='12-Aug-2009 15:04']My first reply just tells you how to get a count of unique clients - your first question. I have posted another reply in the meantime that tells you how to distinguish first and repeat visits. That should help with the other questions.[/quote]


    Thank you Hans. I'm off home in a minute but will try this out tomorrow.

    Best wishes

    Whale

  8. #8
    New Lounger
    Join Date
    Aug 2009
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='788610' date='12-Aug-2009 15:03']It depends on how generic you want to make your queries.

    You could do the following:
    - Create a query based on your table.
    - Add all fields (or all those you need) to the query grid.
    - Add the following condition to the date column (assuming you're using UK date format):

    Between #01/01/2009# And #30/06/2009#

    - Save this query as (for example) qry2009First.

    - Create a new query based on qry2009First.
    - Add all fields to the query grid.
    - In the first empty column, enter a calculated field:

    SeqNo: DCount("*", "qry2009First", "[ClientNo]=" & [ClientNo] And [DateField]<=#" & Format([DateField],"mm/dd/yyyy") & "#")

    where ClientNo is the name of the client number field and DateField is the name of the relevant date field.

    - The SeqNo field will be 1 for the first record of a client, 2 for the second record etc., so you can use it to distinguish first and repeat visits.

    You can do the same for the second half of 2008.[/quote]
    Dear Hans

    I'm afraid I don't have more than basic access skills, I am receiving an 'invalid date field' message. I don't really understand what I'm doing, what the expression means.

    I copied and pasted the expression above into the field row of the query after the columns with selected headings from the original dataset. I changed DateField to Visit Date to reflect the exact heading with the dates and ClientNo to ClientNum...as on the original dataset?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Could you make a copy of the database, remove everything that is not relevant to the problem (and all sensitive data), then compact the copy (Tools | Database Utilities | Compact and Repair Database), and finally zip it and attach it to a reply?

  10. #10
    New Lounger
    Join Date
    Aug 2009
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='788775' date='13-Aug-2009 13:01']Could you make a copy of the database, remove everything that is not relevant to the problem (and all sensitive data), then compact the copy (Tools | Database Utilities | Compact and Repair Database), and finally zip it and attach it to a reply?[/quote]


    Thank you for the offer, our firewall blocks zipped files. I tried putting the formula in criteria but got the same date issue. Hans don't worry, I'm just trying to do something that I don't have the skills to do. Think shall just do it sorting visually in excel. Thank you for your time

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If you'd still like to pursue this in Access: is the Visit Date field a real date/time field, or is is a text field that contains values that look like dates?

  12. #12
    New Lounger
    Join Date
    Aug 2009
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='788804' date='13-Aug-2009 15:59']If you'd still like to pursue this in Access: is the Visit Date field a real date/time field, or is is a text field that contains values that look like dates?[/quote]


    It would be good to get it done in access, taking a while with excel and it's something we'll need to do in future. It is a Date/Time field. Am away for wk but will check in on my return. Thank you.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You mentioned that you can't upload a zip file to the Lounge. If I attached a zip file with a sample database, could you download it?

  14. #14
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='788610' date='12-Aug-2009 10:03']SeqNo: DCount("*", "qry2009First", "[ClientNo]=" & [ClientNo] And [DateField]<=#" & Format([DateField],"mm/dd/yyyy") & "#")[/quote]

    Aren't there missing quotes in this bit? Shouldn't it look like this?
    "[ClientNo]=" & [ClientNo] & " And [DateField]<=#" & Format([DateField],"mm/dd/yyyy") & "#"

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='PeterN' post='788986' date='14-Aug-2009 16:30']Aren't there missing quotes in this bit? Shouldn't it look like this?
    "[ClientNo]=" & [ClientNo] & " And [DateField]<=#" & Format([DateField],"mm/dd/yyyy") & "#"[/quote]
    Yes, good catch!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •