Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2013
    Posts
    16

    Unanswered: extracting info from 2 different tables

    Hi,

    I'm creating a database for a charity and I have 2 tables - 1 for private donors (private donor codes are DI0001, DI0002, DI0003 etc) and 1 for company donors (company donor codes are DC0001, DC0002, DC0003 etc).

    If I have a table for "new donations" one of the fields should donor - in which case how do I oull put the unique donor codes from the 2 tables?When the user gets to this bt where they are required to input the donor code, the options should be ALL donor codes beginning with DI and DC, but since they arein 2 seperate tables, how do I do this?

    Thank you!

  2. #2
    Join Date
    Jan 2012
    Posts
    97
    Hi there,

    Not 100% clear on your question but think this could be the solution you are looking for.

    To collate information stored in two separate tables use a simple select query.

    SELECT tablename.fieldname, tablename.fieldname
    FROM tablename
    WHERE tablename.fieldname = somecriteria 'note the where clause is optional

    I am assuming here that you want to use the information you have stored in the two tables as a data source for adding new donations.
    Hope this helps,

    Dave.

    _____________

    Access 2000 User

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Ideally all donors should be stored in a single table. A donor being a private one or a company is an attribute of the Donor item. If you would create a friends address book, you would not store male friends in a table and female friends in another, that's the same. In a single table, an additional column would specify what kind of donor it is.

    In you situation, you can always use a UNION query to merge both tables, provided that they have the same structure (i.e. the same number of columns and the same type for each column, at least for the columns you want in the resulting data set). Be aware that such a query is read-only and, if it's used as the RecordSource for a form, this form will be read-only too.

    Since you can identify both tables from the contents of their donor code (DI or DC) you could proceed in two steps:
    a) Use the UNION query to identify the table.
    b) Use the found table in a SQL expression, as the RecordSource property of a Form, etc.
    Have a nice day!

  4. #4
    Join Date
    Mar 2013
    Posts
    16
    Hi,

    Thanks for your replies...

    I've tried a simple query wizard and keep getting the same error message:

    "you have chosen fields from these tables [X,Y]; one or more of the tables isn't related to the others. click ok to edit system relationships. you'll need to restart the wizard etc..."

    I'm not sure what to do now, I even made 2 new tables with exacly the same field names and properties etc, and it still doesntwork.....any suggestions?

    Thank you!

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You dont need a Query Wizard for creating the UNION query. Open a new query in design mode, switch to SQL view then type:
    Code:
    SELECT [Col1], [Col2], [Col3], etc... FROM [Table1]
    UNION
    SELECT [Col1], [Col2], [Col3], etc... FROM [Table2]
    and save the query under the name you want.

    Table1 and Table2 are the names of both tables and Col1, Col2, Col3, etc. are the names of the columns in each table. The names of the columns can be different from one table to the other, provided that the corresponding columns are of the same type in both tables.
    Have a nice day!

  6. #6
    Join Date
    Mar 2013
    Posts
    16
    Hi,

    I've tried doing a union query....this is the SQL i types in:

    SELECT [Individual donors].ID, [Individual donors].[Full Name]
    FROM [Individual donors]
    UNION ALL SELECT [Company donors].ID, [Company donors].[Full Name]
    FROM [Company donors]
    ORDER BY ID;

    The problem is the bit in bold - I want to order it by ID but then in database view it ignores the IDs I created (DC0001, DC0002, DC0003 ect for company donors and DI0001, DI0002, DI0003 for individual donors) - it shows up as 0001, 0002, 0001, 0002 instead which leaves out the DC and DI prefixes, and therefore I have multiple records with each ID (as there's no DC or DI before each number) which defeats the purpose of having a unique ID for each record.

    Please can someone help m with the "ORDER BY" bit of the SQL so that the DC and DI prefixes show in the union query.

    Thanks

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If there is no DC or DI prefix in the resulting data set, it means that such prefixes do not exist in the ID column of both tables. You can add them using:
    Code:
    SELECT 'DI' & [Individual donors].ID AS ID, [Individual donors].[Full Name]
    FROM [Individual donors]
    UNION ALL 
    SELECT 'DC' & [Company donors].ID AS ID, [Company donors].[Full Name]
    FROM [Company donors];
    Have a nice day!

  8. #8
    Join Date
    Mar 2013
    Posts
    16
    Thank you Sinndho, that worked! Its been bugging me for ages!

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  10. #10
    Join Date
    Mar 2013
    Posts
    16
    By the way, do you think there's an easier way to create IDs? Ths is how I did it.....(e.g. in company donors table) in design view I selected Text as the Data Type and then > General > Input Mask > "DC"00000.....so in database view I type in the zeros and then 1, 2, 3 etc at the end. But because its not automatically generated like AutoNumber, the users would have to input the next number themselves right? In General > Indexed > I chose "Yes (No Duplicates)" in order to ensure the user won't be able to create new donor records with IDs already in use, but how would the user know which is the next number to start from when creating new donor records?

    Thanks!

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I would use an Autonumber data type and provide the prefix in a query, as explained before. From my viewpoint, the less you rely on users for data coherence, the best it is for the health of your database.
    Have a nice day!

  12. #12
    Join Date
    Mar 2013
    Posts
    16
    That does sound way more sensible than the way I'd done it before! And easier....thanks!

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •