Access Query HELP...

[twstech]

Hello all, I have a database with 2 tables each having a common field "actID". I am trying to select everything from table1 where the date = date+7, this is all fine, in addition I would like to select the info from table2 where actID=actID from table1 and then loop through all the data and display info from both tables in the single loop..?? I'm sure it can be done but I am at the edge of the cliff at this point without the safety net.... I have tried joins, query's and still no luck (type mis-match).. Here is one of the sql statements I have tried with no luck..

sql = "SELECT accounts.*, calendar.* FROM accounts INNER JOIN calendar ON calendar.actID = accounts.actID"

Here is another that works, but does not give all the info I need...
sql = "SELECT * FROM calendar WHERE calendarDate = #" & dtToday & "#"

Any help would be greatly appreciated, thanks...

-Roland

 

[silver]

perhaps it is a type mismatch - are the actIDs the same type in the two tables?

this should generally work (I don't know access - but in mysql it's like this)

select table1.thing1,
table1.thing2,
table1.whatever,
table2.thing1,
table2.anotherthing
from table1
left join table2 on table1.actID = table2.actID

should work,. I spect using table1.* and table2.* works fine but I tend to explictly select each field I need

Sil

 

[masterkeedu]

silver had great advice, a type mismatch would indicate a diffrence in your key formats, alternatly your modification of the date mioght be the mismatch, be sure its format is compatible with adding 7(days?)
also, try using two queries called in sequence, the first to determine those id's which are in both, and the second, feeding off the first to ddo the calculationg.