data missing in crosstab query

Question

hi all,

i am pulling information from a query into a crosstab query - i have been able to use many crosstab queries without any problems but this one is big.  when i pull it over some data will not populate in some fields for a particular ID.  the data that doesnt populate sits in 1 table but it pulls properly into the query that i am basing my crosstab query off of.  other oddities - the data in the field may not show today, but it may have shown yesterday. - lastly, it seems to go by ID - some ID's have all the fields pull over properly, some IDs will have some and some IDs wont have any - but the craziest part of all is that yesterday ID 1 may have had all the data populate in all the fields but today only some and tomorrow none, whereas ID 2 may have all data today, tomorrow and into the future. any help is greatly appreciated.

Answer 1

Post the SQL of both queries.

Answer 2

I'm GUESSING - and it's only a guess, because we cannot see the SQL of your query nor anything about your table structure - that you have an incorrect JOIN in your query. If it's a multitable query, and you're using the default INNER JOIN, then you will only see records where data exists in all of the tables in the query for that ID. You may need to change the Join type in query design to one of the options "Show all records in <this table> and matching records in <that table>", or (equivalently) change the JOIN expression in the SQL view from INNER JOIN to LEFT JOIN or RIGHT JOIN as appropriate.