Results 1 to 5 of 5
  1. #1
    dropwater is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    3

    How to create a report that show all items with the same invoiceID

    Hi guys, I really need some help here. I am trying to create a little database for some invoice tracking.
    So far I have 2 tables , Record and IndRecord, and all customer information are in Record and the ID will be the primary key
    the IndRecord include ItemID ( not really needed , just for primary key ) , InvoiceID which is related to the ID in Record , item, amount etc.

    So I want to make a report that when I click print in the form i created, let say i am on ID 12, i want to print the customer information along with all the items and amount from the IndRecord which has the same ID
    For Example:

    [ID]
    [Name]
    [Address]
    [Phone]

    [InvoiceID]
    [Item] [Amount]
    || ||
    [Item] [Amount]
    (Like a list of items for ID 12 has bought )

    so far for my sql understanding
    it should be

    select Item, Amount
    from IndRecord I , Record R


    where I.InvoiceID = R.ID, ( and R.ID = the number shown on my form)

    And I got stuck, becuase I do not know how to create a report with only 1 ID and show all items belong to that ID from the IndRecord
    I tried to create a Query but even so, after I create a blank report and use the query to display, it will still show up all the items from the IndRecord

    I hope someone can explain for me , sorry for poor English ..

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    One way:

    http://www.baldyweb.com/wherecondition.htm

    i would join the tables instead of using a criteria, and you can refer to the form in the criteria as another way of filtering.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    dropwater is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    3
    Sorry, I still do not know how to play with my table and form and report. To be honest, i am like a beginner in access..

    PS. I can make my form do the thing like what i said, input any amount of items that only belong to the InvoiceID, but I cannot do the same thing when I tried to make a report , since I cannot enter the value for InvoiceID while in the form mode

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What are your tables?
    In business terms:
    Customers, Invoices and Items

    If an Invoice can be for the Purchase/Sale of multiple Items then you need a table between Invoice and Items to represent the InvoiceLineItem(s).

    Your basic tables and relationships would follow this pattern, where ----> represents a 1--->Many relationship

    Customer-->Invoice--->InvoiceLineItem<---Item

    Good luck with your project.

  5. #5
    dropwater is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    3
    Quote Originally Posted by orange View Post
    What are your tables?
    In business terms:
    Customers, Invoices and Items

    If an Invoice can be for the Purchase/Sale of multiple Items then you need a table between Invoice and Items to represent the InvoiceLineItem(s).

    Your basic tables and relationships would follow this pattern, where ----> represents a 1--->Many relationship

    Customer-->Invoice--->InvoiceLineItem<---Item

    Good luck with your project.
    Yes I kinda did that already, and I do not need the information for customer like customerID, so it make thing more simple I just create the Item and the invoice table which contain the all the information
    And I already joint the relation from ID ( 1 to many ) to InvoiceID in the item table ( since 1 invoice can have multi items that along to that invoice)

    I tried to create a template from mircosoft and copy the print marco with where condition ="InvoiceID = " & ID
    But still when I got to the invoice report that I create, the InvoiceID only show my first record ID and all items from the Item record, which I cannot control

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

Similar Threads

  1. Replies: 2
    Last Post: 09-18-2015, 02:22 PM
  2. Hide/Show a row on a multiple items form
    By Historypaul in forum Programming
    Replies: 10
    Last Post: 04-20-2014, 10:34 PM
  3. Form design to show items on an order
    By snipe in forum Forms
    Replies: 6
    Last Post: 01-31-2014, 02:01 PM
  4. Replies: 5
    Last Post: 10-28-2012, 03:26 AM
  5. Replies: 2
    Last Post: 06-09-2011, 09:47 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