gathering data from multiple database to achieve collection information

[princess21]

Wonder if anyone could help. I am using an internet based system which has access data tables behind it storing information. My collegue and I are not very IT so sorry if this does not make sense.

We have a data table with a list of routes which are linked to weekdays, we then have a seperate data table which is a list of 'Unique Street Reference Numbers' and 'Unique Property Reference Numbers' we currently have the below sql query to bring all this information togther and to select the correct day of collection, however this is not bringing back any results.

select ct.collection_type, DATENAME(weekday,cd.date) as weekday, convert(varchar(11),cd.date,113) as date, case when c.active=0 then 'SKIPPED' else '' end as msg from collection_date cd inner join collection c on cd.collection_id=c.id and cd.active=1 inner join route_property rp on c.route_id=rp.route_id and ((rp.USRN={usrn}) or (rp.UPRN={Customer_Details/uprn})) inner join collection_type ct on c.collection_type_id=ct.id where cd.date>=GETDATE() and {collection_types}+',' like '%'+ct.collection_type+',%' group by c.id, cd.date, ct.collection_type, c.active order by cd.date

Does anyone have any suggestions.

Hugs

Princess

 

[revans]

This doesn't look like Access SQL. Access doesn't support Case statements in SQL and curly braces "{" look like they should be square brackets "[". I'd first try rewriting your SQL to return anything from these tables to prove it can be done. Something simple like SELECT * FROM [collection]. Then you can add joins and fancy stuff from there.

hth,

Rich

 

[princess21]

I am using Java Script but with Access type tables behind hence the { brackets. Thank you for your suggestion.

 

[princess21]

Again thank you we have follow your advice with a few changes and this works perfectly! Thanks again