UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Detail Duplication In Report, Access 2016    
 
   
shadow888
post Nov 6 2019, 02:57 AM
Post#1



Posts: 14
Joined: 5-November 19



I am trying to create a database for a small (<100 records) real estate application. I have been having real problems trying to get the problem below resolved. This is Access 2019 BTW but I don't see it on the list of available versions)

Also - If I am posting in the wrong forum - apologies let me know where to post and I shall. I think I'm having a hard time with the report but maybe my query is wrong or I haven't normalized properly.

I have attached 4 images:

All Relationship - shows the entire database and relationships
Prop_Title_Lot_Relationships - Shows relationships for the 3 specific tables I'm working with:
tblProperty - Has all the real estate properties
tblTCT - Has the Titles(TCTs) that are related to each property
tblLot - has the individual lots that come under a single title [*This is the issue*]

I am trying to generate a report that Will show the TCTId + Price [Group Header] - then for detail for each group is the lots attached to that TCT_ID (See attached BasicReportLayOut.png)

I have attached a sanitized image of the query that I have put together to generate the report (PropertyQuery.jpg) - I also included the exported XLSX file with the data.

The PROBLEM: I have tried numerous methods but the Purchase Price for each individual TCT_Id 'attaches' somehow to the Lot(s) under the TCT_Id and the totals multiply according to the number of lots attached to the TCT_ID. Even if there is only one(1) lot for a TCT_Id, it can show up multiple times in the detail record.

I'm at my wit's end. I'm sure this is a very basic error I'm committing and if anyone can point out my mistake I'd really appreciate it.

- I'm thinking maybe my table design is wrong (I should separate out the Purchase Information to a separate table) - But I'm pretty sure I'm in 2nd or 3rd Normal form already

I have attached a sanitized version of the original DB Only put in the 3 tables I'm having the problem with - In rptPropertyPurchases you can see the issue I'm facing. - I kind of massacred this DB if any issues let me know

I'm open to any comments. I'm a rank beginner here with Access although i have tried to normalize and do the basic design correctly.

Attached File(s)
Attached File  Desktop.zip ( 1000.61K )Number of downloads: 3
Attached File  Property_TCT_Lot.zip ( 87.43K )Number of downloads: 3
 
Go to the top of the page
 
isladogs
post Nov 6 2019, 03:45 AM
Post#2


UtterAccess VIP
Posts: 1,891
Joined: 4-June 18
From: Somerset, UK


I have made two changes to queries used in your report
1. qryTCT_Lots - error in OrderBy property causing a parameter error - I removed the non-existent field
2. qryPropertyPurchase - changed it to unique values - SELECT DISTINCT ....

Does the report now show what you want?

NOTE I didn't look properly at the table design, relationships or your screenshots.
However, I did notice that your tables tblProperty & tblTCT both contain attachment fields. These are not a good idea as they can cause the database size to 'bloat' dramatically.
Much better to store the path to the file instead

Attached File(s)
Attached File  Property_TCT_Lot.zip ( 101.46K )Number of downloads: 1
 

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
June7
post Nov 6 2019, 03:46 AM
Post#3



Posts: 1,015
Joined: 25-January 16



Suggest you use a subreport for the lots.

If your database does not grow significantly, use of attachment fields probably will not be an issue.

This post has been edited by June7: Nov 6 2019, 03:48 AM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
shadow888
post Nov 6 2019, 04:21 PM
Post#4



Posts: 14
Joined: 5-November 19



Isladogs -

1. Thank you very much for your response.
2. I saw the changes and they helped me understand some of my basic mistakes. Much appreciated.
3. However - although the lot detail records are no longer repeating ( :-) ), the report is still totaling the Title Purchase Price for each Lot attached to the Title - I have attached 2 screenshot extracts that show this
> This was what was causing me problems in the first place.
> I was wondering if maybe I should put the purchase information into a separate table but that didn't make sense to me either. [The purchase info relates to only a single Title Record - I thought that meets the normalization standard]
> June7 - I had tried using a sub-report but had the exact same problem with the totals
4. I understand your comment about the attachments but this is a very small database that will not grow significantly [As June7 remarked]. With all the attachments considered database size is under 90MB.

Thanks


Attached File(s)
Attached File  Report_Extract2.png ( 58.35K )Number of downloads: 5
Attached File  Report_Extract1.png ( 43.78K )Number of downloads: 5
 
Go to the top of the page
 
isladogs
post Nov 6 2019, 05:25 PM
Post#5


UtterAccess VIP
Posts: 1,891
Joined: 4-June 18
From: Somerset, UK


You have a mismatch with the purchase price field in the TCTId header and the Sum(PurchasePrice) in the PropertyName footer
Perhaps you need to restructure your tables to move the purchase price field from tblTCT to tblProperty.

Otherwise, the 'discrepancy' in the totals may well be unsolvable unless you split the report into a main report / subreport as June suggested.

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
shadow888
post Nov 6 2019, 06:16 PM
Post#6



Posts: 14
Joined: 5-November 19



I will take a look at that. By mismatch you mean that I should explicitly define the SUM(PurchasePrice) as Sum(tblTCT!PurchasePrice)?

The thing is that the price is linked to the TCT_ID and not the Property (a property can have more than 1 TCT). And the lots are included under a TCT.

I did realize just this morning that I could move the price to the Lots table artificially by splitting the price between the lots - I'm pretty sure that would work. But it seemed as if I was doing a work around because of my own ignorance of Access. But - if that is the way to go, I can do that.


Thanks
Go to the top of the page
 
isladogs
post Nov 6 2019, 06:21 PM
Post#7


UtterAccess VIP
Posts: 1,891
Joined: 4-June 18
From: Somerset, UK


I was referring to them being in different group header/footer so the sums won't be what you expect.
I'm not in your line of business but if a property is split into lots I would expect each lot to be individually priced - not to have the total property price assigned to each.
To my mind, your workaround is exactly what the situation should be … and your reports will then give the 'correct' totals.

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
shadow888
post Nov 6 2019, 07:08 PM
Post#8



Posts: 14
Joined: 5-November 19



Fair enough. I will work on that - but your feedback has been invaluable. Thank you very much. I'll leave some feedback by tomorrow but yes, I expect this would resolve it.
Go to the top of the page
 
June7
post Nov 6 2019, 07:42 PM
Post#9



Posts: 1,015
Joined: 25-January 16



I agree it makes more sense to have each lot individually priced. However, removing tblLot from report RecordSource makes P2 subtotal = 11500. Then a subreport would list lots associated with property without compounding subtotal.

This post has been edited by June7: Nov 6 2019, 07:51 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
June7
post Nov 6 2019, 09:10 PM
Post#10



Posts: 1,015
Joined: 25-January 16



Okay, rethinking. What does purchase price actually apply to? If you buy a property then subdivide it for resale, each subdivision lot will have its own valuation for a sale price and their sum could be greater than original property purchase price. Might want to apportion purchase price to lots. If you are buying already subdivided lots then each should have individually established purchase price valuation.

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
shadow888
post Nov 7 2019, 05:11 PM
Post#11



Posts: 14
Joined: 5-November 19



June7 / Isladog -

1. This business with lots / TCTs is complicated. This is in Asia in a country where the real estate administration is ... complicated and disorganized.
2. The purchase price applies to the TCT and all the lots in a TCT.
3. However - moving the price to the Lot table has cleaned up everything. And the only thing I need to do is allocate the price over the lots in the TCT. A relatively small thing to do since only 5% of TCTs have multiple lots.

Thank you for all your feedback. Your responses helped educate me and were much appreciated. This discussion has forced me to look again at the dB design and I have found other places where the table design could be more efficient.


Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    10th December 2019 - 01:35 PM