List related table data into a field

[taylorantone]

On an ASP page, I have DVD's listed and I can't get it to properly display all of the genres without them being in a field.

Here's how the tables are set up (essentially, it's a many-to-many relationship):
tblDVD--
dvdID (autonumber)
other data

tblDVDGenre--
dvdID
genreID

tblGenres--
genreID
genre

I have an ASP page I made with FP2002 that uses a database-populated drop-down box to allow the user to choose a genre and then it displays all of the DVD's that belong to that genre. Since almost every movie out there belongs to more than one genre however, I wanted a way to list all of those genres in the results.

Since I'm such a n00b at this stuff, I just added a field to the tblDVD called genre where I manually entered the genres the movie belongs to, separated by commas. This is totally separate from tblGenres or the linking table.

Any suggestions on how I can do this?

 

[Zion7]

Either add the necessary fields required to the DVD table, to accomodate the maximum number of genres a movie can have, or from the genre table, put a subform on the DVD form( if you have a DVD form)? One way or another, the DVD's have to be defined(by genre), prior to being able to manipulate the table by genre.
Creating an unbound form, put a dropdown box as, "value list", include the options horror;drama;comedy;thriller etc.
You can use a list box or a subform to display the "recordset". I like subforms, they're more versatile.
Create a SQL statement with the criteria being dictated by the choice in the dropdown box.
Once the SQL is complete, it can be appended to the recordsource of the subform or listbox(whichever you chose).
..or a modified SQL can be used as a filter for the form.

taylorantone, I've made a lot of assumptions here, 1st, my understanding of your objective & secondly, your familiarity with the techniques I just suggested.

for the recordsource property, you need a fully qualified SQL eg;
"SELECT tblDVD.* " & _
"FROM tblDVD " & _
"WHERE tblDVD.txtGenre1 = '" & cboGenre & " OR " & _
"tblDVD.txtGenre2 = '" & cboGenre & " OR " & _
"tblDVD.txtGenre3 = '" & cboGenre & ";"

for the filter property it would be like..
Me.Filter = "txtGenre1 = '" & cboGenre & " OR " & _
"txtGenre2 = '" & cboGenre & " OR " & _
"txtGenre3 = '" & cboGenre
Me.Filter.On = True

again, a lot of assumptions.
Hope this offers some direction.
Good Luck!

 

[Zion7]

Geez taylorantone, speaking of n00b, i don't know what an ASP page is, or FP2002.
...did I say, "a lot of assumptions"? LOL

sorry, if I wasted your time, got carred away with the desire to offer some "expertise", & lost sight of the "big picture".

...then again, considering I don't know what those 2 things are, maybe it still applies?LOL

sorry all the same, good luck either way!

 

[taylorantone]

No problem, and thanks for the effort! The tables work great, and I don't use any forms in my database becuase the data isn't manipulated through Access other than by me.

The Genres table and the tblDVD are related through that linking table, so movies can be--and are--replated to more than one genre and vice-vesa.

I just need a way to display this on the a Front Page 2002 ASP page.

Right now, that page has a drop-down box that is populated from the database tblGenres. Someone chooses a genre and is bring up only the movies that have that genre.

I want a way to be able to display the REST of the genres a ltered movie belongs to when the search is done. Seeing all of the genres is much more informative.

 

[Zion7]

Your questions seems very straight forward, if it is an Access & VBA issue? but I don't want to waste your time if not....


The significance of tblgenre is only for the dropdown box, no?
What denotes the genre on tblDVD, a single field, a couple, or a few fields. Any fields at all? Obviously something does, since it "recognizes" the call from the dropdown box?
Is it just a matter of showing more fields, on the ASP page?
(I imagine that's too obvious).
Could i see the code for the dropdown box?

Sorry again, if I'm barking up the wrong tree. But it seems very solvable as far as VBA or Access is concerned?

Disregard taylorantone, if I'm way off.
good luck again, either way!

 

[taylorantone]

There is a set list of genres, and a set list of DVD's. I needed a way to assign a DVD to multiple genres, while also allowing a genre to be assign to multiplt DVD: a many-to-many relationship. To do this, I had to have the tblDVD and the tblGenres, each having unique records, then a table that relates the two called tblDVDGenre(a one-to-many from tblDVD to tblDVDGenre, and then a one to many from tblGenres to tblDVDGenre.

So, it just turned out nicely that tblGenres was a unique set that I could use to populate a drop-down box without needing any special coding.

 

[Zion7]

Why do you not have the option of just displaying your tblDVD.genre field on the ASP page, does the ASP page just show the title of the movie only?
Your looking for something more automated...
If you display tblgenre on the form also, & have the tblDVDGenre be the source behind the dropdown box, will both tblGenre & tblDVDs be filtered accordingly?

 

[taylorantone]

Because tblDVD.GenreID will display a number, and multiple numbers at that.

I'm looking for a way to pull the multiple genres that a DVD is assigned to into a single field for display when I view the movies as a list.

 

[Zion7]

I just added a field to the tblDVD called genre where I manually entered the genres the movie belongs to, separated by commas"
Isn't that the field you're looking for?

Could you open a recordset, or use DLookUp?

very sorry taylorantone, I'm at a bit of a loss. I'm not familiar with the dynamics of a many to many relationship, outside of speculation.
I'm going to sit back & see if other threads come in for this, before I inundate you with too many questions.

Sorry if I jumped in too hastily.

Good Luck!