This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
My boss asked me to do a new Access database from scratch. I’ve never done an Access database on my own before, and would appreciate any advice you guys have. This is to help employees schedule appointments for 46 medical doctors in 14 different medical offices.
As for my relationships, I hope I’ve set up everything correctly:
As you can see, I’ve set up two junction tables, but am not quite sure how to set up a search query with junction tables. I’ve looked in my two Access 2013 books, and I’ve also looked online but am not having much luck. For example, with the table Junction_Provider_Insurance, I’m not sure how to search the Junction table to pull up the doctor and if the doctor is contracted, not contracted, needs authorization or pending for the insurance. So if a doctor’s name and an insurance name such as Aetna is searched, I want the result to show that the doctor is or is not contracted with Aetna in ReportResults.
As for the search, I originally set up a wildcard search for all the search boxes. My original intention was that the user could type in anything they wanted in one or two or three of the boxes (such as doctor’s name, visit type and insurance) and do a search where the results come up. However this is not working at all and time is of the essence as I’ve already missed my deadline to turn in this a week ago.
Due to missing the deadline, I decided to change the wildcard text search to a combo box for the Doctor’s Name, Medical Office Name, Visit Type, Specialty and Insurance in the hopes the search will finally work. I’m thinking the best way to handle this is to remove the medical office, and then just have the doctor’s name, then when that comes up, to cascade into a new combo box for the visit type (new patient, follow up, pre-op, post-op, etc.) Or is there a better way to do this? I still need the results to show in the report too.
Thank you so much for your time and help. I really appreciate it very much.
A family of Microsoft relational database management systems designed for ease of use.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Good morning Gina,
If you look at the screen shot, one appointment repeats itself several times. I've had it happen to where the same appointment repeats 20 to 30 times. I've marked it in red. I don't know why it's doing that.
Look in the query that is the recordsource. The query has duplicate records so will the report which is why I was saying one normally uses subreports to prevent situations like this.
If the report's RecordSource query is returning rows which are exact duplicates over all columns then you should be able to return a single instance of each row by means of the DISTINCT predicate in the SELECT clause. The following simple query in Northwind, for example, will return duplicate rows for those companies who have paid by credit card for more than one order:
SELECT Company, [Last Name], [First Name]
FROM Customers INNER JOIN Orders
ON Customers.ID = Orders.[Customer ID]
WHERE [Payment Type]="Credit Card"
ORDER BY Company;
The following, on the other hand will return one row per company by virtue of the use of the DISTINCT predicate:
SELECT DISTINCT Company, [Last Name], [First Name]
FROM Customers INNER JOIN Orders
ON Customers.ID = Orders.[Customer ID]
WHERE [Payment Type]="Credit Card"
ORDER BY Company;
If the report's RecordSource is not returning exact duplicates per row, however, then there may well be columns returned by the query which do not have distinct values over each subset of rows, but are not shown in the report. These redundant columns can be removed from the SELECT clause. With the above example, for instance, the following variation would not return one row per company because the Order Date column, whose values are not distinct per company, has been included redundantly in the SELECT clause. The DISTINCT predicate therefore achieves nothing:
SELECT DISTINCT Company, [Last Name], [First Name], [Order Date]
FROM Customers INNER JOIN Orders
ON Customers.ID = Orders.[Customer ID]
WHERE [Payment Type]="Credit Card"
ORDER BY Company;
Thank you.
That makes a lot of sense Gina. I ran the "find duplicates query wizard". I figured out the duplicate number matches the SubSpecialities subreport exactly. So I may need to redo the "main query" to remove the Subspecialties from that report.
I'm not sure the SELECT DISTINCT suggested above would work so it may be best to redo the "main query".
In your case I don't think it would. You are going to need to break the report up.