Query not returning blank fields

[Jossy]

Hi there,

Very new to Access and feeling my way around it. I've successfully created one query off a table that manipulates some of the data (creates a new column from a portion of a string in a field) and then another query that uses data from the table and the other query. However, I'm finding that when the 'manipulating query' returns a blank result (i.e. there's no string to cut some text from) I don't seem to be able to return the field in the second query. Any ideas or have I just done an incredibly bad job of explaining myself??

Thanks in advance,

Jossy

 

[isladogs]

If table & query1 have an inner join in the second query there will be no record.
Change to use an outer join taking all records from the table and only those query1 records that exist
That should fix your problem ...assuming what I've said makes sense to you !

 

[The_Doc_Man]

Ridders points out that if you used a JOIN query as part of the solution, you have to use OUTER JOINs. But he didn't tell you why.

A JOIN query attempts to align two tables based on matching data they have in common. In the case of a JOIN trying to match corresponding records, if there IS no match and you use an INNER JOIN, the mismatched records never appear in the result because you can't align with a record that isn't there. If you use an OUTER JOIN (which comes in LEFT and RIGHT flavors to define which is the main table and which is the subordinate), then you get nulls for the partial contributions from the records that aren't there. Nulls are hard for beginners to manage until you know the trick.

It might be productive to show us your queries.

 

[Jossy]

Hi there,

Thanks very much for responding on this on this. I'm not sure how to create a JOIN query? I've joined the table and the first query as in the attached file...

Jossy

 

[arnelgp]

Firstly your relation is not good. You should add another numeric column to the right table. The field shoul contain the id of the first. Therefore you will link only on the two fields.

 

[The_Doc_Man]

This diagram you showed us suggests strongly that your dataset is not normalized, and that you designed it from an Excel spreadsheet.

I respectfully suggest that you learn about database normalization so that you can split that table into AT LEAST 4 tables.

Your Team Stats Goal Times table is not less that two tables and I'm not going to swear it isn't three. Those fields "AG1" "AG2" etc. are what we call repeating groups. That literally SCREAMS for a child table. Ditto "HG1" "HG2" etc.

In the Team Stats table, you have some things at the bottom that look awfully much like a subdivision of some element of a game that also should probably be a child table.

Jossy, if you started this from Excel and wanted to take it to Access, good for you to take that initiative. But you need to do your homework and that means learning that Excel and Access are two different beasties.

In Excel, you can mix formulas and raw data in adjacent columns (or rows, for that matter). In Access, well-structured tables NEVER EVER contain computed data. You use queries as your workhorse elements and let the tables just kind of sit there as reference points for where the queries will get the data they need.