Relationships of different tables, and working on forms

[Sean SR]

Relationships of different tables

Before I start on this project, I was wondering if anyone can advise is this feasible and if it is for some kind guidance. I used Access a lot 15-20 years ago but have not touched it since.

Presently working in excel but I think Access could be better

I have 2 massive excel documents with well over 16,000 records each

One I have been working on over a year, and been updating as I go on, its now time to work on the other one but still reference the old one.

This data is originally exported out of Microsoft Dynamics, a Maintenance management package. The Data is based on 1 contract number and each contract number could have up to 20 rows, each listing contract number, Customer ID, Customer Name, Site, Post codes, next & last bill dates and items on contract, Qty, Item Cost and line cost. In addition, the data from this year I have added extra columns in excel, Audit notes, database, and location. Difficult to share based on being sensitive data

1 customer could have up to 4000 sites, some might be just a single location

The data is audited a month at a time, and then by Customer

I was thinking get both sets of data in Access

Table A (2021) and Table B (2022)

I would want to filter by the month (next bill date) based on table B

And ideally work on a form, showing the contract number, and the line details and the extra comment boxes

But also show any comments and details from Table A

I can add notes or edit

And move on to the next record

Working through all those contracts by customer, before moving on to the next for the relevant month

It makes sense in my head hope it make sense, and someone could advise before I spend the whole Christmas period trying to work it out

I could provide some mock-up sample data

 

[Norie]

Sean

Are you planning to split the data up into multiple tables, e.g. customers, contracts etc.?

 

[Sean SR]

Sean

Are you planning to split the data up into multiple tables, e.g. customers, contracts etc.?

Currently in excel that I am working on this year I have a seperate Excel worksheet per customer, but then I have to use a power query to bring it all together, so I was hoping if in Access I would not have to seperate it

 

[Norie]

If you are going to move to Access wouldn't you want set up a 'proper' database structure?

For example, a table for customers, a table for sites etc.

 

[Sean SR]

Not sure I have those skills to realistically decect the data and get it all back again

 

[Micron]

Access is tall (records), Excel is wide (columns). If you design your db tables as you would spreadsheets you will go down a road that will keep you posting here for solutions to problems. You need to research db normalization at the least but there are many other topics that if reviewed, will save you a lot of grief. I'm quite confident that you're currently on that path because no experienced db developer worth his/her salt would have a table for each year of data. Rather than me re-post those links, perhaps see post 5 here:

Converting existing Excel Project Database over to Access

So after asking for help in the Excel forums the overall consensus was that going to Access may be a better option. I have very limited experience with Access. So I imported in my excel sheet. Currently it is 150 rows long by ~60 columns wide. I set each columns data type. Other than the...

www.mrexcel.com