First time creating a simple db

[yellowdragon]

I have data that I download daily that I will use to generate reports. I’m importing the data into a table called table_ISA, a few ofthe fields are: lname, fname, percent complete, and reference. The referencefield has multiple entries in it that I want to roll up to a different name(for reporting), a couple examples are: Admin, HR will all be under the newheading HQ. Windows, Network, and operations will all be under the heading IT.
I’m sure there are many ways to do this, and since I’m kindof new to access, I’d like to apply good DB practices. I was thinking ofcreating another table called Table_Organization that would look something likethis:
1, windows, it
2, network, it
3, operations, it
4, admin, hq


5, hr, hq
The Table_ISA will have multiple references to windows,network, operations etc.
Where I’m going with this is to have a report that lookssomething like this.
______________________________________
Last name first name Org. Incomplete
Smith john IT 10

The number 10 under incomplete is the count of records thatare incomplete. I’ve got that part figured out, I need help with getting thereference field in the table ISA rolled up to generate a report with the titleOrg.
I was thinking of using the Table_Organization to assist inrolling up to the higher level name, but I’m not sure how to do this. Whichfield would I use as the key field in the relationship of the tables, and howwould I ensure that the Table_Organization has all the entries that are in theTable_ISA that I import daily.

[jzwp11]

You are on the right track, but I would also set up the headings in a separate table and reference it in the organization table. I'll call the headings departments since that is what they appear to be (feel free to change the name to suit your needs)

Table_Organization
-pkOrgID primary key, autonumber
-txtOrgName
-fkDeptID foreign key to tblDepartment (long integer number data type field)

tblDepartment (HQ, IT etc. as records)
-pkDeptID primary key, autonumber
-txtDepartment

Now regarding the reference field in Table_ISA, you said the following:

The referencefield has multiple entries in it that I want to roll up to a different name

Does each record only contain 1 specific reference name?

Like this: john|smith| windows|

Or can it be this: john|smith|window,operations...|


If it is the first option, then this is how Table_ISA should be structured

Table_ISA
-pkISAID primary key, autonumber
-lname
-fname
-percentcomplete
-fkOrgID foreign key to Table_Organization (long integer number data type field)



BTW, it is best not to have spaces or special characters in your table and field names (the underscore _ is OK).

The number 10 under incomplete is the count of records thatare incomplete. I’ve got that part figured out, I need help with getting thereference field in the table ISA rolled up to generate a report with the titleOrg.
I was thinking of using the Table_Organization to assist inrolling up to the higher level name, but I’m not sure how to do this. Whichfield would I use as the key field in the relationship of the tables, and howwould I ensure that the Table_Organization has all the entries that are in theTable_ISA that I import daily.

All your joins between tables should be through the numeric fields (primary key to corresponding foreign key). For your report, you would first create a query. You would bring in all of the tables and then just select the corresponding text field (txt prefix) that has the text you want to display in the report. You would then bind the report to the query. You can set up grouping levels in the report based on each department and then if you want on each organization within the department. That might look something like this

HQ
HR
John Smith 10
Admin
Joe Public 5
IT
Network
Mary Smith 95
Jane Schmidt 25
Operations
Bill Jones 35

etc.

[yellowdragon]

Now regarding the reference field in Table_ISA, you said the following:



Does each record only contain 1 specific reference name?

Like this: john|smith| windows|

Or can it be this: john|smith|window,operations...|


If it is the first option, then this is how Table_ISA should be structured
etc.

Each record will only have 1 specific name. Thank you for the time you spent on this. I appriciate the response, and I'm in the process of trying your recommendation out. I'll get back on this with the results, and more questions for sure!

[yellowdragon]

Here's what's up. I created the following:
tblDepartment tblOrg tblISA
pkID ------> fkDeptID pkISAID
txtDepartment pkORGid ---> fkOrigID
txtOrgName lname
fname
etc.

I'm a little confused about what type of join to use, so I tried, the one labeld "1", and the one labeled "2". The tblISA has all the imported data. I also imported the the data to the tblOrg. I'm not sure how the relationship works here, but I think that's where I'm getting into trouble.

When I create the query, I select the txt fields, and then I select the lname and fname, to keep things simple. I get the following error when creating a query:
The wizard is unable to open your query in Datasheet view, possibly because another user has a source table open in exclusive mode. Your query will be opened in Design view.

It opens in design view, and can't be saved. I made all foriegn keys as number, and the primary keys as auto number.

[jzwp11]

All of the joins should be the type 1. I've attached an example database.

Regarding the error you received. Are you using linked tables? Can anyone access the database at the same time as you? Did you have any tables or forms open at the time?

[yellowdragon]

I working with the db you provided. The problem I think I'm having now is with the fkDeptID in the tblOrg. I'm assuming this is the field that I'm to use to put the department id? The deparment is a name and this field is a number? Ideas?

[jzwp11]

Relational databases like Access are more efficient with numbers that is why you typically store the numeric key field value in the related table and not the text value. All interaction with data should be through forms. So you would create a form based on tblOrg and on that form you would use a combo box based on tblOrg to populate the fkDeptID field. The bound field of the combo box should be pkDeptID. If attached the DB with a simple form added to illustrate.

[yellowdragon]

I attached a file with what the data will look like that I'm importing. The data will be imported to TableISA and then I need a report that will print a name that will be summarized from the Reference field. Software Services Branch, Infrastructure Servcies Branch, Software Services Branch will all be under the heading IT. So the report will print out the name IT for the reference field, rahter than the orignial names under reference. The Human Resources Branch, and PM will be under HQ, and the PS-* will be under PS.

I think that gets down to the nitty gritty. I'm sorry I wasn't clearer on my initial question!

[jzwp11]

The data in the file you attached is not normalized, so I would not import into any table that makes up your normalized structure. I would recommend importing it into a temporary table and then moving the appropriate information (via append queries) into your normalized structure and then using queries and reports built from the normalized structure.

From the file, it looks like the percent complete is calculated based on the number of columns in the range N-AT that show complete/(complete+incomplete)*100 and ignoring those marked unassigned. Do you plan on doing anything with the columns in the range from N-AT in your database or do you only need columns A through M?

[yellowdragon]

right now I have no plan to use n-at, but that doesn't mean I won't need it in the future.

[jzwp11]

If that is the case, you should probably structure the tables now for it. Each of the items in those columns should be records in a table and then the status of each item for each employee needs to be captured in another related table as records. It looks like those items are training courses perhaps? Techncially speaking if you have that information in the database, you would calculate the % complete on the fly rather than storing that value in a table.

In the file, is the reference name the same as the organization name that we have been talking about? (I'm guessing it is)

Does a person have to go through the process/training more than once?

Based on my best guess of your data, I have imported your data into the example database I posted previously, created a few append queries to move the data into the normalized structure & created a sample report. I set up the tables for the "training" info, but I did not want to migrate the data from the import table until I understood more of what you plan to do with it.

[yellowdragon]

Yes, the items are training courses, an no caluculations are necessary. The calculations are already done prior to importing. And yes reference is simliar to organization. There may be multiple references that belong to the same organization! And the training will be done yearly, so for now there is no need to be concerned with students repeating the courses. The data can be imported each year. So the report should have a field titled Organization. An organization might be IT, with all the records under it from Network and Operations, without reference in the report to Network and Operations.

[jzwp11]

In the database I provided on Friday, you only need to remove the reference name (I called it organization) level in the report. The IT, HQ etc. are considered departments in my example. Sorry for the difference in table naming.

[yellowdragon]

I don't understand where it is that I need to remove the reference name?

[jzwp11]

Open the report in design view. Make sure the Group & Sort Icon on the tool bar/ribbon is highlighted. Go to the bottom pane of the report (titled: Group, Sort, and Total). You should see two groups: Group on txtDepartment and Group on txtOrgName. Highlight the txtOrgName group. When you do that an X will appear at the far right. Click on the X to delete the txtOrgName group. I've attached a screen shot.