Data being Ignored

[csherman]

I have a small table that builds from a much larger table. I.E. instead of the whole world, I just see the US portion of the business. When an amount is duplicated it ignores one of the values in the smaller table.

Table
Location A $5
Location B $20
Location C $20

2nd Table builds and it shows
Location A $5
Location B $20

As I am summing them for reports I need it to show $45 and not $25 for a total. I am assuming the reason things are dropping out is that the amounts are the same. I experimented, and subdivided one of the $20's in the example above to show $18 in one and $2 in another and now I see the right total.

I am working with a system someone else built. I am a relative newbie with this program. I did not have this problem before we were forced to update to Access 2007.

If anyone can help, or think they can with more information, please feel free to post.

Thanks so Much

 

[MSAccessRookie]

I have a small table that builds from a much larger table. I.E. instead of the whole world, I just see the US portion of the business. When an amount is duplicated it ignores one of the values in the smaller table.

Table
Location A $5
Location B $20
Location C $20

2nd Table builds and it shows
Location A $5
Location B $20

As I am summing them for reports I need it to show $45 and not $25 for a total. I am assuming the reason things are dropping out is that the amounts are the same. I experimented, and subdivided one of the $20's in the example above to show $18 in one and $2 in another and now I see the right total.

I am working with a system someone else built. I am a relative newbie with this program. I did not have this problem before we were forced to update to Access 2007.

If anyone can help, or think they can with more information, please feel free to post.

Thanks so Much

My question is:

Why do you need to store a copy of the data in a different table when you could add a new column (call it USFlag) to the Table, and key on that.
​

You could set the flag to TRUE for USA related values, and FALSE for other values.

• To get the whole world, you would choose Sum()
• To get the USA only, you would choose Sum() WHERE USFlag =TRUE

Having said that, It appears that your problem is with the Join in your query. You did not provide a copy of any queries, but I expect that they contain INNER Joins, and that they need to contain LEFT Joins.

• An INNER Join connects two tables ONLY on columns where they values in common. Mismatches are ignored.
• A LEFT Join connects ALL of the values from the Table on the LEFT side with ONLY the matching vlaues from the Table on the Right side.

See if that helps any

 

[pbaldy]

Welcome to the site. I suspect for anyone to help, they would need to know specifically how the second table is populated from the first. The SQL of a query or the VBA code used.

 

[HiTechCoach]

Are you maybe using a query (not a table)?

If it is a query, would you post the SQL? You may have DISTINCT or DISTINCTROW that is removing the duplicate entries because the primary key is not included.

 

[csherman]

Found the issue. The professional programmers in one of the 6 queries used to build the tables left a Group By rather than a Sum in a total.

Thank you to everyone who tried to help.