Cannot Read Multiple Records

Report · Feb 25, 2009

Sorry is this has been covered before.

I have a form which inserts value(s) into a table using the primary key. This is linked to another table so that when i use a query, it pulls the information and displays the name according to the primary key. This works fine when only one record is in the field, however, it has a problem recognising this when there is more than 1 entry to the field.

Could this be because a comma is used as a default seperator by Access if there is more than 1 entry (eg displays it as "1, 2, ...")

To infinity and beyond!

Report · Feb 25, 2009

More than one entry to the field? If that's in a single record, your problem is a bad database design. I've heard good things about the book, Database Design for Mere Mortals.

Report · Feb 25, 2009

It may be bad design - I have set up a form in which someone can deliver a talk to many people, this isnt a problem, the problem arises when I try to perform a query to read out the people who have had a talk.
I think the problem arises as Access cannot differentiate between each number.
Can you think of another way to do this?

Report · Feb 25, 2009

BuzzLightyear2 wrote:

> I think the problem arises as Access cannot differentiate between each number.
> Can you think of another way to do this?
>

Access can, but can you properly tell it to do so. I am unclear if your
problem is with an badly designed, unnormalized database scheme or an
unclear understanding of joins and or the "IN" clause.

A bit more description of what is going on would help. What are users
entering with examples. What are you doing with this input, with code.

Report · Feb 26, 2009

Sorry for the confusion.
I have created a form in coldfusion which a user can submit when they have delivered a talk to as many as 25 people, this is then stored in access.
There is also the option to view each talk and who it has been delivered to (for example Joe Bloggs delivered the talk to Batman, Robin, Superman, ...).
This is fine when only 1 person is delivered the talk. When it is delivered to 2 or more people, coldfusion or access fails to display the names.

Thanks for your help, hope this clears things up a little bit...

Report · Feb 26, 2009

BuzzLightyear2 wrote:
>
> Thanks for your help, hope this clears things up a little bit...
>

Not much, because I still don't know if you are having trouble, inputing
the data, outputting the data or storing the data.

You are describing a fundamental concept of relational data. So what
you want to do is very possible. Many of of us on this list do this
kind of stuff day in and day out. But as most of us have day jobs, it
is unlikely that anybody is going to have time or the desire to develop
this from scratch from such a sketchy description. At least not without
a paying contract. Unless you just happen to be lucky enough to catch
one of us on a day we happen to be very bored and have a lot of time on
our hands. (Unfortunately, that is not me today.)

So what exactly is your problem, and how have you tried to solve it?
Show some examples of input, output and|or code so those of us who have
a few moments to provide some guidance have someplace to start.

Report · Mar 03, 2009

In the coldfusion form, a user can add a talk to multiple users, this is done by inserting a number to a table, which is the foreign key to another table with a corresponding name, up to 25 entries can be added to this record.

The problem is that when i try to view who has been delivered talks, the page displays the foreign key fine (eg, 1, 2, 3, etc), but i cannot figure out a way to display names (eg, Joe Bloggs, etc). i thought it was only a matter of a condition in the select statement but this does not display anything if the record has more than one entry.

sorry for being vague, my descriptive skills are not the best!!!

Report · Mar 03, 2009

quote:

Originally posted by: BuzzLightyear2
In the coldfusion form, a user can add a talk to multiple users, this is done by inserting a number to a table, which is the foreign key to another table with a corresponding name, up to 25 entries can be added to this record.

The problem is that when i try to view who has been delivered talks, the page displays the foreign key fine (eg, 1, 2, 3, etc), but i cannot figure out a way to display names (eg, Joe Bloggs, etc). i thought it was only a matter of a condition in the select statement but this does not display anything if the record has more than one entry.

sorry for being vague, my descriptive skills are not the best!!!

Mentioning the foreign key suggests that your database design is not the problem. The 1,2,3 refers to 3 records, not one record storing a list.

Assuming that's correct, your problem is that a single value can't equal a list, but it can be in a list. So something like this,
and somefield = '1,2,3'
should be
and somefield in (1,2,3)

However, the quotes around url.person
AND person = '#URL.person#'
suggests some other problems may exist.

Report · Mar 03, 2009

BuzzLightyear2 wrote:
>
> sorry for being vague, my descriptive skills are not the best!!!
>

The code helps a bit.

> <cfselect name="per_no" height="20" width="50" multiple query="qreceived"
> value="per_no" display="name" queryPosition="below" >
>

This code suggest that you have a HTML select control where a user can
select one or more items from the 'qreceived' query that populated the
control. This is all good and a common way to present this type of
relationship.

When this form is submitted the selected values of this control will be
in a list. As Dan indicates one would often use a list like this in a
database query with an 'IN' clause. Depending on the data types of the
fields in question, you may also need to use the listQualify() function
to properly format the list for the 'IN' clause.

> <cfquery name="qAll" datasource="datasource">
> SELECT test.TBT, test.person, test.date_h, test.received
> FROM test, personnel_create
> where test.person = personnel_create.name
> <cfif IsDefined ("URL.person")>
> AND person = '#URL.person#'
> </cfif>
> order by date_h
> </cfquery>
>

The trouble here is I see no relation in this query to the above select
statement. I would expect to see something referring to 'URL.per_no' or
'FORM.per_no' depending on how the form is submitted, i.e. whether you
used the get or post method in your form. I do not know what you are
trying to do with this query.

Report · Mar 03, 2009

This may not not be possible, but could you use cfloop to loop through the field/list, and select any values where the foreign key and primary key match?