Access Database for Monthly Field Reports

[mo26]

Dear Sir/Madam,

I need your help. I am new to this forum. After some research, I think it is the best forum on this subject.

I would like to develop an access database that will track the following information on monthly basis. Let’s consider the following table (please see attached) which looks the type of information we receive.

We receive monthly reports from 10 districts (field offices). Each district gets reports from its villages. There are about 30 villages. For example, District1 has three villages (V1, V2 and V3) and so on. There also two service locations (LA and LB) in each village.

Assistance is provided to people in several areas/categories such as (1) Training; (2) Health; Food Security; etc. The database should be able to calculate the totals per category and district. We would like to know, for example, number of females trained on health in district2 this month, etc.

We are currently using Excel but we would like to do it in Access. We also have more general information about districts including names of people and roles.

I would like advice on tables, entity relationship or any other help you could provide.

Your assistance is greatly appreciated.

Best regards,

Mo

 

[mo26]

Hi,

I would like to clarify something and also provide more info so that you can help me better.

There are no villages but only locations. I initially said there are villages and location type. We only have districts (10 of them) and Locations (30). There are two types of locations: Small (LA) and Big (LB).

The following are my tables and their relationship. This is where I need the most help. Please feel free to ask any questions.

Thank you.

tblReport
ReportID
RepMonthID (FK)
DistrictID (FK)
CategoryID (FK)

tblDistricts
DistrictID (PK)
DistrictName
ContactPerson

tblLocations
LocationID (PK)
DistrictID (FK)
LocTypeID (FK)
LocationName

tblLocType
LocTypeID (PK)
LocationType

tblCategories
CategoryID

RepMonth
RepMonthID
Month

tblGender
GenderID
Gender

 

[MarkK]

- I would not create tables to hold Gender or Location type. Gender is basically a yes/no proposition. If you create a gender table it has two records at most and very little chance that management will insist you add new genders at some future time.
- Maybe you don't need a LocationType table either. You have two types, big and small. Maybe you're planning new Medium and Xtra-Large sized locations and you need the flexibility??? Maybe not. Maybe just have a Size field in the location table.
- And tblMonth I think you would never need. Time should be stored with the object it describes. A person has a birthday, an appointment is maybe thursday at 2pm, an artifact was manufactured circa 250 BC. Person, appointment and artifact might be tables, but time is always a field.

- And I would expect to see two other tables, Client and ServiceDelivery. What are you reporting on if you don't have these tables? If you want to know how many females received service in March 2012 then the females will be clients

[B]tClient[/B]
ClientID  (PK)
FName
LName
Gender  (here's the gender)

- The your main table, the abstract ServiceDelivery object which makes everything else meaningful...

[B]tServiceDelivery[/B]
ServiceDeliveryID
ClientID
LocationID
ServiceTypeID (maybe your catergory concept???)
ServiceDate (time dimension)

- And do you need to keep track of service personel?
Let me know if this is helping...

 

[mo26]

Dear Lagbolt,

Sorry, I don't have your real name!

Thank you very much for your reply and help. I really appreciate it.

Below are the tables with your suggestions. About people, we would only need to keep track of the people who send the info (monthly reports from districts) and those who enter them. Attached please see the application purpose.

Once again, thank you for the assistance.

Best regards

M

Monthly Reporting Application Tables

1. ServiceDelivery_Table
2. Client_Table
3. Indicator_Table
4. District_Table
5. Location_Table

tServiceDelivery
ServiceDeliveryID (PK)
ClientID (FK)
LocationID (FK)
IndicatorID (FK)
ServiceDate

tClient
ClientID (PK)
Gender(F/M)
FName
LName
AgeGroup*

*(AgeGroup (15-19; 20-24; 25-29; 30-34; 35-40; 41-45, 46-50; etc.)

tIndicator
IndicatorID
IndicatorName

tDistrict
DistrictID (PK)
DistrictName
ContactPerson
Address
Phone

tLocation
LocationID (PK)
DistrictID (FK)
LocationType*
LocationName

*LocationType (Small, Big)

 

[mo26]

Please find attached a more info about the application.

Thank you,

M

 

[MarkK]

My name is Mark.
I hope you understand that I am only speculating about a structure that will serve your purposes, since I don't completely understand your purposes. It seems possible, for instance, that you do not need to keep track of individual clients. Perhaps a location is intended to supply information that is already summarized to a certain degree and in this case you'll need to adjust your table design accordingly. Maybe you need a ClientGroup table that has a Quantity or Count field to indicate that many clients are represented by a single record.
Or maybe service delivery should look like...

[B]tServiceDelivery[/B]
ServiceDeliveryID (PK)
LocationID (FK)
IndicatorID (FK)
Count
Gender
Month
Year

Maybe gender is not the proper field name if data from a location will be Male, Female and Youth.
For more exact help please post exact examples of data as it would be collected from a location.
Cheers,

 

[mo26]

Hi Mark,

I think you do understand my structure well. I appreciate your help very much.

Yes, we do not keep track of individual clients. Locations submit total number summarized data) of clients (female and male) every month. From this, we calculate the totals by sex per district and per indicators. For example, total number of people serviced on health and total number of women serviced on health, respectively.

We also have another category (Age Group). This has these groups 1(15-19); 2(20-24); 3(25-29); 4(30-34); 5(35-40); 6(41-45), 7(46-50); etc.). But not all locations send this info. Most send only send Female and Male data without the age group. Hopefully, one day we will have all of them sending this information.

About the Indicators, we use code letters such A1 for Training, B1 for Health, etc. What about using these established codes using as ID number of the Indicators table or we should just have it as an autonumber and the codes as separate field.

Here is the table you suggested last. I include ClientID and StaffID. Is this correct?

tblServiceDelivery
ServiceDeliveryID (PK)
LocationID (FK)
IndicatorID (FK)
ClientID (FK)
StaffID (PK)
Count (TxT)
Gender (TxT)
Month (TxT)
Year (TxT)

I added a Staff table holds data about staff (districts – those who send report) and head office – those who enter/edit/use the data).

tblStaff
StaffID (PK)
StaffType (TxT)
Role (Text)
LocationName (Text


Thank you,

M

 

[mo26]

Hi Mark,

Attached file shows what we would like to track. This is just an example, you'll notice the data for both tables are the same. The only difference is the indicator.

Thanks,
M

 

[MarkK]

Yeah, so from that ApplicationExample.pdf, each datapoint appears to have these dimensions...

Count
Gender
Location
LocationSize (note that each location, in that document, has both sizes???)
Month
Year
Indicator

...so that looks like our service delivery table except that in this example each location appears to have both sizes, but I don't get what that means. See how each location has data for small and big?

 

[mo26]

Hi Mark,

Thanks for the suggestion and help. Sorry for responding late.

Is Count (as you suggested last time) a new table and what's it is main purpose. Or can we still make the application work without it?

Count
Gender
Location
LocationSize
Month
Year
Indicator

About you the note that (each location has both sizes), actually it is not about size but rather type of location (physical). For example, small location means it can only offer a limited service compared to a big location. We have about 20 small locations and 10 major ones that offer full service. We only care about the end result/numbers: how people (female, male and youth) were served in given time (month) on what indicators (training). For example, Location A (a small place) trained 10 women, 15 men and 5 children in Dec 2010. So we want the database to store this info and calculate the totals as I have shown in the table before.

I hope this clarifies it.

Thank you.

M


has both sizes???)

 

[MarkK]

No, count is a field. Those are, in my opinion, the fields that you'd need to represent the data as it appears in the pdf you posted. In that pdf, if you look at indicator A1, January 2011, District1, Location L1, there is Male AND Female data for Big AND Small. Four different counts.
So those are the fields you need. The 1) count of 2) gender at 3) location size at 4) location in 5) district having 6) indicator during 7) month during 8) year, so my list in the last post is missing district. It should be in there too.
Does that make sense?

 

[mo26]

Re: Access Database for Monthly Reports

Hi Mark,

Thanks for the email. I agree we need the field (Count) but I'm not sure where it will go (which tables). I would your help to analyse the tables.

Here are the tables. I made this write-up (attached) which I think will explain you better what I am trying to accomplish.

Thank you very much.

M

Service Delivery Table
People Table
Indicator Table
Centre Table;
Location Table
Staff Table

tServiceDelivery
ServiceDeliveyID (PK)
PeopleID (FK)
IndicatorID (FK)
CentreID (FK)
LocationID (FK)
Count
DateRep (Date)
Gender (Txt)

tPeople
PeopleID (PK)
PeopleType (Txt) [Adult;Gender]
Gender (Y/N) [Women; Men; Youth]

tIndicator
IndicatorID (PK)
IndicatorName (Txt)
IndicatorDescription (Txt)

tCentre
SiteD (PK)
StaffID (FK)
CentreName (Txt)
CentreManager (Txt)
Contacts (Txt)

tblLocation
LocationID (PK)
CentreID (FK)
LocationName (Txt)
LocationType (Txt) [SmallLoc; BigLoc]

tblStaff
StaffID (PK)
StaffName (Txt)
StaffRole (Txt)

 

[MarkK]

In post 6 I asked for detailed info as it might be collected at a location. You provided a pdf in post 8. In post 9 I recommended a structure that I believe would be suitable for the data that was presented in that pdf.
I'm not sure what else to add. Obviously you are free to use any info you found to be valuable.
Let me know if you have other questions,
Cheers,
Mark

 

[mo26]

Hi Mark,

Thanks again for post and suggestions. I really appreciate.

I was wondering if the structure (tables) is okay or may need additional information (new table or fields) according to last the doc (pdf) I sent.

Once again, many thanks for your help.

Regards,

M

 

[mo26]

Hi Mark,

Hope you're well.

I would like to ask you another question regarding that the database.

Thank you.

M

 

[MarkK]

Hey there, I'm great. You don't need to ask to ask a question. You have a standing invitation ask questions.
But maybe start a new thread if the topic is new too.
Your call,
Cheers,

 

[mo26]

Hi Mark,

It's nice to hear from you again. Thanks again and I appreciate your response.

I have the same question which I’m not sure if I’m on the right track. This time I have tried to write in different way which mimics exactly the real problem.I hope I could find a help again.
Kind regards,
Mahad

 

[mo26]

Help needed - charity database

Hi,

As I said this is the same application but tried to provide more info (see attached) and I apologize for posting a long one.

I am helping a charity (NGO) in Africa develop a database improve its programs which is seeing an increase of calls for help. The organization helps the poor people by providing essential services such as food, housing, health care, education, etc. in various villages across the country.

We would like your assistance and we really appreciate your help and time.

The organization helps people in 10 villages and their respective centers. Each village has two centres with different names. For simplicity, let’s assume Village1 centres are Centre1 and Centre2. There are 20 Centres with different names and all offer the same services. In other words, people in villages receive help only at the village Centres.

Villages compile data (people assisted) every month to the organization’s main office which compiles data. Every month, the organization would like to know the number of people it has helped in each village and every area (or Indicator): Indicator1-Food; Indicator2-Shelter; Indicator3-Health. For example, the number of people (women and men) received food in a particular month or the total number of women (age group 45-49) that received heath care assistance.

Village reports is summarized data disaggregated by gender (women, men) and age group which includes four tables for each indicator: (TablesA: Cumulative; TableB: Indicator1-Food; TableC: Indicator2-Shelter; TableD: Indicator3-Health). A sample data complied by Village1, in paper form, for January 2011 is attached for our perusal.

The main office receives 10 of these reports every month and compiles all of them. Thus the charity needs a database to store this information and also produce monthly reports for management. The charity would like create a data entry form that mimics the reports from villages for ease of data entry. The database should also be able to capture the staff both at village (those who send reports) and main office (those that enter data and manage). So the database fields would include Reporting Month, Indicator, Village, Centres, Head of Village, etc.

The following are the database tables. I want your help in this specially design structures (tables) as well as any other advice you could assist us.

Your help is greatly appreciated.

Thank you,

M

1. Report Table
2. Period Table
3. Indicator Table
4. Village Table
5. Centre Table
6. Staff Table

tbl Report Table
PeriodID (FK)
IndicatorID (FK)
VillageID (FK)
Data
Gender
AgeGroup

tblPeriod
PeriodID
Month
Quarter
Year

tblIndicator
IndicatorID
Indicator
IndicatorDescrip

tblVillage
VillageID
VillageName

tblCentre
CentreID
VillageID (FK)
CentreName
CentreType (1,2)

tblStaff
StaffID
VillageID (FK)
StaffName
StaffRole

 

[MarkK]

mo, you seem like a nice person, but you seem to make all the same mistakes in your latest post as you made in your original post.
So if you keep repeating your same questions should I just keep repeating my same answers?
Instead I'm gonna direct you to re-read what I've posted and see if you can find answers there, or start a new thread and see if someone else can help you. Maybe you'll understand it more clearly if someone else tells you.
Best of luck,

 

[mo26]

Re: Hi

Hi Mark!,

How you're well. It's been a while. I'm doing fine.

Remember, the database you helped me build? It is okay and working well. Thank you.

I'd like to ask you to help me modify a code (attached).

Thank you.

Mo