I have an issue that seems like it could be resolved by sql code or query. I have a simple, one table access database (contactfirstname, contactlastname, address, city, state, zip). I have many situations where there is a contact at address x in one record and then I have another contact that is also at address x (spouse) in a different record. I want to merge the two contacts and create one new record that has contact1firstname, contact1lastname, contact2firstname, contact2lastname, address1, city, state, zip. This would cut my database almost in half. Can anyone help shed some light on this? I am intermediate with access but can't figure it out. Thanks in advance for any assitance.

First I would say this is something you should not do. What you have is a one to many relationship. One address with many contacts. If you have 2 tables, you could create a form that cycles through each address and a subform that displays each contact at one address. You would relate the records by having a foreign key in the Contacts table. Having said that, if you still want to do what you asked, you would need to add the additional columns to your table, run an Update query, then a delete query.

interestingly enough, I have a same problem, I have a table in Access. which looks like following, D | X ---------- A b A c A d Ideally, I would like to end up with a table like following, D | X | X_1 | X_2 ----------------- A b c d The post above says i would end up with more coloumns but i would appreciate if someone could reply in more detail Thanks, Scops

You start with a table named myTable such as: D | X ---------- A b A c A d Modify table to: D | X | X1 | X2 ----------------- A b A c A d Write a query to index all values of column D. SELECT (SELECT COUNT(*) FROM myTable WHERE myTable.X < mt2.X)+1 AS INDX, myTable.D, mt2.X FROM myTable INNER JOIN myTable AS mt2 ON myTable.X = mt2.X; End up with this: INDX D X 1 A b 2 A c 3 A d Write an update query to populate X into X1 where index = 2 UPDATE myTable INNER JOIN Query1 ON myTable.D=Query1.D SET myTable.X1 = Query1.X WHERE (((Query1.INDX)=2)); End up with this: D X X1 X2 A b c A c c A d c Write an update query to populate X into X2 where index = 3 UPDATE myTable INNER JOIN Query1 ON myTable.D=Query1.D SET myTable.X2 = Query1.X WHERE (((Query1.INDX)=3)); End up with this: D X X1 X2 A b c d A c c d A d c d Now write a delete query to remove duplicate records. DELETE myTable.*, myTable.X1, myTable.X2 FROM myTable WHERE (((myTable.X1)=[X])) OR (((myTable.X2)=[X])); End up with this: D X X1 X2 A b c d