new table with field of one-many relation text string

[Michael Slade]

I have problem figuring out how to create a new table (using a query) from
two tables with the following fields

tableA: name ID
TableB ID prod

Items in TableA are unique and linked to TableB by ID.

There may be zero or more items in TableB with the same ID but different
prod.

I want tableC to be ID name field3 where field3 is prod(ID1) & prod(ID2)...

That is, all prod(sameID), if any, to become a text string to be put into
TableC field3.

Is there a simple way of doing a query and maketable to generate this?

If not, what script would be needed?

thanks
Michael Slade

 

[Dirk Goldgar]

I have problem figuring out how to create a new table (using a query)
from two tables with the following fields

tableA: name ID
TableB ID prod

Items in TableA are unique and linked to TableB by ID.

There may be zero or more items in TableB with the same ID but
different prod.

I want tableC to be ID name field3 where field3 is prod(ID1) &
prod(ID2)...

That is, all prod(sameID), if any, to become a text string to be put
into TableC field3.

Is there a simple way of doing a query and maketable to generate this?

If not, what script would be needed?

To you really need to make a denormalized table like that? It seems
like a bad idea. You can create a query to return the data like your
proposed table C, using Dev Ashish's fConcatChild() function posted at

http://www.mvps.org/access/modules/mdl0004.htm

Having such a query, you can turn it into a make-table query if you
really want to, but I don't see why you would want to.

 

[Michael Slade]

This looks like it should do want I want -thanks! But...

The field that I want to concatenate is a text field so I think I need to
specify "String' (the example uses "long").


However, whenever I try and run the query, I get a 'compile error ' and the
'Dim db as Database' line is highlighted.

I can't seem to set a breakpoint before this to see what is going on and
eventually have to crtl-alt-del to kill the Access program.

Any suggestions?

thanks
Michael Slade

 

[Dirk Goldgar]

This looks like it should do want I want -thanks! But...

The field that I want to concatenate is a text field so I think I
need to specify "String' (the example uses "long").


However, whenever I try and run the query, I get a 'compile error '
and the 'Dim db as Database' line is highlighted.

I can't seem to set a breakpoint before this to see what is going on
and eventually have to crtl-alt-del to kill the Access program.

Any suggestions?

thanks
Michael Slade

You need to set a reference to DAO. While in the VB Editor, click
Tools -> References..., locate the list entry for

Microsoft DAO 3.6 Object Library

and put a check mark in the box next to it. Then close the dialog.

Also, where you see the declarations in the code:

Dim db As Database
Dim rs As Recordset

change them to

Dim db As DAO.Database
Dim rs As DAO.Recordset

That will take care of any confusion between DAO objects and ADO objects
of the same name.

 

[Victor Delgadillo]

You are probably missing references to DLL (Dynamic Link Libraries). Check
while in the Visual Basic edit screen, tools / references and see if you are
missing one.

--
Victor Delgadillo MS-MVP Access
Miami, Florida

Mensajes a los grupos de noticia, asi todos nos beneficiamos!

 

[Michael Slade]

(I'm running win98 and Access 2000)
http://www.mvps.org/access/modules/mdl0004.htm


still looks like what I want to use and I've done -

"You need to set a reference to DAO. While in the VB Editor, click
Tools -> References..., locate the list entry for

Microsoft DAO 3.6 Object Library

and put a check mark in the box next to it. Then close the dialog.

Also, where you see the declarations in the code:

Dim db As Database
Dim rs As Recordset

change them to

Dim db As DAO.Database
Dim rs As DAO.Recordset"

but things still don't work - no data is being picked up.

Attempting to debug shows that the line -

Set db = CurrentDb

does not get set. Is there something else that needs to be set first so that
CurrentDb will work?

thanks again!
Michael Slade

 

[Dirk Goldgar]

(I'm running win98 and Access 2000)
http://www.mvps.org/access/modules/mdl0004.htm


still looks like what I want to use and I've done -

"You need to set a reference to DAO. While in the VB Editor, click
Tools -> References..., locate the list entry for

Microsoft DAO 3.6 Object Library

and put a check mark in the box next to it. Then close the dialog.

Also, where you see the declarations in the code:

Dim db As Database
Dim rs As Recordset

change them to

Dim db As DAO.Database
Dim rs As DAO.Recordset"

but things still don't work - no data is being picked up.

Attempting to debug shows that the line -

Set db = CurrentDb

does not get set. Is there something else that needs to be set first
so that CurrentDb will work?

Are you working in an ADP, not an MDB, by any chance? I didn't consider
that possibility, but if so that code will have to be substantially
rewritten.

 

[Michael Slade]

No!

It is an .mdb database.

What should the variable set db = CurrentDb become set to?

A string of the file (database) name?

thanks!

Michael Slade

 

[Dirk Goldgar]

No!

It is an .mdb database.

What should the variable set db = CurrentDb become set to?

A string of the file (database) name?

No, it becomes a reference to a copy of the DAO Database object that
Access currently has open -- that is, the current database. After
you've executed a statement such as "Set db = CurrentDb", the various
methods and properties of the database become available via the "db"
object variable. For example,

Debug.Print db.Name

will print the complete path and filename of the database. But there's
a lot more than that to a DAO.Database object.