I need some help with a tabular form and 3 tables

[Jon123]

So I'm trying to set up a Tabular form as sort of a checklist
example Step 1, Step 2, step3, etc etc etc
I have the form setup with 4 tabs or 4 seperate forms within 1 form (works nice) now I trying to set up my query to open the form and I have 3 tables linked to the form. see bitmap
When I add the 3rd table to the query my forms opens to a blank screen no pages and no fields and I can't figure out why. Any ideas would be super helpful

 

[plog]

4 seperate forms within 1 form (works nice) now I trying to set up my query to open the form and I have 3 tables linked to the form

First, let me teach you about definite articles: (http://en.wikipedia.org/wiki/Definite_article#Definite_article). This is a lesson in general communication, not just English so even if its not your native language this applies. You mention a query, 3 tables and 5 forms. So, when you use the term 'the form' in the last part of that sentence, we have no idea which form you are referencing.

Also, this is posted in the Queries forum, so that leads me to believe your issue is with a query not a form. 3rd, you mention your form being linked to 3 tables, what does that mean to you exactly? A form can have a control source which can be a table or a query, but only one, so the 3 tables thing is throwing me to.

In conclusion. I have no idea what you need. If you are trying to get a query to show the correct data tell me about your query and leave out all the form stuff. Give me sample data from all data sources your query is based on (include field and table names) and also provide what you expect the results to be based on that sample data.

 

[Jon123]

Sorry, I guess its easy to get lost in the data and trying to explain can be rough.
I will try better.

I have 1 form (tabular) 5 pages so each page is like a seperate form. However,using the Tabular control it allows you to have 1 form with tabs / pages that show as 1 form. Very nice when you have many fields to report data for. So this 1 form has 100 fields that are divided up accross those 5 pages or tabs. They also report to 3 different tables so the data that gets stored from this 1 form goes into 3 different tables or at least this is what I'm trying to do.
So when I build the control source for the form I'm trying to use a Query that has all 3 tables and the are linked with a pk. But when my form opens is a blank screen no tab's If I remove 1 of the tabes and just have 2 tables in the query it works.
I believe my issue is with the query

Does this make anymore sense?

again sorry its not easy getting it out

 

[plog]

So let's forget talking about forms and focus on your query. Can you provide some data samples to show what is happening? Post your database with examples of it working and then not working.

 

[Jon123]

would it be possible to just send it to you? I would rather it not go out to all without alot of scrubbing? I can scrub if you perfer.

jon

 

[plog]

That didn't go a long way to explaining your issue. I see 3 tables, a query and a form. Your query seems to be working. What is your issue?

 

[plog]

I think I see your issue, well at least with the query. When you INNER JOIN (http://www.w3schools.com/sql/sql_join_inner.asp) data sources in a query, you are in effect using them as criteria: the fields they are linked on must match. If you have a record in one table but not the other, you will not get that record.

The more INNER JOINS you have, the more criteria you have. If a record is in table 1 and table 2 but not in table 3, it will not show up--because it has to be in all 3 tables. I believe that is what is happening to you when you add that 3rd data sources.

What you should do is use LEFT JOINS (http://www.w3schools.com/sql/sql_join_left.asp) which shows all the data from one side of the join and then matches from the other table if they exist.

To see what I mean, open your query in design view and left click on the line between your 2 tables and select the option that says something like 'Join Properties' and then select the option that shows all the data from one table and matches in the other. This is how you need to add that 3rd (and additional tables).

 

[Jon123]

plog - thats it I was jioning them wrong. Thank you for the help.

have a great new year

jon