AboutScottgem Expertise I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.
Experience I've been designing databases for over 15 years working with dBase, FoxPro, Approach and Access.
Organizations Author of Microsoft Office Access 2007 VBA Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing
Thanks as always for your thoughtful and timely responses. My database, though rudimentary, seems to have come a long way. I have a few additional questions.
1. A technical question. I would like to format a number, which will be used as our patient identification number. This is different from the PatientID autonumber, which serves perfectly well to uniquely identify patients. I must recreate the program patient identification number, as it's something the program has always used. I don't have to store it in the tables per se but it does have to be on reports, and as such can simply be a query.
The algorithm is as follows: This is a 10-number code. The first 3 numbers are the site code (3-digit codes associated with each of our provider sites); the next 2 numbers are associated with the first letter of the last name, ie A=01, B=02,...Z=26; the next 4 numbers are sequential numbers, eg 2550, 2551, 2552 (although I am not sure for the moment what number they begin with); the last number is the last digit of the year, eg 2004="4".
Can I create some tables or combo boxes with 2 columns and pull from them the numbers for the site code, last name, and last digit of the year? Ideally, I want the number to be pulled from the data that is input about each client on the form. I understand there is a way to auto-generate sequential numbers as well. And then one must format the 4 fields using & ? Any suggestions are appreciated.
2. Perhaps more importantly than #1 above, I am trying to figure out a way to have information in my tables updated via information that is queried. I have queries/reports that will run indicating those clients that are due to receive transportation vouchers to appointments. The user runs the report every week, prints pre-made labels, mails them off... and then will have to go back to the main form and hand check those clients who were sent vouchers, as well as the date they were sent. This means searching for records on the form and clicking check boxes and filling in the dates.
I am sure the experts know of a way to auto-update these records by way of an update query or append query upon printing the labels or printing the report. Is there a reasonable way to do this without resorting to complex code or expressions? Or is there another, easier way to approach this that I am not thinking of?
3. Finally, any links you might provide, Scott, regarding where to research using combo boxes (or list boxes) as parameter queries for the user, ie so that user does not have to type name of parameter correctly in a query or report?
The question here becomes where does the SEQNUM come from. Does your system have to generate it or is stored someplace when the patient data is entered?
2. I'm not clear on this, but anything that can be pulled in a SELECT query can then be appended to a table or used to update records. As an example: you run a select query of all those due transportation vouchers. And use that query to print labels to mail the vouchers. After printing, you can then run an update query to update the appointment records that the vouchers were sent. Lests say the Select query is named qryVouchersDue and there is a Yes/No field indicating the vouchers were sent named VoucherSent. Your Update query would look like this:
UPDATE tblAppointments SET VoucherSent = True
WHERE AppointmentID IN(SELECT AppintmentID FROM qryVouchersDue);
To append records you make a copy of your SELECT query and turn it into an APPEND query.
3. Parameter queries are queries where you embed a prompt that asks the user for a value. You do NOT want to use Parameter queries, what you want to do is use a form to enter criteria and then reference the value in the control in your query criteria. This works because you can reference the value of a control on an open form using the syntax:
=Forms!formname!controlname
As an example, you might have a report that should only list a specific, user selected site. So you would open a form (frmListbySite) and add a combobox (cboSite) to select a site. Then, in your query you set the criteria in the site column to:
=Forms!frmListbySite!cboSite
When the report opens it reads the query whihc looks to the form for its value. That's all there is to it.
Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA
---------- FOLLOW-UP ----------
QUESTION: Hi Scott,
The sequential number is a number from 0-1000, then they recycle.
Your answers on questions 2 and 3 did the trick.
Issues I am currently having that perhaps you might help with:
1. Importing information into DB - there is another database (which is more sophisticated than mine, but which doesn't capture the info that mine does) that our program uses. Once per month, we will be exporting data from the other DB and attempt to import into my DB. I lined up the fields and can successfully import. However, I noticed that there are a considerable # of duplicate records in the data table I would like to import. I cannot seem to remove them "on the outside" (from the other DB). I've tried 2 methods for removing the duplicate records once in my DB: (1) Using the indexed field for program ID code in the table and setting it to Yes (No Duplicates). This removes way more of the records than just the duplicates. Also, for those records that do not have a duplicate program ID code, I cannot find duplicates. I've tried using 2 primary keys and setting indexed fields for both to no duplicates, but with the same poor outcomes. (2) I investigated the Find Duplicates Query Wizard and utilizing a delete query, then creating a new table with only unique records. This seems to be a disaster in the making (?) b/c I will be making ever more "new" tables - is this a robust way to run a DB? Any thoughts/ideas/suggestions?
2. Reports - I've made a number of ready-made reports (see attached screenshot), which more or less capture information I think the program will need. Several problems, though.
Routinely, the user will want to make unique reports for each individual provider - for appointment requests, attendance log inquiries. As it currently stands, the user will have to select a provider from the combo box I've made, then click to open the report that lists information for that provider; then, repeat for the other 5 or 6 (or perhaps in the future many more) providers. It's extremely inefficient. If I include all providers on one report, grouping by provider, I end up with a long report, with numerous providers on each page. The purpose of the report is to have providers on their own page, so that the page can be printed and faxed, or emailed. I thought of making numerous individual sub-reports, then aggregating into one giant report, but that seems like an awful lot of work. Ideas?
Second, I would like the user to be able to open PivotCharts directly from a link on my form. I've formatted the query that creates the chart to a default view of PivotChart (in the query design view). Oddly enough, the query continues to open up in database view. Am I missing something? Or is there an easier way to let the user just export from the database view to Excel, to make reports there? (There doesn't seem to be an export to Excel option in the database view of the query.)
Thank you in advance for your time and assistance, and for the very useful help you consistently provide.
Geoff
Answer First, on the sequential number. Since they want a number from 1-1000 and will start the count at 1 when it gets to 1000, you need keep track of when it resets. To do that I would have a one record table (call it tblParameters) to store such things. So you have a field called lastResetDate. Also, you will have to have your datefield in the record, show the full date and time the record was created or a second field to do that.
Second, You need to generate this number just prior to saving the record to avoid duplication. So I would have a button on the form to generate the number with code like this:
IF intSeqNum = 1000 Then
intSeqNum = 1
CurrentDB.Execute "UPDATE tblParameters SET LastResetDate = #" & Now() & "#;"
Else
Me.txtSeqNum = intSEQNum + 1
End If
This will reset the sequential number to 1 when it reaches 1000 and reset the LastResetdate.
Now for the current questions:
1. First you need to define what constitutes a duplicate and why does the other system allow them. Second, have you thought of creating an ODBC link to the other database rather than doing an export/import?
2a. Use a MultiSelect Listbox to allow the user to select multiple providers. I would write the selected providers to a temp table that is joined to the query the report is based on. This will produce reports for the selected providers. You can then loop through this recordset to e-mail the report.
2b. I actually haven't worked that much with Pivot Tables and charts (more in Excel than Access). I beleive you need to create a form with the chart and have the user open that form.
Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA
)
 check those clients in query who were actually sent the vouchers and (b) the date vouchers were sent')){kind=link}