Trouble with concatenate code from Duane Hookum

  • Thread starter Thread starter ct4accessHelp
  • Start date Start date
C

ct4accessHelp

I have two tables: "Courses" and "Prerequisites" and a third query
"qryCourses" that bridges the two so I know which courses have prereqs and
what they are. One course may have multiple prerequs and could look like this:
Course ID PrereqsID
3200000012 320000001
3200000012 320000002
3200000013 320000012
3200000014
3200000015 320000001
3200000015 320000002
3200000015 320000003

I attempted to borrow the code from Duanes "Concatenate Demo" database but I
continue to get a run-time error '-2147217904 (80040e10)': No value give for
one or more required parameters.

Please pleease pleeease help...
 
ct4accessHelp said:
I have two tables: "Courses" and "Prerequisites" and a third query
"qryCourses" that bridges the two so I know which courses have prereqs and
what they are. One course may have multiple prerequs and could look like this:
Course ID PrereqsID
3200000012 320000001
3200000012 320000002
3200000013 320000012
3200000014
3200000015 320000001
3200000015 320000002
3200000015 320000003
Click to expand...
These reads:

One course can have zero, one more prerequisites and one prerequisite is
assigned to zero, one or more courses.

So you need three tables, not only two. At least something like this:

Course:
ID, CourseName, ...
primary key: ID

Prerequisite:
ID, PrerequisiteName, ...
primary key: ID

Course_Prerequisite:
CourseID, PrerequisiteID
primary key: CourseID, PrerequisiteID
foreign key: CourseID to Course.ID and
PrerequisiteID to Prerequisite.ID

The foreign key is set in the relationship window.

Now you can simply create a query by adding the table Course and
Course_Prerequisite. Change the ^join property to 'select all from
Course and only matching from Course_Prerequisite.
This should return your desired result.
Please pleease pleeease help...
Click to expand...
The error number often indicates a type in a field name in a query. I
would carfully check the query qryCourses for that.


mfG
--> stefan <--
 
You're right Stefan and I do have three objects (two tables and one query
matching the courseID with the prerequID) and the fields are exactly as you
describe; however, I need the data in a string and the query is not returning
the desired result. I attempted the VB function (as previously posted) and
still get the error). Do you have any further suggestions or perhaps would
you provide me with the exact SQL statement as I am a novice Access user?
 
Please provide your field data types and the expression you are using. I
expect this may be an issue where you have a text field and are treating it
as if it was numeric.
 
Back
Top