related tables, multi-column combo boxes, form says one thing, table says another

[Instructor Support]

First let me start by saying that I am not a programmer. I am a power user. It is only with the help of kind geniuses like those in this forum that I can accomplish anything.

Now, I have inherited a very large database with lots of interlinked tables. The part I am specifically struggling with right now is somewhat of a staff box.

tblStaff has StaffID & StaffName

tblJobs has (among a million other things) AssignedStaff which is a combobox with a lookup source of;
SELECT [tblStaff].[StaffID] FROM tblStaff ORDER BY [tblStaff].[StaffID];

formJobForm has a combobox called Combo312 who's controlsource is AssignedStaff and the column is generated by
SELECT [tblStaff].[StaffID], [tblStaff].[StaffName] FROM tblStaff ORDER BY [StaffName];



When I select a staff member in my form, it shows the ID correctly (for example: 04)
When I check my tblJobs to make sure it's saving correctly is says something different (for example: 06)

Going back to the form it still says the correct number. I don't understand why they're different! Please help.

As a last note, I am using a Japanese version of MS Access, so I can't actually read the properties boxes. I usually go online to find an English version and match the order to what I have to fill these things out. If you want to know where something is- please provide a screenshot showing where I can find that detail. I will do my best!

[orange]

I'm not understanding this line
tblJobs has (among a million other things) AssignedStaff which is a combobox with a lookup source of;
SELECT [tblStaff].[StaffID] FROM tblStaff ORDER BY [tblStaff].[StaffID];

Are you saying this is a lookup field in a Table?

Can you post a jpg of you Tables and Relationships?

[Instructor Support]

A lot of it is company proprietary information, so it would have to be mostly blacked out. Would that still be helpful?

I can show you the part you're asking about.



I can read some of this stuff, so if it helps to have some translations, I can try. For example this is the "SQL Statement: Query Builder" on top of the "Table" window showing the "text box" properties' "Lookup" tap where the somethingsomething "Source" is the Select query.

[Instructor Support]

I didn't have to black out as much as I thought I would.

Relationships:

(Actually, I do know what one of those tables on the right is, but not the other one. Either way they're not part of this topic.)

Tables:


Is this what you wanted?

[orange]

Post #4 is saying invalid attachment for the links.

[Instructor Support]

That's weird. They were working before. I posted them again.

[Instructor Support]

Any ideas?

[orange]

You say you have inherited this database - can you elaborate on this aspect of your situation?. What documentation do you have? What transfer type info did you get/receive from the previous person/team?
I can see that some data could be sensitive, but I really don't think table names are so confidential that they need to be blacked out.
You have to go back and get the business facts of the business, and get a clear statement of the Purpose of the Database. Then next step in my view is to make sure the data model (tables and relationships) support those business facts. Get some meaningful test data (non confidential with good and bad values) so that you can test various parts - procedures,queries,etc and be certain of functionality before moving to production.

I certainly do not understand Japanese. And I don't have any "feel" for what this application is about. It seems to me that you do NOT have much knowledge of the database nor application. Many of us here are willing to help, but, at best, we are guessing since we, and you, do not have intimate working understanding of the database.

Please tell us if we have misunderstood, and lay out a plan of approach, so we can be informed.

[Instructor Support]

I greatly appreciate your interest in helping to clean up the database as a whole, but I'm afraid that particular project is a bit over my head for the time being. This database has been in use for many years. I've been told I'm the 4th or 5th person to inherit it. I am not a database programmer really. It is my job to handle the data that this database contains, and I'd like to try to make some small efforts at improving the database while I'm at it.

As indicated in the file names, this database contains pretty much everything my company handles, everything we know about our teacher staff, client company profiles, contract job details and so forth.

I've cleaned up the system quite a bit since I started a year ago, but the person who was responsible for this data prior to me didn't really do anything with the database. So he was not able to tell me much of anything except how to fill out the forms for the jobs. I know a little bit about programming, enough to scrape by on most of what I've needed to do until now.

Based on my current skills, I've been trying to clean up the database in pieces as I learn more about how each piece is used in my company. I am not the best person for this particular job, but I'm all we've got here. So I'm doing my best.

The current flaw I am trying to resolve is that each job is supposed to be associated to a sales person & teacher, which they are, but they are associated by name instead of ID number. When the names change the data becomes orphaned. I'd like to change the database to tracking people by their ID numbers (while still displaying names, so that the interfase is user-friendly. Hense the multi-columns in the form) so that this orphaning thing doesn't happen anymore. I figured since we have a lot less sales people than teachers, the sales people would be a good place to start. That's where I am now- trying to switch it from storing "Smith" in the tables to storing "02" and then in my queries pulling the name from my list of staff or something.

Is that enough information to get started?

Thank you again for your patience and willingness to help.

[orange]

Do you have a test/development database? You should NOT be making untested changes to a Production database.
Can you provide a copy of your database- a dumbed down (no confidential/personal data)?

[Instructor Support]

In fiddling with it, I seem to have found a fix. Although I'll admit I don't entirely understand why.

In the job table I have the staff field combobox with the lookup query pulling the list of staf from the staff table. I had selected the bound field to 0 as I had always read that the first column is 0. If I set the bound column to 1, suddenly it starts storing the data correctly. I guess the 0 base is only for forms or only for multi-column comboboxes? Either way, it looks like I'm able to progress from here.

Thanks again for your time.

[orange]

In the job table I have the staff field combobox with the lookup query pulling the list of staf from the staff table.

There are free tutorials re combo boxes at
http://www.techonthenet.com/access/comboboxes/