Selecting specific table from listbox

[stormchaser]

Hello all, my first post here i got to say you forum helped me a lot during my online search for help!
But now i have a problem for which i cant find a solution..
Here is the problem:
I have to make a multi-user manage database.
All the usernames are sorted in one table and exported to listbox in Form.
For each user there is a separate table with 3 columns where i must fill information from listboxes.
The listbox (there are 4 listboxes, one for users select and 3 for different parameters) are located in MainForm where you select the information.
My question is how can I select a specific username(table) from the listbox and fill the information from the other listboxes to the selected one? I am completely new to the whole Access programing so i am not even sure if that is possible but I am sure you guys know!

if that helps the usernames table is named - Clients
each table for separate user is named client1, client2, client3 etc.
the form where you choose what to select is named Fill form.
The listbox for users is named - List71
And the listboxes for parameters are named - List75, List77, List79

Also there are 3 textboxes with calculated fields that i also need to insert in the specific user table.

Sorry again for the long post and all the questions but as I say i am really new at the whole Access programming.

P.S. also sorry if my english is bad

Best regards!

 

[Frothingslosh]

Your problem isn't that you need to select a table - although that CAN be done via VBA - your issue is that your database is set up wrong. There is virtually never a reason that you should have multiple tables with the same data in them.

In the situation you laid out here, instead of having a different three column table for each user, you should have a single four column table with the first column being UserID, and the other three being the columns in those duplicate tables. That way, you can simply filter out everything save the data for the user in question.

Also, it's a REALLY good idea to actually name your form controls. 'lstUserID', 'lstClientName', 'lstActivityDate', and the like make your database far easier to modify than trying to remember what 'List71', 'Text139', and 'Combo1550' all are.

 

[stormchaser]

Hello, thank you for the quick replay! I know its a good idea to name the objects I will do that first thing tomorrow.
Can you help me choose a better structure for the following type of database:

Client1
Field1       Field2                          Field3                                         Field4
Jan           different project            time worked on that project               summary
Feb
March
...etc

that is basically how my table should look for each user. If there is any better idea I am always open to learn something useful

P.S. sorry if my questions are dumb but i have never build a database before so i am kind a like a first grader

Best regards!

 

[Frothingslosh]

Your suggested table looks like you just save the amount of time worked on each project for each client each month, with a separate entry for each user. Is that correct?

Assuming yes, you'd do it something like this:

Table: tblWorklog
Fields:

• UserID or UserName - probably a text field
• ClientID - Number (long integer), link to ClientID in tblClients
• Month - Personally, I always use MonthID and link to a month table.
• ProjectID - Number (long integer), link to ProjectID in tblProjects
• TimeWorked - Number (single)
• Notes - Memo

(Note - You'd probably want an autonumber primary key, something like WorkLogID. You'd ALSO want an index containing UserID, Client ID, Month, and ProjectID, with it set to 'Required - No Duplicates'. The WorkLogID is there just to enable easier searching elsewhere if necessary.

Table: tblProjects
Fields:

• ProjectID - AutoNumber
• ProjectName - Text
• (Whatever else you need for each project)

Table: tblClients
Fields:

• ClientID - AutoNumber
• ClientName - Text
• (Assorted other client data)

 

[stormchaser]

Okay so I decided to make a big change. I will try to explain it as good as I can:
For now I just need to keep track of employee productivity for this purpose i need to have the following:

Table for employees, table for project type (We got 4 project types 2 of them have sub type), table for months, I guess... on the main form I have 4 textboxes (prTypeRes1,2,3,4) that calculates the results done by the employee based on entered information from two other textboxes. I also got two listboxes (lstEmployees, lstMonths) to select employee and month. My question is how can I add the information from the main Form to some sort of main table that consist the following information: Employee, project type (and if it has, subtype), months and result for every month selected from the listbox.. Pretty much every employee works on only one project at month but some of them work on two projects that is also a thing to consider. At the end I need to generate a report with chart where lets say I need to keep track for projectType1 and Employee2 and see his productivity during selected months. Is that a hard thing to do for a newbie like me? Thanks in advance

p.s. maybe I can add a table for each project type with Employees and monts as fields and columns?

Best regards!

 

[Frothingslosh]

Okay, first thing you really need to look into is database normalization. Hopefully one of the other members here can provide you a good link - I don't have one handy, and the wikipedia article isn't very newbie-friendly. The basic rules, though, are that everything in a table should be directly related to the subject of the table, and repeating or sequential fields should instead be split off into a separate table and linked back in via relationships.

The big thing here is that you do NOT want a separate column for each month - you include month as a field, instead. The rule with relational databases is that you fill down, not across. Your query you mentioned at the end, for example, is easy to build when you have a single month field (you just limit the search to, say, months 3-6), but a royal pain to limit to a few selected months if each is a different field.

Overall, I don't think your database will be too hard for a newbie, but you do need to do it right. Unfortunately, today and tomorrow I will be really busy at work and unable to help during the day. Hopefully one of the other folks here can step in and take over; if not, I'll be back after work with ideas/suggestions.

 

[stormchaser]

Okay, maybe I don't need a column for each month but how can I compare the productivity for each month then?
Do you think it is a good idea to add 4 tables for the project types and fill them?
Basically i need to fill the value from 4 textboxes into each month via Action button.
I want the final look to be something like this:

Employee.......... January Feb..... March April etc..
Employee name..... 58 hrs. 28 hrs.. etc..

We got 30+ employees so the list will be a long one
That information will be filled through textboxes and i need the listboxes to select Month and Employee. Thank you very much for your time. I will wait for you to finish your work. I appreciate the help!

Best regards!

 

[Frothingslosh]

Okay, sorry about the delay. Been swamped at work, and home has been just as hectic.

Anyway, rather than a column for each month, you would just put in a column called 'Month' and put the value in there. I'd STRONGLY suggest just using the numbers 1-12, as it's trivial to convert them back to month names via the MonthName function. (I'd also strongly recommend adding a 'Year' column as well for historical purposes.

Yes, the list will be a long one, but that's the whole point of using queries in Access! You can build a query called a crosstab query (there's even a wizard for it) that lets you break down the data by employee and month, just the way you want it to look.

That's how relational databases work - you store the data in tables that are each really about one subject, and use the relationships to tie everything together.

As to project types, don't do a different table for each one. Just make a project table with everything you need, and a different table with the information specific to each TYPE, such as the name, comments, billing changes, etc. Put a primary key (preferably autonumber) in the Type table (named something like TypeID or TypeID_PK), and in the Projects table, you put in a matching field (named either TypeID or TypeID_FK) of the same data type. If you did AutoNumber for TypeID, in the Prjoects table, you'd use Number/Long Integer. Then you go into the relationships screen, add both tables, and drag and drop the primary key on Types to the matching field on Projects. Turn on 'Enforce Referential Integrity' for certain. I generally also enable 'Cascade update related fields', but NOT 'Cascade delete'.

If you want, post a cleaned copy of your database (fake data, 10 lines tops in each table is plenty) here and I can take a look at it after work and make some suggested changes so you can better see what I'm talking about.

 

[stormchaser]

Okay right now I am trying to make a new structure and I will attach a copy when I am finished probably tomorrow I saw a lot of articles for database normalization and I think I got the idea so I will try to make a new design and then ask you for improvements (for that I thank you in advance! )

Best regards!

 

[Frothingslosh]

Okay, good luck!

As you don't have ten posts yet, if you do upload the database, you'll need to upload it as a zipped file or the site will reject it.

 

[stormchaser]

View attachment database.zip
Hi, so far i came up with the following design for the Employee, Country, ProjectLog and ProjectType tables. I will fill the information for the Employees, Countries and ProjectType and make a Form for filling the ProjectLog based on this tables. But right now the problem is that I cant come up with a design for the ProductivityLog table.. I need that table to consist the Employee number, the project type and name and also the hours that he has been working on that project for every month and then come up with report for that. If you can help me with the design of the ProductivityLog table that will be great! I think I can manage to make the Forms for filling the information but the table structure is a little bit confusing for me
And I just thought of another thing.. the zones are CEE (Central Eastern Europe) SWE (South west europe) etc... do you think that setting the zone for primary key is a good idea?

 

[Frothingslosh]

I'll check it out this weekend (can't download stuff at work).

So productivity log has to be employee ID, project type (name should be in the project type table, not the log table), and hours applied to that project ID. You'll also want month and year, as you said earlier you were tracking by that. Anyway, I'll check it out and see what I can suggest.

As to the zones, what table are they in? The primary key needs to be something that uniquely identifies each row in the table, and the only time you'd make zones a PK would be when the table is about those zones, with the rest of the fields being information directly related to them.

 

[Frothingslosh]

Okay, I've looked through your database, made some changes, put a ton of notes in the table comments areas, and created a project log table. I've also set up some relationships for you in the relationships tab that you should check out - they will keep everything linked and working together.

 

[stormchaser]

Hi, I looked at your database but there is one problem, I have made everything that is required right now but I need to optimize the ProductivityLog table, If its is not too annoying can you give me an email where i can send you the table with data and the only thing left to do (i think) is trying to figure out the query for graph.. I dont know how to make the relations with + that you made on your table I guess I will have to read some books about access but this thing came out as urgent and I really need to finish it this week

Best regards!

 

[Frothingslosh]

In access 2007 and 2010, go to the "database tools" tab, and one of the buttons at the top is "relationships". You can set relationships between tables there - that's the biggest strength of relational databases, so you should definitely get used to using it and how you can get related tables to work together. (Example: In an invoicing system, you would have invoices in one table, and invoice line items in a different one, relating it back to the invoices table via the invoice number.)

Instead of sending me a table with live data, just put in fake data and post it here again - that allows other people to help if they see something I missed. Seriously, 5-10 faked up entries should be plenty to make sure everything works. I'll take a look at it then and we can get you a working crosstab query.

Oh, one other thing - you should avoid putting spaces in object and field names. While Access DOES allow it, it can make other things more difficult later on.

 

[stormchaser]

View attachment databasedata.zip
Okay so here is pretty much everything that I want.. Sorry that I did not use your database but I got everything done before you sent it to me plus I think that I did not say clearly enough what needs to be done, so basically right now I got a working form for ProjectLog that works just fine for add/edit projects the problem is that in the ProductivityLog table I get a lot of repeating entries, I will need your help about that Also for the report generation I made a separate form, I am not even sure if that is possible but if I can generate a graph chart like in Report1 based on the combobox and the checkboxes (the report needs to be lets say for employee1 and the average points for every month, for example if he worked on 3 projects and he got 22,3; 22,1, 20,5 points I need to graph the average of that for the month..) that would be more than amazing!

Best regards!

p.s. If you dont feel like doing the stuff for the report (I guess there will be some need of programming) thats okay you helped me more than enough!

 

[Frothingslosh]

Us being on different continents doesn't help a whole lot, either. Hopefully someone else can jump in and help - if not, I'll take a look at it tonight, but it'll probably be around 12 hours from now.

 

[stormchaser]

Well 12 hours from now works perfectly for me it will be for the next working day thanks again!

 

[Frothingslosh]

The only real problem I see with the ProductivityLog table is that you're saving month names rather than month numbers. Then again, as you're using a combo box to input the month, you don't have to worry about misspellings - it's just a little more data usage than using numbers, which isn't a huge deal.

You're going to get a lot of similar entries - that's how access works. It's actually easier to pull the data you need with this layout than with months read across, trust me. As for the form, look into forms and subforms - that's how you display two related tables in a form.

Okay, I spent about ninety minutes on this after I got home from the hospital tonight. I've made some pretty serious changes to all the tables, and created a new Projects form using frmProjects and sfmProductivityLog. Note how I actually created a productivity log and inserted it into the projects form - that's a subform, and it makes data entry a LOT easier. Tab order is all messed up and I did nothing about aesthetics, but it should give you some ideas.

I also filled out the relationships in the database. To view them, go to the Database Tools tab, and click on the Relationships tab at the top.

If you stick with my design, my form will actually replace the projects and productivity log forms you already had. If you're still stuck on the report, I can look at it tomorrow after work. (Even if I could download at work, I can't help from there - we only have Access 2003 at work, as opposed to my copy of A2010 at home.)

Anyway, the modified database is attached. Let me know if anything is messed up, especially where employees are concerned.

 

[stormchaser]

Thank you very much! I think we got it right this time now the only thing left for me is to design the report but I think that will not be so hard... again thank you! If I need anything else I will ask you but I think you did just enough Hope you are all alright and have a nice day!

Best regards!