Solved Missing records (1 Viewer)

[hhag]

Hi,
I've a query which I run with absolutely no filter. And I don't get all the records. Why?
The query is data source to a subform in a form.
It consists of fields from several tables, which all are connected with one-to-many relations.
Does anyone have any idea why I don't get all records? I can't see any odd things with these records.
The intention is, which I also do, to filter this query from a combobox.
When I picked a specific value (a customer) in the combobox I saw that I didn't get any records in the subform, and now I don't know if I can trust the result.

Rgds,
HHAG

 

[isladogs]

Assuming the query has inner joins, only the records present in all tables will be listed.
If you want all records even where there are null values in one or more tables, use outer joins.
See Query Join Types

 

[HalloweenWeed]

Hi,
I've a query which I run with absolutely no filter. And I don't get all the records. Why?
The query is data source to a subform in a form.
It consists of fields from several tables, which all are connected with one-to-many relations.
Does anyone have any idea why I don't get all records? I can't see any odd things with these records.
The intention is, which I also do, to filter this query from a combobox.
When I picked a specific value (a customer) in the combobox I saw that I didn't get any records in the subform, and now I don't know if I can trust the result.

Rgds,
HHAG

How does your combobox filter your results? I am assuming you have some conditionals in the query that gets it's values from the combobox. If that's the case, an "empty" (or default value) combobox selection may have undesirable results. And from my experience, you can't put conditionals such as "IIf" statements within a Query condition field. Instead, create a custom column with an algorithm to compute a value, and then set the result conditional to bring up only those rows which match. Something like this:

Expr1: IIf(IsNull([Forms]![frmSearch]![ComboBoxElement]), True, False)

Combine that with a "True" in a conditional row, and on another row your current conditional in the same column as previous. This should result in giving you all the table when the combobox field is Null.

 

[hhag]

Hi,
Pls, see attached screenshot below.
1. This is the field I use a combobox to filter on. I don't have any empty fields in the tables. (Doubled checked)
2. tblAnbudsledare and its relationships to the other two tables is the problem. The query only picks the records when tblAnbud.RegistreradAvID=tblAffärsmöjligheter.AnbudsledareID.
3. One person can register a record in tblAffärsmöjlighet
4. Another person (though picked from the same table, tblAnbudsledare) can register another record in tblAnbud. And when these two persons are different, that's when the query 'misses' to include items from tblAnbud.

It should be possible to use different persons. And I thought it was a good idea to reuse the table in order to reduce data and redundancy.
Now, my question is if I should create a copy of the tblAnbudsledare (and have to identical tables, which can't be necessary, or?) or if it's possible to use a Union query in some manner instead? And how to do that?

Rgds,
HHAG

 

[isladogs]

You should never duplicate data though you can of course use the same table more than once in a query,

Did you consider the points I made earlier regarding outer joins?

 

[hhag]

Hi isladogs,
Sorry for late response. Yes, indeed I considered your comment re outer joint. I checked the join properties and try to select an outer join, but then I got a message that (if I recall correct) I should edit the SQL string instead or something like this. Well, to be honest, I'm not a professional regarding editing SQL strings.
I read some more about different types of joins and then I realized that a relationship and a join is not the same thing. As far as I understood you don't need to have relationships in querys (which now are called joins). You can disconnect these. So what I did was actually to disconnect the join between tblAnbudsledare and tblAffärsmöjlighet, since I don't need to filter on the former regarding records in tblAffärsmöjlighet. I only want to filter tblAnbud.RegistreradAvID. After I removed this join, I got the result I needed, which means all record in tblAnbud.

 

[isladogs]

Excellent. Pleased to hear you have a solution.
If you want to know more about relationships and referential integrity, see this article http://www.mendipdatasystems.co.uk/relationships1/4594533224

 

[hhag]

Thank you so much for your help!
And I'll most certainly be back with new 'problems' ahead

 

[Pat Hartman]

You might have a solution but there is something wrong with your schema. I can't work out the problem because I can't understand the column names but the diagram indicates some type of pathological relationship between the two tables.

 

[hhag]

Well, I'm Swedish so it's Swedish text you see
Yes, I do agree that the scheme looks a bit odd. I've two relationsships (one-to-many) established from tblAnbudsledare. Same persons can create records in both tables (tblAfärsmöjlighet and tblAnud). One person ID can create a record in tblAffärsmöjlighet and don't necessary need to create a record in tblAnbud. But I don't want to have two tables of tblAnbudsledare which are identical. This seems also odd. Any good ideas how to solve it better?

 

[Pat Hartman]

I don't have a frame of reference without being able to read Swedish. Without being able to read the column names, I can't tell what data you are trying to store. If you can take the time to translate the tables, I'll see if I can make sense out of it. I'm having surgery on Friday so I'll look for this tomorrow, If I don't see it, it will be several days before I get back on line.

 

[hhag]

@Pat
Sorry for a very late reply. I've had some time off from my computer. And I do hope your surgery went well and that you're feeling better.
As you see above I've one table (tblAnbudsledare = tblTenderPerson) with a one-to-many relationship with two different other tables. This is due to the fact that one person ('Initialer'=initials in Eng) can add a record in the two other tables. These two tables are connected with a one-to-many relation. It doesn't have to be the same person who notes one record in the parent table and then notes the corresponding record/records in the child table. Though I only got the corresponding records (same initials in both the parent and the child) in my query. So I just disconnected the relationship between the tbalAnbudsledare and the other parent table in my query, since I'm not interested in knowing the initials in the parent table. And then it worked.
Since I want to reuse the tblAnbudsledare I made the connections as you see above. And I don't have any other good solution to get around this, more than really observe what's happening in my queries.