Repeating data in join query

[AccessNovice16]

I preface this post with stating that I have very limited experience with MS Access.



I'm trying to link two tables by using a query since one table has almost all of the data that I need with the exception of description fields (DocNote and TranNote) and the other has such descriptions. When I do this, I'm finding that I'm getting repeating descriptions and amounts even though the original tables do not have this. I've attached my database (zipped) so that you can see exactly what I'm doing (and doing wrong). Any suggestions would be greatly appreciated. Thank you.

[ranman256]

joining tables can produce duplicates.
If you want, to remove duplicates,set query property: UNIQUE VALUES = TRUE

[AccessNovice16]

I think I've tried to do this by going to design view, opening properties, and where it says "unique values", I've used the drop down to select "yes". Is that the same as what you are suggesting? If so, it hasn't resolved my problem. I must be doing something else wrong and it's likely simple since my dataset in my test database in quite small.

[Micron]

As far as I can see, you are not getting duplicate records. Look again.
For the 2nd and 7th records, the dollar values are different. This makes these 2 records unique. Nothing you can do with this query to prevent that as long as one or more fields hold unique values. What's the goal? Maybe you should be calculating in a report instead.

[AccessNovice16]

Sorry, I know I'm not explaining this well but if you look at the data in the table "PSJ", the descriptions (e.g. "Tissue paper for Holiday gift", et al) (i) all belong to account "6550", (ii) are for amounts below $5, and (iii) only appear once. My query is somehow erroneously attributing each of those descriptions to each of the amounts in account "4900". In other words, even though the first record in table PSJ for $71.15 only appears once in my table, it appears five times, once for each description, in my query when I was expecting no description at all since that field is blank in table "PSJ".

[Micron]

Gotcha. Seems to be the combination of concatenated vendor data and your calculated field for January that's problematic. Removing either doesn't change anything. Removing both does. Splitting the concatenated field doesn't help either. I think the issue boils down to normalization as I see a lot of repeated data that looks like it should be in one table and not another. For example, sub account 1300 appears in PSJ for more than one account (4900 and 6550) and in the other table, 4900 has more than one sub account - so you get mix and match.

Not sure why you'd calculate the Jan field in a Totals query anyway, but I confess I'm not trying to make a whole lot of sense of the data since I don't know what it's about. Try this for fun in a new query.

SELECT DISTINCT IIf([Vendor/Customer ID]="","GL",[Vendor/Customer ID]) AS [Vendor Name], IIf([Journal Type]="GJ",[Reference Number],[External Reference Number]) AS Invoice, [GL Quick Query].[Transaction Description], PSJ.DocNote, PSJ.TranNote, Sum(PSJ.[Transaction Amount]) AS [SumOfTransaction Amount]
FROM [GL Quick Query] LEFT JOIN PSJ ON [GL Quick Query].[External Reference Number] = PSJ.[Invoice Number]
WHERE ((([GL Quick Query].Account)="4900"))
GROUP BY IIf([Vendor/Customer ID]="","GL",[Vendor/Customer ID]), IIf([Journal Type]="GJ",[Reference Number],[External Reference Number]), [GL Quick Query].[Transaction Description], PSJ.DocNote, PSJ.TranNote;

Maybe you can use it along with another query if you really need those 2 fields - but I really think you need to bone up on normalization. If I'm right, this is just the beginning of your headaches if you continue down this path. There are other things you're doing that I would not, so here's a good start for much of those things.
Normalization is paramount. Diagramming maybe not so much for some people.

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.ca/...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.ca/...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.ca/...cation-in.html

Important for success:
One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
About Auto Numbers
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html

[AccessNovice16]

Thanks so much, I will take look through those materials. Greatly appreciated!