Will be grateful for any help

I want to show a 3 column table of Manufacturers -
ID Manufacturer ParentName
When the Manufacturer has a Parent/Holding company (which will already
in the table) I need to show that 'ParentName' that in a 3rd column.
Note I was just trying to avoid having a separate table for 'Parent
companies'

My table is called tblMakers with fields ID (auto), parentID
(long), and Maker (text).

If the Manufacturer does not have a Parent then parentID can be either
0 or the same as ID.

If intended to to display the 'Parent name' in 3rd column by detecting
if parentID>0 AND parentID<>ID

eg,

ID Name Parent (parentID)
---------------------------
1 BP (0 or 1)
1 Compaq HP ( is 3)
2 Delta (0 or 2)
3 HP (0 or 3)
4 Smiths (0 or 4)

Any assistance will be really appreciated.

Kenny


--- news://freenews.netfront.net/ - complaints: news (AT) netfront (DOT) net ---

Ken Ashton <kashton (AT) terra (DOT) es> wrote in
news:61gqc6l47hk655640a54kevhj72enlt1f8 (AT) 4ax (DOT) com:

You will need to fix the bad data where parentID = ID. They must to
be set to 0.

Once that is done, open the query design tool, add the table to the
query twice.
Make sure the properties box is open. Click in the right table's name
bar. Change the Alias to Parent.

Next drag and drop the left table's parentID onto the right table's
ID field. Double-click on the line that appeared and choose the
second option.

Now just double click on the fields you want in the query, save it
ant test it.



--
Bob Q.
PA is y I've altered my address.

On Sun, 31 Oct 2010 10:56:37 GMT, Bob Quintal <rquintal (AT) sPAmpatico (DOT) ca>
wrote:

Hi Bob

That was awesome, I'm working on an ASP page and never thought to use
Access console to generate the Sql (it always confused me anyway).

Thanks for the hand hold, followed your instructions, and copied the
sql statement over to my page, works 'perfectly', no dupes or errors.

Many thanks for your time and sharing your knowledge.

Ken


--- news://freenews.netfront.net/ - complaints: news (AT) netfront (DOT) net ---