Access database idea with lots of combinations (1 Viewer)

[Kajtek116]

Hi to all
I am creating a database whose output is to be Excel data as shown below

I have several phases, nearly a hundred different items, several responsible and several additional documents.
I created tables for them. And I'm stuck.
When creating a new document, 100 rows are created in the table with the name (repeatable), phase (repeatable), item (unique), revision, etc.
When editing and creating a new revision, I need to add another line for item with a new date.
Normally one document should have 100 lines, but when editing and revising changes (even about 10) about 1000 lines are created for one document.
And now the question is, do you have any ideas for creating such documents in Access, and maybe I'm going the right way.

I am asking you for help and any ideas, experiences

Thank you

 

[jdraw]

Welcome and good luck.
"creating a database whose output is to be Excel data "???
Please tell us about the application. Why are you using Access? Why not use Excel?
In simple terms WHAT is the subject matter and purpose of this project?

 

[Kajtek116]

Welcome and good luck.
"creating a database whose output is to be Excel data "???
Please tell us about the application. Why are you using Access? Why not use Excel?
In simple terms WHAT is the subject matter and purpose of this project?

I have dozens of such documents made in Excel files and saved in separate files (and new ones are still being created). The application is to be created so that you can quickly view the status of individual documents (all item done, then document done), now I open each Excel file separately and check if someone has changed anything in it.
I have to manage it and wanted to make an application that will allow me to quickly determine the status of the document, I thought that in Access it would be more convenient to create tables and queries, and then on the Excel side visualization of this data.

 

[MajP]

This is pretty common to have a document tracker with workflows and revision information. Since it is common, I would Google to see if you can find an Access template that you like and could be a starting point. Understand you need to get the table structure correct and people here will help you with that.

 

[jdraw]

You did ask for any ideas/suggestions:
so with "someone has changed anything in it" being the key to changes

My advice would be to have that someone identify what they accessed and what was changed as a starting point. Before doing anything in Access, I recommend you draw a picture identifying the
things involved and what relates them one to another.
It's always better to get a clear description of what you're trying to do; bounce it off others to ensure it is complete and clear; get a good data model; test it with mock data to validate the model; then move to physical database development. Jumping into physical database (Access or other) without a plan and/or model can be a very long, difficult journey.

 

[Kajtek116]

I already have some database outline

And I would like to get this effect:

I am asking about your experience in creating such databases.
I have already made quite a few applications, where access is used as a data base, and Excel is the front for users (it's easier for me to create forms, charts, etc. in Excel), which only selects and updates on tables (using VBA and SQL).
Unfortunately, this time I got stuck up a bit and I don't know if I'm combining well.
I thought that the easiest way to create a new document would be to cross the name of the document (id) with items and save it to the table (100 lines received in this way). And then for each row (doc ID, item) add further revisions (download doc name and item from the database, edit it and save it as a new record with a new revision).
I just wonder if the path I have chosen is good, and maybe you have any other ideas.

If you have any similar bases in action, then I would be very grateful if I could take a look.
I am not looking for a ready solution, but rather advice if I combine well, and maybe try something different

 

[The_Doc_Man]

It sounds like you are at least partly still in a design mode. One of the most important questions to answer - and understand - is "where is the authoritative copy?"

In any multiple-document situation (not limited to Access/Excel combinations), there can be one and ONLY one "authoritative version" of anything. Stated another way, if you have two copies of something, each one from a different source, and they don't agree on something, which one is right? If you cannot answer that question, you are doomed before you start. Your design MUST include that concept and its ramifications.

In such an environment, any copy in any other medium or format outside of the authoritative source can ONLY be considered as a point-in-time snapshot, true only for the moment of that snapshot and never again true. There are two major implications to that "authoritative" concept as you are trying to implement it.

First and foremost, you CANNOT care what happens to the snapshots because they are not authoritative. What I did was allow an end user to ask for a status-summary spreadsheet to be generated for their specific project. Didn't take that long, really. They could do what they wanted with it it, but what they could not do was make changes and re-import that spreadsheet. If they had to make changes, they had to use the facilities of the authoritative source, which was my Access database. If you can make forms in Excel, you can make forms in Access. It is important to provide an atmosphere where folks "trust" your authoritative source. Otherwise, your data integrity will at least potentially be subject to the failing of having someone maintain the spreadsheet and forget that they should be coming to the authoritative source for maintenance. (NOTE: This is more of a procedural problem than a technical problem, since an external form that updates the authoritative source from an external app adheres to the letter of the "authoritative" concept - but not the spirit.)

Second, you cannot worry about past dates for something if it represents a summarized status as of a given date. If you are having trouble because your code is making too many rows, then you have faulty design of what you are building or faulty logic in the builder code. You show us summarized statuses like DONE or ONGOING. I did much the same thing, but that meant doing some kind of logic to roll up all related entries across multiple dates to give me a summary status such as you are doing. The only date I ever reported wasn't in the spreadsheet's main body. It was in the formatted header that included the phrase "Contains all updates through 12:00:00 12-Jul-2016" (or whatever day it was.)

Unfortunately, the only database where I did anything similar belongs to the place where I used to work before I retired, so I cannot share it. But it did a lot of what you suggest you want to do, so I really do understand the goal.

 

[Kajtek116]

It sounds like you are at least partly still in a design mode. One of the most important questions to answer - and understand - is "where is the authoritative copy?"

In any multiple-document situation (not limited to Access/Excel combinations), there can be one and ONLY one "authoritative version" of anything. Stated another way, if you have two copies of something, each one from a different source, and they don't agree on something, which one is right? If you cannot answer that question, you are doomed before you start. Your design MUST include that concept and its ramifications.

In such an environment, any copy in any other medium or format outside of the authoritative source can ONLY be considered as a point-in-time snapshot, true only for the moment of that snapshot and never again true. There are two major implications to that "authoritative" concept as you are trying to implement it.

First and foremost, you CANNOT care what happens to the snapshots because they are not authoritative. What I did was allow an end user to ask for a status-summary spreadsheet to be generated for their specific project. Didn't take that long, really. They could do what they wanted with it it, but what they could not do was make changes and re-import that spreadsheet. If they had to make changes, they had to use the facilities of the authoritative source, which was my Access database. If you can make forms in Excel, you can make forms in Access. It is important to provide an atmosphere where folks "trust" your authoritative source. Otherwise, your data integrity will at least potentially be subject to the failing of having someone maintain the spreadsheet and forget that they should be coming to the authoritative source for maintenance. (NOTE: This is more of a procedural problem than a technical problem, since an external form that updates the authoritative source from an external app adheres to the letter of the "authoritative" concept - but not the spirit.)

Second, you cannot worry about past dates for something if it represents a summarized status as of a given date. If you are having trouble because your code is making too many rows, then you have faulty design of what you are building or faulty logic in the builder code. You show us summarized statuses like DONE or ONGOING. I did much the same thing, but that meant doing some kind of logic to roll up all related entries across multiple dates to give me a summary status such as you are doing. The only date I ever reported wasn't in the spreadsheet's main body. It was in the formatted header that included the phrase "Contains all updates through 12:00:00 12-Jul-2016" (or whatever day it was.)

Unfortunately, the only database where I did anything similar belongs to the place where I used to work before I retired, so I cannot share it. But it did a lot of what you suggest you want to do, so I really do understand the goal.

Thank you with the hint.
Once again I will think about the application logic and try to redesign it.
I will try to design everything in Access, including Forms.

Thank you all for the tips

 

[The_Doc_Man]

If I may add a little more advice: Getting your people to use Access to do updates when they were used to something in Excel will involve a learning curve - for them and for you. Preparing an Access interface for non-Access users can be tedious. Search this forum for "Securing a database" to find info on how to structure a DB that your users won't easily undermine. And we understand that at times it can be a little overwhelming. You feel like you are playing that game "Whack-a-Mole" with about 100 moles jumping all at once. Been there, done that, no fun - but when it is finished and working like you want it, there is a sense of accomplishment and maybe more than a little pride of workmanship.