Multiple table select query 1

[cr84net2]

Hello, I have five access tables each with the same fields named misc1, misc2, misc3, misc4, misc5. There is a number field in each table named display. If there is a 1 in the display field, I need to display a linked image to a page where the content from the table can be shown. I have done this sort of thing several times but never with 5 tables. Here are the fields in each table.

ID-imglink-title-content-display-showuntil-lastupdat

If a tables display field contains a 1, I need the ID and imglink fields to create the linked image. The showuntil field can be compared after the rs is created to determine if the record should still be shown.

Is there a Join i can use to reduce overhead and still check all 5 tables? I have never joined this many tables before.

I dont know if this helps but I have aliased the important fields here:

CSql="Select misc1.ID AS ID1, misc2.ID AS ID2, misc3.ID AS ID3, misc4.ID AS ID4, misc5.ID AS ID5,"_
    & " misc1.imglink AS Ilink1, misc2.imglink AS Ilink2, misc3.imglink AS Ilink3, misc4.imglink AS Ilink4, misc5.imglink AS Ilink5,"_
    & " misc1.showuntil AS SU1, misc2.showuntil AS SU2, misc3.showuntil AS SU3, misc4.showuntil AS SU4, misc5.showuntil AS SU5,"_
    & " misc1.display AS Dis1, misc2.display AS Dis2, misc3.display AS Dis3, misc4.display AS Dis4, misc5.display AS Dis5"_
    & " From misc1, misc2, misc3, misc4, misc5"

Any help would be appreciated.

 

[BigRed1212]

Perhaps I'm missing something?

You have five tables of image information with identical structures and no relationship between the tables.

select *
from misc1
where display=1
union
select *
from misc2
where display=1
select *
from misc3
where display=1
union
select *
from misc4
where display=1
union
select *
from misc5
where display=1

But the bigger question is, if the above is correct why aren't the 5 tables just one table?

 

[cr84net2]

You know, I am wondering the same thing now. This is one of those things that just got out of hand. First the client wanted one misc page to use as they wished, whenever they needed it. Later, after I was done with the bulk of the site, they said let's make it two...still later , how about 5. One table would handle this nicely and I can loop through the records to create the image links to the page and use the ID parameters to retrieve the content.

Thanks for clearing my head. Obviously I needed it. I can just restrict the number of records in my admin code to 5 misc pages. That way when they want 8 pages (you know they will) all I will have to change is the administrative restriction on the table.

Gee, this feels good, my brain is working again. Thanks, I really appreciate the help.