Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    164
    Thanks
    0
    Thanked 0 Times in 0 Posts

    is this possible (2000)

    I have two tables in which I'd like to combine somehow into a new table. I have one named register and the other named income. Is there a way to have them combine into a new table based around a timeframe?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: is this possible (2000)

    You'll have to be a tad more specific. How do you want to combine the tables?

    Are the tables (or can they be) linked and do you want to combine fields from a record in Register with fields from the corresponding record in Income? In that case you would create a query that uses a join between the two tables.

    Or do the tables have the same structure and do you want to add records from Income after the records from Register? In that case a union query comes to mind.

    And how does the time frame come into this?

    If you provide more information, someone may be able to help you.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    164
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: is this possible (2000)

    I apologize, as I should have been more detailed. Here goes...

    I have a table called income, which includes the following fields..

    income_id - autonumber(primary key)
    source - text
    date - date/time
    desc - text
    category - text
    amt - currency

    then the register table...

    register_id - autonumber(primary key)
    store - text
    amount - currency
    date_of - date/time
    pay_method - text
    card_type - number
    checknum - number

    Ok, as you can see this will function as a checkbook basically. I need to create a report with the combined tables. So I'm guessing I need to combine these tables into a new table, so I can do a report, or is it possible to link these two in a report. I hope this helps. I'm new to Access, so I'm trying to work through this. It's basically a tool that I hope will keep me better organized, plus will help me learn Access better.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: is this possible (2000)

    To create a report, you don't need to create a new table; you would create a selection query and use that as Record Source of the report. A query gets the current values from the tables each time it is run, so the report is always up to date.

    It's still not clear to me how you want to combine the two tables in your report - do you want one or more pages based on the Income table and one or more pages based on the Register table? Or is there some kind of link between the tables?

  5. #5
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    164
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: is this possible (2000)

    There is no relationship between the register and income table. Basically I just want a report where I see both, as if it were a real life checkbook register. See I have an income table and form for the user to input all their incomes each month. Then i have a register table/form tso the user can input all his/her charges. I jsut need to combine the two into a report. But I have no clue on how to do this.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: is this possible (2000)

    Anyone else with suggestions? I'm afraid I can't help here.

  7. #7
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: is this possible (2000)

    I'll try to pick up on this if I can.

    To be a bit more specific here.
    I notice the two tables both contain an autonumber as reference.

    income_id
    register_id

    But you want your User to enter his or her incomings and outgoings !!

    Is this where you Should have a unique ID to relate to eachother.

    I presume user(a) has incomings and outgoings and user([img]/forums/images/smilies/cool.gif[/img] also.

    If you create a field to relate each user, then you can base your report on All transactions or User transactions.

    If this is not correct, please reveal more details so we can help you.

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: is this possible (2000)

    I have another assumption, maybe what you need to drive the report is what Hans suggested earlier, and that is a UNION query.

    UNION queries are useful in that they can combine 2 or more tables of information into 1 stream of data.
    It could look like:

    SELECT 1 as TypeofRecord, source, amt, date, "" as pay_method, "" as card_type, "" as checknum, desc, category
    FROM Income
    UNION ALL
    SELECT 2 as TypeofRecord, store, amount, date_of, pay_method, card_type, checknum, "" as desc, "" as category
    FROM Register
    ORDER BY Date

    As you can see you must keep all fields of the query in order and data type even if you have to create dummy fields in one SELECT of the query.

    In your report you can define all the fields required for the different type of record (an Income record as opposed to a Register record) then in the Detail section of the report you can make certain fields visible or invisible depending on TypeofRecord from the query.

    If I have assumed incorrectly then post back and we will see what we can come up with.

    HTH
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: is this possible (2000)

    I think the problem is that you have separated items into two separate tables where in fact they are essentially the same item, the differences being one is income the other is outgoings.

    You have a table, which shows the source of income, the amount of income and the type of transaction. Then you have your outgoings table, which has essentially the same items, (the extra field
    Attached Files Attached Files

Posting Permissions

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