Query make table - allow blanks

[dasaki]

I'm using access 2007 and I'm trying to use the make table query in order to pull data from different tables together. The tables are linked by a unique ID #. Right now the query does not return anything if even one of the tables does not have all the columns filled out. How can I allow blanks in my table? For example if there is no info about unique id#1 in tables 2 and 3 I would still like to create a table with the unique id# and blanks for those columns or null values.

This is my previous post regarding my whole structure:
http://www.mrexcel.com/forum/showthread.php?t=559843

I feel like I can put something in the criteria section under query design for each column. Any help would be appreciated

 

[Joe4]

You might have something going with the Properties of the fields, and which fields are required, and which are not.

One thing about Make Table Queries is that you do not always have complete control over the fields are going to be set-up - Access tries to "figure it out" on its own. Sometimes, it does not do the best job.

I often have better luck manually create a blank table desgin from Access, then importing the data into that table, either directly through data import, or through an Append Query (if the data already exists elsewhere in Access).

However, it should be said, many times it is NOT necessary to do this at all. If you can get what you need through a query, many times it is unnecessary to write those results back to a table (and many times it is actually unwise to do so, as it could violate the rules of data normalization and undermine the dynamic nature of the database). Queries can be used for many of the same things that tables can - they can be used as Control Sources for Forms, Reports, and can be exported.

So that is really the first question you should ask - does this information really need to be written back to a table, or will a query suffice? If not, why?

 

[dasaki]

Well I'm essentially trying to create a summary table with the specific unique #s from one sheet and compile all the information the other tables have on that specific unique # then eventually bring it back to excel. I'm not sure if that means if I need a query or not. How do I specify a field to NOT be required in access 2007? I can't seem to find that option in properties.

 

[Norie]

Try creating a separate query for each table, joining them to the summary table by the ID.

Set the join so it will return every record from the summary table and those that match from the other table.

If those queries work you can then combine them using a UNION query.

By the way, I'm assuming all the tables are basically the same.

 

[Joe4]

That is precisely the thing that you would queries for - to combine and summarize data from different tables. So it appears that there really is no need to write it back to a new table.

You would just create a query with the three tables, and create the joins between them (using the common join fields). Then just select which fields you wish to return in the query.

I would highly recommend picking up an introductory Access book and working through some of it first, especially the sections on how to do queries and joins between multiple tables. This will all make a lot more sense if you understand those concepts. Access has a steeper learning curve than Excel, and is not as intuitive, so trying to use it without having much knowledge about it (or about relational databases) can be very frustrating.

 

[dasaki]

OK. Thanks for your help I'm going to pick up a book today to learn about queries and joins for access '07

Norie: The tables are actually completely different the only common denominator is the unique ID# does that change anything?

Thanks for your quick help

 

[Norie]

If they are all different how are you trying to combine them?

Are you trying to get specific fields from each table based on the ID in the summary table?

eg field1 and field2 from table1, field3 and field5 from table2 etc

 

[Joe4]

The tables are actually completely different the only common denominator is the unique ID# does that change anything?

Only if you are trying to do UNION queries (which I don't think you are).
As long as you have a common field to join the tables on, you should be fine. Relational databases need field(s) to join on, otherwise how would you match your data between tables? You would end up with a Cartesian Product with out one (where every field in one table is matched up to every field in the other table).

 

[dasaki]

exactly. I don't have a summary table actually but I'm trying to get (based on an ID#) fields1&2 from table1 fields3&4 from table2 etc etc. but if that # has no record in table2 i want to just output null values from fields 3&4 so the final table would look like

id | field1 | field2| field3 | field4
123 blah blah null null

 

[Norie]

So do you have a table with the IDs you want?

If you do you could add it and all the other tables to a query.

Create a join between the the ID table and each of the other tables.

Set them all to return all records from the ID table and the records that match from the others.

Then just add the fields you need from each table to the query.