Database across several years

[jeppenedved]

Hi! I would like to create a database for Project at my Company. We have a sheet for each year where we see all Projects in that year. Some of the Projects lasts over new year meaning that, currently, inputs needs to be done in two different workbooks. My plan is to create one centralised database with all Projects and then load them into the workbook for each individual year. I'm testing the "Get Data From Workbook", but that way I get the entire database. I have a few columns which specifies what years that are affected by the projcet, which i'm thinking should be used to define in what yearly Project workbook it should be shown in (the columns only returns true or false). Is there a way to define the Query to only load the data for which the column for 2019 says TRUE? Or, if there is any other solution? Thanks!

[Bernie Deitrick]

You really don't need separate sheets for each year - you could use filters on the master database, or pivot tables, depending on what information you want to summarize.

Or you could use data queries, one for each year. Once you create the query, just use the tools to filter the appropriate column to limit what data is extracted to the sheet.

[jeppenedved]

Yes, I know that I can filter the database. The yearly plans will be used to generate a gantt chart and it uses about 10 combined IF formulas to figure out what value that should be returned for each and every day times, around 1000 rows. We have already seen that the file gets slow so I dont want unecessary data in the file. So, I'm thinking if it would be possible to set a critera in the Query that is should only import the data for the rows that has a TRUE value in one of the columns.

[alansidman]

A normalized data base design should have one table for the projects with a field for the year so that it can be filtered/queried on that basis. Putting the data in multiple tables will cause issues in the future. Here is a white paper on data base design that you might find helpful.

[Bernie Deitrick]

You should be able to amend the query to choose specific values for any column that you are importing.

[jeppenedved]

Thanks for the database instructions. will have a proper look at that. My aim is have one database in one table. So, so far so good

[jeppenedved]

You should be able to amend the query to choose specific values for any column that you are importing.

I found how to sort the data. in Power Query I could filter the criteras TRUE or FALSE so I only get the rows i need for the specifc year. Thanks!