Beginner's questions

[redhughes]

Hi,



I have been using an Excel spreadsheet as a data source for PowerBI reports/dashboards, but since number of lines in the report exceeded 20k it's been running painfully slow so I decided I could try creating an Access database to replace the spreadsheet.

I am having two problems so far. I have two tables - one with purchase details (i.e. customer, date, price, product etc) and one with customer details (i.e. one line per customer, their address/region and some extras). What I would love to have is a column in purchase table that is automatically populated with the client's region based on the client's name columns in both tables. Could someone please walk me through the process? I did some trial and error joining/relationships/queries but it didn't seem to work...

The other question is whether I can have a column with a calculation that returns a maximum value from the purchase date column (or the column that states the date of Monday of the week when the purchase was made, i.e. week commencing date which is also present in my data). What I want to achieve ultimately, is a column that says 0 for all purchases in the most recent week of the data, and let's say -1 for the week before that, -2 for the week before that and so on. Obviously, this column's contents will change every week as I will be adding data, and therefore week 0 (and others) will be changing. I can then use it in PowerBI to run filters etc.

Thanks in advance for all suggestions, and advice!!

[Micron]

What I would love to have is a column in purchase table that is automatically populated with the client's region based on the client's name columns in both tables.

This you would not do as it duplicates information and violates a basic rule of normalization. At first, I thought you had reviewed this based on the current tables layout, but now am thinking maybe not. Suggest you review normalization if you have not done so.

The other question is whether I can have a column with a calculation

This you also do not do, except for special circumstances. For both questions, the answer is that queries/forms/reports are used to display, assemble data in a relatable way, and perform calculations that should not be stored. If you need to pass a calculation to another system that cannot extract a calculation by referencing a query, then there are other ways. A table of temp values might be one, or a csv file that the other application can read might be another.