Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

searching over multiple tables

Discussion in 'Information Technology' started by Bob, Jul 28, 2006.

  1. Bob

    Bob
    Expand Collapse
    Guest

    I have one table with customer information, including a customer ID
    (tblCustomers). Each customer can generate on of two types of invoices -
    each with their own set of criteria, therefore each invoice type has it's
    own table (tblMfgInvoice, and tblSvcInvoice)

    Each customer will have an Invoice number (PK) in one of these invoice
    tables, potentially a customer can have an invoice in both tables.

    I know there has to be a way to query or script something like
    find/list all records in tblMfgInvoice records with CustomerID.
    then find/list all records in tblSvcInvoice with same CustomerID
    Display the criteria of each invoice found

    Is this possible?
     
  2. Loading...

    Similar Threads Forum Date
    World War 1 Sikh-Canadian Pioneers: Still Searching for Our Sikh War Heroes History of Sikhism Feb 20, 2011
    Sikh News US to begin searching Sikh turbans (Bangkok Post) Breaking News Sep 3, 2007
    Sikh News Sikh community condemns policy on searching turbans (Oakland Tribune) Breaking News Aug 29, 2007
    Searching for an answer Spiritual Articles Mar 22, 2006
    Soul-searching Doctors Find Life After Death Interfaith Dialogues Nov 15, 2004

  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    The best solution would be to combine tblMfgInvoice and tblSvcInvoice into
    one table, probably with an extra field to indicate which type of invoice it
    is. You can still use separate forms for entering the invoices if you wish.
    Each form would be based on a query that chooses just the appropriate
    invoice type.

    If that is not possible then the fields of the 2 tables must be radically
    different. That means a UNION query that tries to combine the 2 into one
    long list will not work very well, because the fields won't match, and yet
    you want the details of each invoice found.

    If you cannot combine them at the table level, and cannot UNION them at the
    query level, all you are left with is showing 2 resultsets, i.e. show two
    queries (or forms) that give the 2 sets of results.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Bob" <poopsie@comcast.com> wrote in message
    news:3etdg.3944$rS6.285@fed1read11...
    >I have one table with customer information, including a customer ID
    >(tblCustomers). Each customer can generate on of two types of invoices -
    >each with their own set of criteria, therefore each invoice type has it's
    >own table (tblMfgInvoice, and tblSvcInvoice)
    >
    > Each customer will have an Invoice number (PK) in one of these invoice
    > tables, potentially a customer can have an invoice in both tables.
    >
    > I know there has to be a way to query or script something like
    > find/list all records in tblMfgInvoice records with CustomerID.
    > then find/list all records in tblSvcInvoice with same CustomerID
    > Display the criteria of each invoice found
    >
    > Is this possible?
     

Share This Page