1 form two tables

[June7]

Is the account deadline always December 31?

[keiath]

no there are over 300 sites all diffenet

[June7]

Well, would need to know a lot more about your data and business processes. So I guess if the current setup can be made to work to your satisfaction, the job gets done.

[ItsMe]

Access is a tool to manage data. If you apply the basic principals of relational databases you will not have these hurdles you are currently wrestling with. I suggest you search for Access tutorials using terms like RDBMS, Normalization, Primary Key, Foreign Key, committing spreadsheet, etc.

[keiath]

Well, would need to know a lot more about your data and business processes. So I guess if the current setup can be made to work to your satisfaction, the job gets done.

Hi June

It can work and yes I agree probably not the cleanest method, the issue is that these are not normal year end company accounts that we are producing, they are service charge accounts which are wrapped in huge amounts of legal requirements.

The issue with the Accounts Tracker its self is that while I may still be working on data for 2013, I can be working also on 2014 accounts, and lots of data "core" can be changed from one to the next, for example "Accountant" (a combo box, that stores its look up into the main table) in 2013 it could be Accountants Rouse, but in 2014 it could be another accountant and all this information is required (for legal reasons) must be kept. hence the need for 2 Tables one for each year (and this will continue to 2015 and so on) there are perhaps only 2 fields in each of the tables that would never change "Site Number and Site Name" everything else does

However the One thing that doesn't change is the Form layout that remains the same for all the tables, and the reason I believe I need to do it this way.

Now I could simply have 1 form marked 2013 that displays 2013 data and related tables, and 1 form for 2014 data and related table, but that in my mind is even more messy.

Hope that all makes sense.

[keiath]

Well, would need to know a lot more about your data and business processes. So I guess if the current setup can be made to work to your satisfaction, the job gets done.

Hi June

It can work and yes I agree probably not the cleanest method, the issue is that these are not normal year end company accounts that we are producing, they are service charge accounts which are wrapped in huge amounts of legal requirements.

The issue with the Accounts Tracker its self is that while I may still be working on data for 2013, I can be working also on 2014 accounts, and lots of data "core" can be changed from one to the next, for example "Accountant" (a combo box, that stores its look up into the main table) in 2013 it could be Accountants Rouse, but in 2014 it could be another accountant and all this information is required (for legal reasons) must be kept. hence the need for 2 Tables one for each year (and this will continue to 2015 and so on) there are perhaps only 2 fields in each of the tables that would never change "Site Number and Site Name" everything else, some do change some might change.

However the One thing that doesn't change is the Form layout that remains the same for all the tables, and the reason I believe I need to do it this way.

Now I could simply have 1 form marked 2013 that displays 2013 data and related tables, and 1 form for 2014 data and related table, but that in my mind is even more messy.

Hope that all makes sense.

[June7]

Still doesn't convince me can't be one table with another field for the year identifier.

[keiath]

I am not sure how that would be done though

[June7]

As stated would need to know a lot more about data and processes. If you want to pursue this, provide db for analysis. Follow instructions at bottom of my post.

[keiath]

HI June

Please find attached database

[June7]

I see a lot of attachment type fields. Are you aware Access has a 2GB size limit? Attachments can use that up fast.

Suggest no spaces or special characters/punctuation (underscore is exception) in naming convention. Better would be SurplusOrDeficit instead of Surplus/(Deficit) and MonthNum or Month_Num instead of Month Number. ED's should not use apostrophe. What is Ed's1 for?

What exactly is an 'account'. The tracker tables use Site Number as primary key. If site numbers persist across years, there should be a master table of site numbers and info that does not change (site name, company, company type, site type, developer) or does not have historical value and can therefore be edited. Do you care who the manager and accountant were 2 years ago? What if they change mid-year?

Everything else should be in related tables. And not in multiple tables that duplicate the field structure. There will be many records related to each unique site in the Sites table.

Your tracker table shows a lot of empty fields and there are a LOT of fields. Will every record eventually always have data in every field?


Interesting button effects on the Accountants form. How did you do that?

I notice some spelling errors. Should Quires be Queries? Proir should be Prior. Accounts Filed at Companies House is probably intended to be possessive instead of plural so would do: AccountsFiledAtCompanyHouse. Asigned should be Assigned. Dormat probably should be Dormant.

[keiath]

Hi June please see answers in red below

I see a lot of attachment type fields. Are you aware Access has a 2GB size limit? Attachments can use that up fast.

Thanks for that i was unaware of this fact

Suggest no spaces or special characters/punctuation (underscore is exception) in naming convention. Better would be SurplusOrDeficit instead of Surplus/(Deficit) and MonthNum or Month_Num instead of Month Number. ED's should not use apostrophe. What is Ed's1 for?

Noted will do, ed's number of directors

What exactly is an 'account'. The tracker tables use Site Number as primary key. If site numbers persist across years, there should be a master table of site numbers and info that does not change (site name, company, company type, site type, developer) or does not have historical value and can therefore be edited. Do you care who the manager and accountant were 2 years ago? What if they change mid-year?

We are a block management company and have to produce year end service charge accounts. Site number and name never changes (company type and site site can change) developer don't change, all have historical value. the manager changes all the time so not consistent and has no historical value as for the accountant we have a field called previous accountant so that covers that

Everything else should be in related tables. And not in multiple tables that duplicate the field structure. There will be many records related to each unique site in the Sites table.

Yes everything is driven by the site number

Your tracker table shows a lot of empty fields and there are a LOT of fields. Will every record eventually always have data in every field?

Yes

Interesting button effects on the Accountants form. How did you do that?

click on button then format then quick styles. yes its a nice effect

I notice some spelling errors. Should Quires be Queries? Proir should be Prior. Accounts Filed at Companies House is probably intended to be possessive instead of plural so would do: AccountsFiledAtCompanyHouse. Asigned should be Assigned. Dormat probably should be Dormant.

Yes this system is not live and i have not run spell checker over the labels yet

[June7]

Those spelling errors were for field names, not label captions.

Hope you now have enough info to make decision on design path. Good luck.