One to many relationships

[Guest]

The Biblio database consists of a table form Book Titles, and another for
Authors.
A Title can have multiple Authors...
So, how can I show all the authors for a title as a single record, using a
select query????

 

[Marshall Barton]

The Biblio database consists of a table form Book Titles, and another for
Authors.
A Title can have multiple Authors...
So, how can I show all the authors for a title as a single record, using a
select query????

You need to use a function to combine the many side data
into a single value. There's a good one at:
http://www.rogersaccesslibrary.com/...Generic Function To Concatenate Child Records'

 

[Guest]

The link says:

ADODB.Recordset error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in
conflict with one another.

/Otherdownload.asp, line 32


Can you please post the correct link???

 

[Marshall Barton]

The link says:

ADODB.Recordset error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in
conflict with one another.

/Otherdownload.asp, line 32


Can you please post the correct link???

That link works fine for me. Did you news reader program
truncate it? Or maybe it was wrapped to more than one line
and you forgot to paste it back together in IE's Address
box?

 

[Guest]

Anyways, Duane himself posted this link above yesterday...

His idea is toooo good, & guess what, I already have the idea working for me
perfectly...
Thanx for the reply...

I have a new problem now... I used Duane's idea to create a select query(a
view), named TitleAuthors, listing the authors of all titles in a semi-colon
separated format.

That worked just fine in Access, but when I tried to retrieve data from that
query using VB.NET 2005, it generated an exception, 'Could not find the
function Concatenate'.
What is the reason for that exception, & what's the solution?????

 

[Marshall Barton]

I have a new problem now... I used Duane's idea to create a select query(a
view), named TitleAuthors, listing the authors of all titles in a semi-colon
separated format.

That worked just fine in Access, but when I tried to retrieve data from that
query using VB.NET 2005, it generated an exception, 'Could not find the
function Concatenate'.
What is the reason for that exception, & what's the solution?????

The reason is because a function in Access is not known in
other environments. When you run a query from any place
other than Access, you are using a data access library that
interfaces with the database engine, not with Access. A lot
of people think Access is the database engine when in
reality, it is a development tool for creating a data
centric application. The default database engine that comes
in the Access box is Jet (using either the DAO or ADO
library), but many people use other db engines such as MSDE,
SQL Server, Oracle. etc.

AFAIK, the only way Jet can deal with user defined functions
is when it it used from the Access environment. When you
come from another environment such as .NET, Jet has no way
to invoke UDFs.

The solution ro your problem is to create an equivalent
function that will work with your db engine and a compatible
data access library. I can't help you with that beyond
suggesting that you find a forum specific to your
development enviornment and ask your question there.

 

[Guest]

Thanx very much for that reply Duane...
It will take me a couple of days to see whether that code fitted my problem
domain or not... (I am sure, it will)...
I will surely report back to you the amount of insight that code provided to
me ;-)