Make Table Query

  • Thread starter Thread starter AllenKarns
  • Start date Start date
A

AllenKarns

Hello, I just have a couple of questions on creating a new table from an
existing table. Right now I have a table that was created from a former Sales
Database that the prior sales team used. It was exported from an internet
based sales system before they left. I then imported that data into access.
Presently there are multiple entries for the same account. Everytime there
was a comment left on that account it created a new entry with duplicate
information in every other field. There are also multiple contacts at each
account. What I'd like to do initially is take the last updated record for
each account and import that into a new table. Secondly and not essential is,
I'd like to somehow see if I can I append the new recordset with the 2nd
contact name at the location if there is one. My table name is tblsalesinfo
and I'd like use the field Mailing Address as the unique identifier. Any help
would be greatly appreciated, I'm awfully new to this .


Thanks,
Allen
 
Also, I forgot to mention that I am using the field Last Modified Date to
grab the latest updated field.

Thanks again for any help.

Allen
 
Hello, I just have a couple of questions on creating a new table from an
existing table. Right now I have a table that was created from a former Sales
Database that the prior sales team used. It was exported from an internet
based sales system before they left. I then imported that data into access.
Presently there are multiple entries for the same account. Everytime there
was a comment left on that account it created a new entry with duplicate
information in every other field. There are also multiple contacts at each
account. What I'd like to do initially is take the last updated record for
each account and import that into a new table. Secondly and not essential is,
I'd like to somehow see if I can I append the new recordset with the 2nd
contact name at the location if there is one. My table name is tblsalesinfo
and I'd like use the field Mailing Address as the unique identifier. Any help
would be greatly appreciated, I'm awfully new to this .


Thanks,
Allen
Click to expand...

I'm pretty sure you need to create some new, normalized tables. You
have one (accoutn) to many (comments) relationship - you should have a
table for Accounts and a separate related table for Comments.
Similarly, you probably have a one to many (maybe a many to many)
relationship between Accounts and Contacts.

I'd VERY strongly recommend that you *not* use mailing address as a
unique identifier. A Primary Key should ideally be unique, stable, and
short. An address isn't even necessarily unique (you could have two
businesses sharing one address), and it's certainly not stable or
short.

Consider the possible structure:

Accounts
AccountID Autonumber <Primary Key>
<account specific fields>

Comments
CommentID Autonumber <Primary Key>
AccountID Long Integer <foreign key to Accounts>
CommentDate Date/Time <default Now()>
Comment Memo

Contacts
ContactID Autonumber <Primary Key>
LastName
FirstName
MiddleName
Suffix


It will depend on your business model whether the address fields
belong in the Accounts table or the Contacts table - you might have an
Account with a business which has multiple offices, with each contact
at a different office. I'd certainly recommend at least putting Phone
and Email fields in the Contacts table, not the Accounts table.

John W. Vinson[MVP]
 
Back
Top