How can I auto-update a table used to track monthly progress based on information in other tables

[Thadeus1991]

Good afternoon everybody,



I am a newbie to the forum (1st post as a matter of fact) and also fairly new to access.

To explain my problem(s), let me tell you what I use my database for and what I am trying to achieve:


I am using the database to store information of the supply chain of our company. The company I work for is a fruit importer, so our supply chain looks something like this:

Growers --> Pack Sites --> My Company --> Retailers

We have several suppliers, each with one or more sites. For each supplier site, I store information relating to the commodities the site processes, the country the site is located in, which customer(s) each site is used for, etc.

I also track the Due-Diligence status of each site using the access database (e.g. has the site provided their reference number, have they shared the site's information with us, have they completed their questionnaire, etc).

Naturally, I am tracking the progress month-on-month. By "progress" I mean every month I track, how many sites have shared their information, have completed their questionnaire, etc. so that I am then able to make month-to-month comparisons.
I used to do all this in Excel but have now changed over to Access. To track the progress I have a table that I want to update every month for each of our three commodities (so 3 new rows each month) to track the progress. It looks like this:

Commodity	Year	Month (Numbr)	Month (Text)	Total Suppliers	Compliant Suppliers	Total Sites	Sites that are linked online	Sites with completed questionnaire	Number of audits in last 2 years
...	...	...	...	...	...	...	...	...	...
ST	2017	10	Oct	5	4	20	17	17	10
AV	2017	11	Nov	100	85	500	390	300	210
TF	2017	11	Nov	50	50	80	75	70	50
ST	2017	11	Nov	5	5	20	19	19	12

My question is, how can I create an append query (or even just a form) that allows me to easily update this table?

I struggle to do this in any way that is not completely manual due to several reasons:

• I can't figure out how to count unique entries based on commodities (e.g. how many suppliers/sites are there for the commodity AV)
• I can't work out what the best way is to do a count of compliant sites ("compliance" of a site in "tblPack_Sites" is based on information from table "001tracker..."
• I can't work out how to make all necessary values appear in one single query/form/report

The only thing I have managed so far (after hours of Google) is to do counts of the suppliers and sites by commodity by using a Totals query (but only ever got the result for one commodity, so would have to run three queries to get the results for each commodity individually. Also, I was unable to take these results and add them to a form).

Please see below the relationship structure of my database, to give you an idea of the database.



If you guys need more information, please ask and I will provide the info.

Any help would be very much appreciated.

[orange]

You will have fewer syntax errors if you adopt a naming convention that does not use embedded spaces in field names.

It would be better for you and readers if you would start with a clear, simple English description of the business you are trying to support with this proposed database.

I see Suppliers, Sites and Commodities. What exactly do you mean by "track the progress"?

[ssanfu]

In addition:

Database Table and Field Naming Suggestions
http://www.databasejournal.com/featu...uggestions.htm


Some suggestions:
Use only letters and numbers (exception is the underscore) for object names.
Do not use spaces, punctuation or special characters in object names.
Do not use look up FIELDS, multi-value fields or calculated fields in tables.
Do not begin object names with a number.

I always use an autonumber type field for the PK field, NEVER test fields (It appears that "Country" is a test type field)
It looks like you have 3 MVFs in "tblSuppliers". Most experienced Access programmers/developers avoid MVFs.

[Thadeus1991]

You will have fewer syntax errors if you adopt a naming convention that does not use embedded spaces in field names.

It would be better for you and readers if you would start with a clear, simple English description of the business you are trying to support with this proposed database.

I see Suppliers, Sites and Commodities. What exactly do you mean by "track the progress"?

Thanks for that - I will make sure to change the field names and replace spaces with underscores.
Regarding your other questions, I updated the original post now, here are the answers:

• The company I work for is a fruit importer, so our supply chain looks something like this:
  
  Growers --> Pack Sites --> My Company --> Retailers

• By "progress" I mean every month I track, how many sites have shared their information, have completed their questionnaire, etc. so that I am then able to make month-to-month comparisons.

I have also extended the table in the post to make more obvious what I mean by "tracking the progress"

Does this help?

[orange]

A few things still not clear:

-questionnaire
-commodity AV
-compliant Site
-online site
-001tracker...
-audits

Again, step back and give readers an overview of the business in simple terms. We're willing to help, but should not have to guess a the meaning and fit of the various terms. Look at this from a different perspective --you have a group of people(designers/developers/testers) who are seeking a clear understanding of the business processes and your requirements before offering advice or jumping the gun and/or giving options for a problem they don't really understand.

Good luck

[Micron]

If a supplier and a pack site can be in different countries, then OK with country being in each of those tables. If not, then no. No clue as to the 001 table needing country without understanding the significance of it. This might also mean that country field needs to be joined to suppliers and pack sites. What's more worrisome is the multi value fields as noted. Really suggest you review normalization so that you can better grasp the concept of entities and relationships as well as a few other highly important subjects:

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.ca/...on-part-i.html
and/or
http://holowczak.com/database-normalization/

One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
About Auto Numbers

- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html