Possible to link feilds thru multiple tables?

[Talayoe]

Hello. What I have is a table ('tblJobLog') with our main listing in it where we add customer job information as it comes in. The 'Customer Name' is a dropdown list that links to our table 'tblCustomers' so that we can ONLY select a customer name that we deal with. The alternative is to add a customer or list them as COD. Now this is where I would like to target, the COD's. We still like to keep a record of who the COD is and we have some common customers that are listed as COD only.... So when we go to enter the customer name we have to put COD and in the description we are suppose to input the customer name (We will says RandyShop <--- me, for now). What I would like to have done, if possible, is to see when we select 'RandysShop' from the dropdown, it checks to see if the COD Status (in the 'tblCustomers' table) is set to YES (its a check box, or YES/NO field). If it is set to YES (or selected) then the change the name shown in my main 'tblJobLog' to look something like COD: RandysShop. The reason I would like, if possible, to do it this way, is because some customer go on and off COD regularly as the mess around with payment. So instead of creating a new customer that is listed as COD: RandysShop to have access do the leg work based on a simple YES/NO field (COD Status). Thanks! (btw, sure wish I could use the enter key to not make this one giant paragraph!! Just saying)

[Talayoe]

Hey, I went into design view for the tblJobLog I mention above and added this criteria for the Customer name field thru the expression builder. It didn't work, but it kind of gets the point across (I think) of what I am wanting to do...... IIf([tblCustomers]![Status_COD]=True,"COD:",[tblCustomers]![CUSTOMER])

[jzwp11]

It sounds like you are entering data directly into your tblJobLog, if that is the case, I would strongly recommend not doing that but rather, use a form. You have much more capability to change the way things are displayed when using a form. Second, it also sounds like you are using a dropdown in that table for the customer name. Even though Access has this capability, it can cause some issues down the road. This site details how these table-level lookup fields can cause problems. Of course, removing the dropdown from your table will require a different way of handling the data, so a change in your table structure is necessary. How this is typically handled is using a foreign key field in the related table (tblJobLog) that references the particular customer record in the customer table. This creates a relationship between the two tables.

I thought that it might be a good idea to illustrate how the customer and job table relationship is established, so I have created the attached sample database for you. If you look at the fields in the two tables and then the Relationship Diagram, you will see how I have related the two tables (a customer can have many jobs--so a one-to-many relationship is necessary)


I also created the form frmJobLog to illustrate what you can do in a form to achieve displaying the COD status of the customer when creating new jobs or reviewing existing jobs.

There is some code associated with the on current event of the form as well as the after update event of the customer combo box in the form. The code shows the COD warning if the customer record has a check mark in the COD field. The COD status is brought in via the combo box (The COD column of the combo box is hidden, this is done by setting the column widths property to 0; if you want your uses to see the COD status when using the combo box, just change the column widths property to a number greater than zero)

[Talayoe]

Hello, Thanks for that file. I will dig around inside of it and maybe have a few questions to ask.

You are correct that we (some) are directly inputting data into to the tblJobLog. The reason I do this is because...well, that is what I was shown and told 'advanced' users do. We have a form, similar to the one in your example which can be used, and is for the less experienced users (again, what I was told) and gives them less likelihood to botch anything royally. So, even tho I may be in the wrong for the time being in that I use the table, I will say that the example is interesting. I will incorporate the popup text into our form for sure.

Now, I suppose at the end of the day the sole purpose of my endeavor is this; We have a semi weekly job planning meeting. The list is printed from a report. The report VERY closely resembles the qryJobLog (which is select columns and organizational structures based on tblJobLog). This qry obviously pulls its info from its parent, tblJobLog, and we have no room to add another comment area where I could have stated 'This customer is COD' (or similar), as such we break It down to 'Customer' equaling COD in the fashion mentioned previously.

Something that did interest me from the link you provided was that it mentions is security implemented on tables....I know nothing about this and, even tho this is kind of off topic for this thread, I would be very interested. We would like to give our foreman access to the data base, but only the frmJobLog and nothing else. Is this possible?

[jzwp11]

Now, I suppose at the end of the day the sole purpose of my endeavor is this; We have a semi weekly job planning meeting. The list is printed from a report. The report VERY closely resembles the qryJobLog (which is select columns and organizational structures based on tblJobLog). This qry obviously pulls its info from its parent, tblJobLog, and we have no room to add another comment area where I could have stated 'This customer is COD' (or similar), as such we break It down to 'Customer' equaling COD in the fashion mentioned previously.

You did not mention that you were trying to do this in a Report; it sounded like you were trying to do it only at the table level. Since the report is based on a query, you have some flexibility with the query where you can replace the customer field with a calculated field as follows:



SELECT IF(CODfield=0, customerfieldname, "COD: " & customerfieldname) as ModifiedCustomerName, other fields
FROM ...

I do not know your actual field names, so you will have to replace the generic field names above with your actual field names.

Something that did interest me from the link you provided was that it mentions is security implemented on tables....I know nothing about this and, even tho this is kind of off topic for this thread, I would be very interested. We would like to give our foreman access to the data base, but only the frmJobLog and nothing else. Is this possible?

There are many ways to setup security around a database in Access, but it is typically across the whole database not just a particular table. What I typically do is have a table that holds the users and another table that has the various form names. I then set up a related table that joins the users to the forms that they are allowed to use. You would typically have a login form. Once the user logs in, I would have a main menu form that has buttons for the forms the user has permission to use. (the button name would also have to be stored in the table with the form names)

[Talayoe]

Hi,

Sorry for the delayed response. It takes me some time to get this (and thru it!), unfortunately.

The initial premise is to do this on the table level because that is where 2/3 of the people work from. The previous way works splendidly for rest (with the form).

With this way you describe, I have to do this at the table level, do I not? And since it is reading from 'tblCustomers' to get the customer name and COD status, where do I fit that into the expression as you listed above?

Field names are as follows (just to make my life clear)

tblCustmomer:
CUSTOMER
STATUS_COD
tblJobLog:
CUSTOMER (text)
CUSTOMER_NAME (calculated <--- just created for your example)


tks for the help!

[jzwp11]

tblCustmomer:
CUSTOMER
STATUS_COD
tblJobLog:
CUSTOMER (text)
CUSTOMER_NAME (calculated <--- just created for your example)

The customer name should only be in the customer table. You would just reference the customer using the key value in the job table which I assume is the field called customer (you can use a query to display the customer name). Additonally, although key fields can be text, numeric fields are generally preferred. That is why I use autonumber primary key fields and long number foreign key fields. For now you can leave it as text so as to not make you redo everything.

Also, the general rule is that users should not access the tables; all interaction with data should be through forms. You can use a form that looks like a table if you want (datasheet view)

[Talayoe]

Good Morning JZ,

Apparently my 'works splendidly' was a bit premature. There is a difference between your form and mine and that is that mine is scrollable. As such, when a customer is selected where the status is COD, I get the writing...but so does every other customer in the scrollable list. See the pix below. I created two new customers for this example;
"Randys Shop (cod)", in the tblCustomers STATUS_COD = Yes
"Randys Shop(not cod)", in the tblCustomers STATUS_COD = NO

First pic, I have click on any given box within the current customer of Randys Shop (cod) and I get my note even on a non COD (the second listing).

Well...for some reason it wont let me upload any pix. Hit the upload button after selecting and nothing happens. Anyways, same thing applies when I click on the Randys Shop (not cod) but this time it wipes ALL the COD comments from every record listing in the form.

Any ideas how to make it stick?

tks muchly!

[jzwp11]

What you describe is typically seen when using a continuous forms since it is the current record that governs what is displayed. As far as I know, there is no good way to change it. So, we might have to use a different approach. Let me think about it; it may be tomorrow before I can come up with something.

[Talayoe]

That is exactly what I have is a continuous form. And tomorrow is fine. We have lived with our dbase like this for this long, and few days wont kill anything.

Thanks for the help!

[jzwp11]

Well, the simpler way might be to just place a control on the form and reference the COD column from the combo box. I have done that in the attached. I used an IIF() function to evaluate whether the COD was checked (-1) or not. If checked, ON COD is displayed; if not, no value is displayed.

[Talayoe]

Hi Jz,

That was quick and simple to implement, thank you. I appreciate all the help!

Now I was curious if, going back to the original post, you can think of a way to have something similar incorporated into my main tblJobLog?

[jzwp11]

Since the COD status pertains to the customer, it belongs in that table and only that table. If you want to see the COD status and the job information then the only ways are with a form or via a query. Repeating the COD information directly in the job table would violate normalization rules.