Results 1 to 9 of 9
  1. #1
    Travis is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    15

    Question Query provides no records (Two tables)

    Hi. This Newbie is hopelessly confused.

    Trying to build a simple database of my medical appts. Two tables:

    The "one" side:
    Providers table: name address phone, etc. (At the moment, has 13 records in it.)

    The "many" side:


    Appointments table: Date, time, etc. (At the moment, has 3 records in it.)

    Relationship: for the first several tries, the relationship status was "indeterminate". Finally figured that out and "Relationships" now properly shows one-to-many. Primary key in "Providers" is ProviderID which becomes a foreign key in Appointments.

    But when I use the Query Wizard, include both table and all fields within both fields, no records are selected.

    I asked about a form that would include all info the other day, but then figured out that if the query isn't working first, the form never would!

    Please, can someone help me figure out where to begin?

    Click image for larger version. 

Name:	Medical Appts SS 001.png 
Views:	20 
Size:	107.6 KB 
ID:	43616

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    start simple, just the 2 joined tbls.
    remove all the duplicate "_#" tables.
    run query
    does it work?

  3. #3
    Travis is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    15
    Nope. There are only two tables. Re: The "Providers_1", "Providers_2", and "Appointments_1" tables: I didn't create them, and while I can hide them, I don't see any way to delete them. Is that part of the problem?

    BTW, thank you for your replies.

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Could you maybe run a comact and repair then zip the file and upload it here so we could see what you have (please remove any sensitive data).

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    Travis is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    15

  6. #6
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Look at your query in design view. You should not have the LName fields linked.

    Why do you have the ProviderID (PK) field linked to the ApptDate field?? There should be a link between Providers.ProviderID and Appointments.ProviderID.
    To limit the returned records to a date or a range of dates, filter on the ApptDate field
    Click image for larger version. 

Name:	Query1.png 
Views:	19 
Size:	33.5 KB 
ID:	43620
    Attached Files Attached Files

  7. #7
    Travis is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    15

    Lightbulb SSanfu: Thank you! There is progress!!

    I removed the relationship between LName fields in the tables. That helped.

    Q: "Why do you have the ProviderID (PK) field linked to the ApptDate field??"

    A: Because my mind was mush at that point and I clicked on the wrong thing. 😁

    The query now reports everything that's in the Appointments table correctly, and I presume that I'll be able to do a form and/or report from the query.

    If I may: Should I not be able to enter a new appt from the query? There is the blank line at the bottom. But if I try adding another appt with a provider already used in the query, it won't allow me to enter the same Appointments.ProviderID.

    I suspect that it may not be a great idea to input a new appt using the query, but I'm exploring what can and can't be done.

    Either: (A) any comments on that, or (B) you don't necessarily want to carry on this conversation forever, which I would understand, and would seek opinions/guidance from another kind forum user!! 😁


  8. #8
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by Travis View Post
    If I may: Should I not be able to enter a new appt from the query?
    When you have 2 or more tables in a query, the query (normally) becomes non-editable. You are better off entering new records using a form.
    The query should work ok for a report, but not for a form ( the non-editable thing)

  9. #9
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I looked at your dB a little more and have a few suggestions.

    I would have tables:
    Click image for larger version. 

Name:	Relationship1.png 
Views:	5 
Size:	25.6 KB 
ID:	43668

    You would have a form to Add/Edit/Delete Patients
    You would have a form to A/E/D Providers
    You would have a form to A/E/D Appointments

    You could have a Form/sub form to select a Provider to see the appointments/Patients (Main form Provider, sub form appointments/Patients)
    You could have a Form/sub form to select a Patient and see the appointments/Providers (Main form Patients sub form appointments/Provider)


    Good luck with your project....

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 04-25-2016, 10:39 AM
  2. Replies: 4
    Last Post: 03-29-2014, 01:29 AM
  3. Replies: 2
    Last Post: 04-17-2012, 12:18 PM
  4. Replies: 3
    Last Post: 01-05-2012, 12:04 PM
  5. Replies: 6
    Last Post: 02-10-2011, 07:09 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums