Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Nov 2009
    Posts
    223

    Unanswered: Can I append a new field to an existing record?

    Can I append a new field to an existing record? Access 2003.

    I have 2 tables:-

    Table 1 contains (Name) and (Value 1)
    Table 2 contains (Name) and (Value 2)

    I want to ceate a new Table 3 which combines Table 1 and Table 2:-
    (Name) (Sum of Value 1) (Sum of Value 2) in ONE Record.

    I have managed to append Value 1 and Value 2 to Table 3 but it is in TWO Records.

    Eg.
    (Name) (Sum of Value 1)
    (Name) (Sum of Value 2)

    Does anybody know if this can be done??

    Thanks in advance.

    Paul

  2. #2
    Join Date
    Nov 2009
    Location
    Google, KS
    Posts
    47
    yes it can, you can use a make table query. An SQL query code example below. Please elaborate more if this doesn't meet your needs.

    Code:
    SELECT Table1.Name, ([table1].[value1]+[table2].[value2]) AS [Sum] INTO [Table 3]
    FROM Table1 INNER JOIN Table2 ON Table1.Name = Table2.Name;
    Last edited by Marsbars; 01-06-10 at 17:42. Reason: Grammar
    Life - sexually transmitted, always fatal.

    My beer drunken soul is sadder than all the dead christmas trees in the world.

  3. #3
    Join Date
    Nov 2009
    Posts
    223
    Marsbars,

    Thanks very much for your reply.

    I am very pleased to learn that this can be done and am now about to take my very first steps into programming with SQL and I look forward to the challenge.

    Thanks for guiding me in the right direction.

    Cheers,

    Paul

  4. #4
    Join Date
    Nov 2009
    Posts
    223
    I have tried to work on my newly acquired SQL skills but, although I have learnt some things, I am not able to get the new Table with the correct data.

    Please could I explain in a little more detail what I am trying to achieve?

    Table 1 has 2 columns. (Name) and (Amount Spent)
    Table 2 has 2 columns. (Name) and (Amount Saved)

    Eg. Table1 (Name) (Amount Spent)
    Record 1 - Name 1 - $100
    Record 2 - Name 2 - $100
    Record 3 - Name 1 - $85
    Record 4 - Name 3 - $10
    Record 5 - Name 1 - $10

    Eg. Table2 (Name) (Amount Saved)
    Record 1 - Name 1 - $15
    Record 2 - Name 1 - $10
    Record 3 - Name 3 - $20

    I now want to make a new table (Table3) with 3 columns (Name) (Amount Spent) and (Amount Saved).
    All three columns should be in ONE Record.

    The (Name) column should be the name of the selected person (eg. Name 1). This name will be chosen from a drop down menu).
    The (Amount Spent) column should add up all the amount spent by Name1.
    The (Amount Saved) column should add up all the amount saved by Name 1.

    I presume this should be done with a MakeTable Query?

    Therefore, the final Table3 should have 3 columns in ONE record which should look like this:-

    Name 1 - $195 - $25

    Any help you can give would be much appreciated.

    Thanks,

    Paul

  5. #5
    Join Date
    Jan 2010
    Posts
    12
    You don't need a make table query. The output for a Select Query will also be in the form of a table. The only difference is that one will create a table in your table listings and the Select Query will only output your results without adding another table (AND will automatically update based on new information in your two tables.)

    The solution you are looking for is actually pretty simple. As long as the names are EXACTLY the same in both tables, it will work. Create a Select Query and put both tables in the relationship area. Do not join the two tables in any way. In the field chooser, pick one of the Name fields as the first query field and then both amount fields as the next 2 query fields. Here is where you were probably getting stuck. Right click in the field area anywhere so this menu appears:

    Click image for larger version. 

Name:	Access menu.JPG 
Views:	14 
Size:	176.4 KB 
ID:	10398

    Click on the sigma ("Totals") highlighted in red. You should get another row of options for your query. Under your Name field, make sure it says "Group By". Under the two Amount fields, select "Sum". When you run the query, you should get an output that you are looking for.

  6. #6
    Join Date
    Nov 2009
    Posts
    223
    Thanks Jokerusn,

    I have tried the Select Query (without the "Sum") but the number fo Record is not correct.

    As in my example, there should only be 3 Records (in Table 1) for Name 1 and only 2 Records (in Table 2) for Name 1. But the Select Query produces 9 Records (I have shown the results of the Query below).

    85 15
    100 15
    10 15
    85 20
    100 20
    10 20
    85 10
    100 10
    10 10

    So then if the number of records are incorrect then obviously the "Sum" will be incorrect also.

    Am I allowed to attach my very small Test database to a message so you can see the results better?

  7. #7
    Join Date
    Jan 2010
    Posts
    12
    Quote Originally Posted by reddevil1 View Post
    Thanks Jokerusn,

    I have tried the Select Query (without the "Sum") but the number fo Record is not correct.

    As in my example, there should only be 3 Records (in Table 1) for Name 1 and only 2 Records (in Table 2) for Name 1. But the Select Query produces 9 Records (I have shown the results of the Query below).

    85 15
    100 15
    10 15
    85 20
    100 20
    10 20
    85 10
    100 10
    10 10

    So then if the number of records are incorrect then obviously the "Sum" will be incorrect also.

    Am I allowed to attach my very small Test database to a message so you can see the results better?
    It's not the "sum" that is the key here but rather the "group by". It worked for me when I tried it in a test db I created. If it doesn't work for you, you can send it to me. Just PM me and I'll reply with my email address.

  8. #8
    Join Date
    Nov 2009
    Posts
    223
    Hi Jokerusn,

    I have PM'd you.

    Cheers,

    Paul

  9. #9
    Join Date
    Jan 2010
    Posts
    12
    I haven't been able to look at your db that you sent me but I think I may have a solution anyway. Basically, noone is going to save anything if they haven't spent anything, correct? If so, then as long as the names are the same in both tables, you should get the correct solution if you create a select query and join the names from table 1 (x1 in my tester - amount spent) to the names from table 2 (x2 in my tester - amount saved) and make the join properties look like this:

    Click image for larger version. 

Name:	access2.jpg 
Views:	26 
Size:	20.3 KB 
ID:	10404

    Click image for larger version. 

Name:	access query.jpg 
Views:	44 
Size:	53.2 KB 
ID:	10405

    Then, in the field list at the bottom of the design view screen of the query, do what I said before with the Group By and Sum under the "Total:" property. You should come out with this result:

    Click image for larger version. 

Name:	access query result.jpg 
Views:	37 
Size:	31.6 KB 
ID:	10406

    Hopefully, that should make it work how you want it. There are also ways to do with a PivotTable but I think this was what you were looking for. If you need any more help or if that's not what you're looking for, I'll take a look at your db tonight when I get home and try to make it work there.

  10. #10
    Join Date
    Jan 2010
    Posts
    12
    Quote Originally Posted by jokerusn View Post
    I haven't been able to look at your db that you sent me but I think I may have a solution anyway. Basically, noone is going to save anything if they haven't spent anything, correct? If so, then as long as the names are the same in both tables, you should get the correct solution if you create a select query and join the names from table 1 (x1 in my tester - amount spent) to the names from table 2 (x2 in my tester - amount saved) and make the join properties look like this:

    Click image for larger version. 

Name:	access2.jpg 
Views:	26 
Size:	20.3 KB 
ID:	10404

    Click image for larger version. 

Name:	access query.jpg 
Views:	44 
Size:	53.2 KB 
ID:	10405

    Then, in the field list at the bottom of the design view screen of the query, do what I said before with the Group By and Sum under the "Total:" property. You should come out with this result:

    Click image for larger version. 

Name:	access query result.jpg 
Views:	37 
Size:	31.6 KB 
ID:	10406

    Hopefully, that should make it work how you want it. There are also ways to do with a PivotTable but I think this was what you were looking for. If you need any more help or if that's not what you're looking for, I'll take a look at your db tonight when I get home and try to make it work there.
    Ok, so that's not exactly correct also... Third time's the charm...

    I promise, this will be the right one. I was thinking something didn't add up with what I said above so I checked it out on my home computer. What I should have said was that you need to do separate queries for each table that groups and sums THEN link the two queries together like the way I said in the beginning of the above post. You WILL then get the right answer.

    Sorry for all the confusion.

  11. #11
    Join Date
    Nov 2009
    Posts
    223
    Joker,

    Hey, it isn't difficult to confuse me!!!

    I have not had to be involved in Joins or Relationships before and so I must be getting good if I have advanced that far (haha).

    Thanks to your help, I have now managed to successfully merge the three fields into one record. Yippeee! Actually, it isn't so difficult, right - it is just being pointed in the right direction.

    However, there is just one final matter to be sorted.

    If, say "Name1" has a record in Table 2 but NOT in Table 1, then that "Name" does not show in the merged Table.

    I had used an example of "Spent" and "Saved" which may not have been the best example to use?

    Is there any way to show the results of the "Name" even if it does not have a record in one of the tables??

    Cheers,

    Paul

  12. #12
    Join Date
    Nov 2009
    Posts
    223
    I have cut and pasted the current SQL code which is:-

    SELECT [Query 1].[Supplier Name], [Query 1].[SumOfOrder Value], [Query 2].SumOfPayment
    FROM [Query 1] LEFT JOIN [Query 2] ON [Query 1].[Supplier Name] = [Query 2].[Supplier Name];

  13. #13
    Join Date
    Jan 2010
    Posts
    12
    I think if you are going to have have some names in either list that aren't in the other, you'll probably have to create a separate joining table or query that just has all the unique names from both tables. I'm not as familiar with SQL and VBA coding so there might be a way to do it in both of those but I think the simplest way would be the joining table.

    If you do that, you'll have to modify the final output query to have the names table sit in between the two summation queries and act as the main point of data for the names. I believe that if you just used a 1-1 join (join option #1 in the join properties) for the joins for both queries, you'll get all the names that have either an instance in table 1 or in table 2 but not the names that are not listed in either but are in the main names table. If you want ALL the names, you'll have to make sure the join properties are arrows that go from the name table to both queries.

  14. #14
    Join Date
    Nov 2009
    Posts
    223
    Thanks again for all your help, Joker. I am a novice with relationships and SQL code and so I am definitely struggling with this problem.

    But I nearly have all the data I need. I just need to include the "Names" which have records in Table 2 (but no records in Table 1).

    My current code for the Join Query is:

    SELECT [Query 1].[Supplier Name], [Query 1].[SumOfOrder Value], [Query 2].SumOfPayment
    FROM [Query 1] LEFT JOIN [Query 2] ON [Query 1].[Supplier Name] = [Query 2].[Supplier Name];

    Is there a way to also include the "Names" which are in Table 1 OR Table 2 (but are NOT in BOTH the Tables????

    Marsbar had suggested an Inner Join but I am not familiar with that? Is that soemthing which would help?

    Thanks if you can suggest something.

    Paul

  15. #15
    Join Date
    Jan 2010
    Posts
    12
    I just looked up what "INNER JOIN" means in SQL and it is NOT what you are looking for. What you actually want is "FULL JOIN" or "OUTER JOIN" BUT they aren't recognized functions in MS Access... Check out this link:
    SQL Outer Join

    The only way to get all of what you are looking for in Access is to put the table of names (Table 3) in between the two queries. It will look something like this in SQL:

    SELECT Table3.name, Query1.SumOfsale, Query2.SumOfsaved
    FROM (Table3 LEFT JOIN Query1 ON Table3.name = Query1.name) LEFT JOIN Query2 ON Table3.name = Query2.name;
    Hope this helps.

Posting Permissions

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