Welcome to SPN

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

Sign Up Now!
  1. Guest ji, please consider donating today!
      Become a Supporter    ::   Make a Contribution   
    Monthly Recurring Target: $300 :: Achieved: $95

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?
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page