Results 1 to 3 of 3
  1. #1
    Gina Maylone is offline Always learning
    Windows 7 64bit Access 2013
    Join Date
    Jun 2013
    Location
    Afton, MN
    Posts
    544

    Need to add a single record to a table using an sppend query with a one to many relationship

    Good morning,

    I have tables Account (PK acctnum) and Orders (linked via acctnum to Account). There are multiple orders per account that need to be billed individually. When creating an invoice I need to add the Account to the Invoice table and the Orders to the InvoiceDetail table. I use 2 queries to accomplish this. The first one though adds multiple records to the invoice table (because of the one to many relationship). I need to keep that so I don't bill anyone with no orders.

    INSERT INTO Invoices ( InvoiceDate, AcctNum )
    SELECT DISTINCT Date() AS InvoiceDate, Account.AcctNum
    FROM Account INNER JOIN [Order] ON Account.AcctNum = Order.AcctNum
    WHERE (((Order.OrderNum)=[Forms]![Account]![Order].[Form]![OrderNum]) AND ((Account.BillingCycle)="on discharge") AND ((Order.EndDate) Is Not Null) AND ((Order.Closed)=Yes) AND ((Order.PatientName)=[Forms]![Account]![Order].[Form]![PatientName]));

    Is there any way to get it to only add one record? I've tried limiting it with several parameters to no avail.



    TIA! Have a great day!
    Last edited by June7; 11-21-2014 at 02:07 PM.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    You want one record for one patient OR one record each for multiple patients? Since you include a parameter for a single patient I am guessing the first is the case but only if that patient has orders that meet criteria?

    I would use VBA to run an SQL action, not a saved Access object. And only run the action if conditions are met.

    If DCount("*", "Order", "OrderNum=" & Forms]![Account]![Order].[Form]![OrderNum] & " AND Not IsNull(EndDate) AND Closed=True") > 0 Then
    CurrentDb.Execute "INSERT INTO Invoices (InvoiceDate, AcctNum) VALUES (Date()," & Me.AcctNum & ")"
    End If

    The actual syntax of the condition depends on where the code is run from (main or subform).
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Gina Maylone is offline Always learning
    Windows 7 64bit Access 2013
    Join Date
    Jun 2013
    Location
    Afton, MN
    Posts
    544
    YES! that worked! Thank you June7!!!

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

Similar Threads

  1. Replies: 2
    Last Post: 12-05-2013, 12:09 PM
  2. many-to-many relationship to a single table
    By roleic in forum Database Design
    Replies: 2
    Last Post: 08-01-2012, 03:41 PM
  3. Replies: 4
    Last Post: 11-07-2011, 07:25 PM
  4. Replies: 5
    Last Post: 03-23-2011, 10:39 AM
  5. Replies: 7
    Last Post: 11-13-2010, 08:08 AM

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