Create Table from truncated info from other table

jamied66

New member
Local time
Today, 18:58
Joined
Oct 20, 2006
Messages
6
I'm a relative newbie to the more advanced features available to Access.

If someone could help me or point me in the right direction, I'd really appreciate it.

I have one table that contains companies and associated contact info. Each company can have more than one unique number ID (3 digit alphanumeric).

I want to take this table and run a query to create a new table that will only have one instance of each company name, along with the other contact information. The unique ID's aren't needed in this table (but it would be great if I could somehow toss them in, too).

This new table would then be used to create a form with a combo box containing all of the names to quickly jump to their contact info.

Any and all help would be appreciated. If a query isn't the best avenue for this, then please point me in that direction, too.

Thanks!
 
I don't think you don't really need the second table. The source for your combo could be:

SELECT DISTINCT CompanyName
FROM TableName

which will only show one instance of each name.
 
There is a Wizard that starts when you add a combo box which allows you to select an option to have the result look up a record on a form. Use this. You can then change the recordsource to only return DISTINCT results, so you wont get the duplicates.


PBaldy, you beat me to it. :D
 
pbaldy said:
I don't think you don't really need the second table. The source for your combo could be:

SELECT DISTINCT CompanyName
FROM TableName

which will only show one instance of each name.

That seems like such an easy solution! Thanks!

But when I tried it it doesn't seem to work. Steps as follows:

Using Access 2003
Built sample table, approx. 5 names, 10 entries, 10 distinct numbers.
Built a form, added a single combo box. pulled "name" records from "table1".
Went into the Properties dialog, selected Row Source, went into SQL View, edited the statement to say this:

SELECT DISTINCT Table1.ID, Table1.name
FROM Table1;


Went out, saved form, opened it up, and the combo box stilled showed all instances of each name.

What am I doing wrong, or is Access jus stupid?

Thanks again for your help!
 
Access isn't stupid; it does exactly what it's asked to do. DISTINCT considers all the fields in the SELECT clause. Since there are 10 distinct ID's, you get 10 records. If you want only the distinct names. only include name in the SELECT clause. If you want one of the ID's associated with each name, there's a different way to do that.
 
pbaldy said:
Access isn't stupid; it does exactly what it's asked to do. DISTINCT considers all the fields in the SELECT clause. Since there are 10 distinct ID's, you get 10 records. If you want only the distinct names. only include name in the SELECT clause. If you want one of the ID's associated with each name, there's a different way to do that.

I figured out where I was going wrong. By not selecting an automatic ID key to be made, it allowed me to have the outome I wanted.

And since that isn't a requirement for what I'm using it for, I'm a very happy guy.

Thanks for the help!
 

Users who are viewing this thread

Back
Top Bottom