Expert help / advice in creating a table

[champion83]

Hello,

Some time ago I started building a database in Access. It was supposed to replace the spreadsheet that is currently in use. The data database will have various projects listed and savings reported weekly for each project. Each project is usually reported over 52 weeks period, i.e. if the project starts, for example, in week 44 - 2019, the project will end in week 43 - 2020. The problem for me is that at the moment every year a new spreadsheet is created and the same project appearing in both files. I would like to create one Access Database one for all years.

I am looking for the best solution and my question is:

Is it possible to create a dynamic table with only 52/53 columns / rows or is it better to create a table with columns / rows for the next 5 years, i.e. over 260 columns / rows?

I also have to consider that I will have to create one userform where we can change the data for each project. There will also be 53 fields where we will have to enter savings for a given week. If I create these 260 columns, each field in the userform will have to connect to the appropriate column of the table.

Another thing is that later I will have to create an annual report and if I will go for option with 52/53 columns, the report will only have to show me the savings for the year (not 52/53 weeks).

I hope that I managed to explain what I want to achieve.


Thanks in advance,

Lucas

 

[theDBguy]

Hi Lucas. If I understand your situation correctly, my answer would be neither. Instead, I would recommend a table structure similar to this.

tblProjects
ProjectID, pk
ProjectTitle
etc.

tblProjectSavings
SavingsID, pk
ProjectID, fk
ReportDate
etc.

Hope that helps...

 

[MajP]

None of those solutions are correct. You only need a couple of columns and each record is a week. Give us a little more information. That is a spreadsheet, you need to build a database. You can use a crosstab query to display in columns if needed.

TheDBGuy beat me, but that is the approach you need.

 

[champion83]

@MajP , @theDBguy

Thank you both for your very quick reply. In Excel, I had everything in one table and I was hoping that I will do the same in MS Access, but since I'm using Access, I'll have to start thinking a little differently.
Due to fact we have introduced the dates, I will probably will need to add an additional table which will translate these dates into company's trading weeks & periods (if I understand it correctly)
I will have to as well figure out how to create some user friendly userform which allows the user to see/modify all this information in one window.

Tomorrow I will try to figure it out. I hope I don't hit the wall again.

Thank you again for today. I will keep you informed about my progress.

Lucas

 

[theDBguy]

I will probably will need to add an additional table which will translate these dates into company's trading weeks & periods (if I understand it correctly)

If you can reduce the "translation" into a logical process, you may not need a table. Instead, we could probably create a function to convert any given date into its equivalent company period. If so, you won't have to maintain and update a table of dates.

 

[Uncle Gizmo]

It is fortunate that you asked here before you set about building your MS Access database. It's a very common issue for people moving from Excel to MS Access, you can create a minefield of problems if you duplicate Excel in MS Access. I discuss some of them in Text and YouTube's on my blog here:- https://www.niftyaccess.com/excel-in-access/

I also have a tool for helping the conversion of Excel type tables into Access type tables:-

Nifty Transpose Tool - MS Access

Are you regularly tackling the challenges of disordered spreadsheet data? Seeking a solution that can provide a smoother, more efficient transition from spreadsheet structures to an effective database system? Here's your solution! Introducing the "Transpose Tool," also known as the "Database...

gum.co

Details Here:- https://www.niftyaccess.com/normalization-tool/

Contact me for instructions, if you want a free copy.

 

[The_Doc_Man]

@champion83, one of the biggest "hits" that a person takes during the transition from Excel to Access is a tiny little difference that is astoundingly important.

In Excel, each cell contains your data. Excel creates that sheet for you but other than "diddling" with the cells, the data you see on the sheet is what you have. You have to play with complex searches to try to correlate stuff, or you have to be clever in your data layout.

In Access, what you see doesn't have to be what you have stored. You can reformat things to differ from actuality (like different number of decimal places or different date formats, etc.) You can build queries that take data from multiple distinct sources and combine them in ways to better display what you have stored. Access delineates between computationally oriented data and display-oriented data.

As suggested by theDBguy and MajP, you need to focus on the best way to store your data, which is probably a single table that contains dates. Then, when you want something limited to 52 weeks, you write a query to impose that limit. You don't impose that in data storage. You impose it for data display. This is an important difference between Excel and Access but it covers a LOT of heartaches.

 

[mike60smart]

@MajP , @theDBguy

Thank you both for your very quick reply. In Excel, I had everything in one table and I was hoping that I will do the same in MS Access, but since I'm using Access, I'll have to start thinking a little differently.
Due to fact we have introduced the dates, I will probably will need to add an additional table which will translate these dates into company's trading weeks & periods (if I understand it correctly)
I will have to as well figure out how to create some user friendly userform which allows the user to see/modify all this information in one window.

Tomorrow I will try to figure it out. I hope I don't hit the wall again.

Thank you again for today. I will keep you informed about my progress.

Lucas

Hi Lucas

Can you upload a copy of your database in zipped format together with a copy of your Excel file with no confidential data?

 

[Pat Hartman]

Access has lots of functions. If you need to convert a date to a week number, you would use a function. Since projects cross years, you would use the Format() function because you would need to include year in order to get weeks to sort correctly if you are using the actual week of the year. If you want to use an absolute week, i.e. Week 1 is the week your projected started, create a function. Pass in the ProjectStartDT and have it return ProjectWK.

Once you get your head around the concept of columns in a spreadsheet needing to be rows in a table, your next step is to familiarize yourself with Access functions. If you search, look for a link that lists them by category so that all the date functions are together and all the string functions are together. Otherwise, it is too hard to find the function you need without knowing its name beforehand.

 

[champion83]

Thanks again to everyone for your help. Sorry to reply so late but I have been given other high priority tasks to do...

So far I am in the process of building an Excel formula that will be able to convert dates into weeks. Creating such a formula in excel / access function isn't so easy because it will have to understand as well how to deal with the lap years. When I find a solution in Excel, I will try to do the same in Access.

Tomorrow I will try to generate more free time to think how to remove confidential information from existing Excel database and new Access DB then I will try to share both files on this forum.

Thanks,
Lucas

 

[Minty]

I wouldn't re-invent the wheel.
If your company has a non-standard or strange week numbering methodology then simply create a calendar table with the relevant data points you need. (Have a google for it)

Access does have a week() number function that may be sufficient for you needs? It takes account of leap years.

 

[Pat Hartman]

Access has a function that will return the week. I'm pretty sure Excel does also. Why are you building your own?

 

[champion83]

Hi all,

I'm not trying to create my own function . I would like the function to be able to calculate the weeks as in the attached calendar. This is only the part of the calendar that includes only customer's weeks & periods. The second part is to include my employer's weeks and periods. If I don't figure it out, it won't be the end of the world. It would be nice to automate everything, but if you can't, you can't.

Thank You,

Lucas

 

[plog]

That's really too much data to consume. Can you explain the logic?

Why does Year=2019, Period=1, Week=1 start on 1/27/2019? Is that not logical, but just a constant and everything downstream is calculated off that?

Why does Year=2020, Period=1, Week=1 start on 2/1/2020? What's the math between periods and between weeks?

 

[champion83]

@plog

I'm not sure what logic the client is using, what I noticed is:

• Each week starts with Sunday and ends with Saturday.
• There are never incomplete weeks. All weeks have 7 days
• The year preceding a leap year is always 53 weeks long. The remaining years are 52 weeks long.
• Periods:
  • Week 1-4 = Period 1
  • Week 5-8 = Period 2
  • Week 9-13 = Period 3
  • Week 14-17 = Period 4
  • Week 18-21 = Period 5
  • Week 22-26 = Period 6
  • Week 27-30 = Period 7
  • Week 31-34 = Period 8
  • Week 35-39 = Period 9
  • Week 40-43 = Period 10
  • Week 44-47 = Period 11
  • Week 48-53 = Period 12

This is probably all that may seem logical on this calendar.

 

[plog]

I would like the function to be able to calculate the weeks as in the attached calendar...
... I'm not sure what logic the client is using,

Without definite logic you cannot calculate it. You will need to keep that excel file you posted as a table and reference it when you need the Year/Period/Week of a date.

 

[Minty]

I'm with @plog, these are non-standard periods so the best and most efficient way to deal with them is in a calendar table.
Load it up with all the real dates and then add in the weird dates groupings and references you need. Something like this;
tb_Calendar

FullDate	DayOfMonth	DayOfWeek	DayofYear	DateYear	DateQuarter	DateMonth	DayName	WorkingDay	IsWeekend	IsHoliday	HolidayName	Period	ISOWeek	NthDayOfMonth	FiscalStartDate	FiscalEndDate	FiscalWeekNo	FiscalMonth	FiscalYear	LastMonOfMonth	LastTuesOfMonth	LastWedsOfMonth	LastThursOfMonth	LastFriOfMonth	DaysInMonth	LastXofTheMonth
01/01/2010​	1​	6​	1​	2010​	1​	1​	Friday	0​	0​	-1​	New Years Day	201001​	53​	1​	06/04/2009​	05/04/2010​	39​	10​	2009​	0​	0​	0​	0​	0​	31​	0​
02/01/2010​	2​	7​	2​	2010​	1​	1​	Saturday	0​	-1​	0​		201001​	53​	1​	06/04/2009​	05/04/2010​	39​	10​	2009​	0​	0​	0​	0​	0​	31​	0​
03/01/2010​	3​	1​	3​	2010​	1​	1​	Sunday	0​	-1​	0​		201001​	53​	1​	06/04/2009​	05/04/2010​	39​	10​	2009​	0​	0​	0​	0​	0​	31​	0​
04/01/2010​	4​	2​	4​	2010​	1​	1​	Monday	-1​	0​	0​		201001​	1​	1​	06/04/2009​	05/04/2010​	40​	10​	2009​	0​	0​	0​	0​	0​	31​	0​
05/01/2010​	5​	3​	5​	2010​	1​	1​	Tuesday	-1​	0​	0​		201001​	1​	1​	06/04/2009​	05/04/2010​	40​	10​	2009​	0​	0​	0​	0​	0​	31​	0​
06/01/2010​	6​	4​	6​	2010​	1​	1​	Wednesday	-1​	0​	0​		201001​	1​	1​	06/04/2009​	05/04/2010​	40​	10​	2009​	0​	0​	0​	0​	0​	31​	0​
07/01/2010​	7​	5​	7​	2010​	1​	1​	Thursday	-1​	0​	0​		201001​	1​	1​	06/04/2009​	05/04/2010​	40​	10​	2009​	0​	0​	0​	0​	0​	31​	0​
08/01/2010​	8​	6​	8​	2010​	1​	1​	Friday	-1​	0​	0​		201001​	1​	2​	06/04/2009​	05/04/2010​	40​	10​	2009​	0​	0​	0​	0​	0​	31​	0​
09/01/2010​	9​	7​	9​	2010​	1​	1​	Saturday	0​	-1​	0​		201001​	1​	2​	06/04/2009​	05/04/2010​	40​	10​	2009​	0​	0​	0​	0​	0​	31​	0​
10/01/2010​	10​	1​	10​	2010​	1​	1​	Sunday	0​	-1​	0​		201001​	1​	2​	06/04/2009​	05/04/2010​	40​	10​	2009​	0​	0​	0​	0​	0​	31​	0​
11/01/2010​	11​	2​	11​	2010​	1​	1​	Monday	-1​	0​	0​		201001​	2​	2​	06/04/2009​	05/04/2010​	41​	10​	2009​	0​	0​	0​	0​	0​	31​	0​

Although a lot of these fields can be worked out if you do a lot of date-based calculations, it's a lot easier to either join or query the right field based on the dates you have and the values you store in here. This type of table doesn't use a massive amount of storage but can reap big benefits depending on your usage.