Lookup and Text calculation in Access vs Excel

[New2Access]

I'm sure this is simple...

I can get this to work in Excel, but I need help getting it to work in Access:

I am using a form to populate a table. I have fields for first name and last name. I would like to calculate the full name (last, first) which I can do in Excel. I can get it to work on the FORM in Access, but it won't put it in the table.

Also, in another table I have a department number and department name. I would like to have it enter the department name into the table when I enter the department number into the form.

Hope this makes sense.

Thanks for your help!

[nico5038]

I'm sure this is simple...

I can get this to work in Excel, but I need help getting it to work in Access:

I am using a form to populate a table. I have fields for first name and last name. I would like to calculate the full name (last, first) which I can do in Excel. I can get it to work on the FORM in Access, but it won't put it in the table.

Also, in another table I have a department number and department name. I would like to have it enter the department name into the table when I enter the department number into the form.

Hope this makes sense.

Thanks for your help!

I a normalized database we keep those names separate, just in a query (or on a form in an unbound c.q. new field) we use an algoritm like:
=[LastName] & ", " & [FirstName]
or
select [LastName] & ", " & [FirstName] as FullName from tblYours.

department number and department name are normally stored in a separate tblDepartment.
Now in the other table just store the department number and make the field in the tabledefinition a "Lookup" field. (Check the Lookup tab).

Getting the idea ?

Nic;o)

[NeoPa]

Check out this tutorial (Normalisation and Table structures) for a fuller explanation of the principles. I would recommend it to anyone new to databases.

[New2Access]

I a normalized database we keep those names separate, just in a query (or on a form in an unbound c.q. new field) we use an algoritm like:
=[LastName] & ", " & [FirstName]
or
select [LastName] & ", " & [FirstName] as FullName from tblYours.

department number and department name are normally stored in a separate tblDepartment.
Now in the other table just store the department number and make the field in the tabledefinition a "Lookup" field. (Check the Lookup tab).

Getting the idea ?

Nic;o)

I still can't get the FullName to calculate. I'm not sure why.

On the department number and name issue, now I can get a drop down list of the department names, but I still have to select the correct one from the list. I would really like it to put the correct department name in the field when I enter the department number.

[NeoPa]

:confused:
Doesn't this mean that you really want a TextBox with the value in?
If so, you simply need to include the extra table in your form's Record Source query.

[New2Access]

:confused:
Doesn't this mean that you really want a TextBox with the value in?
If so, you simply need to include the extra table in your form's Record Source query.

I want the Full Name to show up in the Table. It works fine in the form, but I need it to show the Full Name in the table, without having to type it in.

Same with the Department Name. I need it to look up the department name when I enter the department number. I do have a separate table that just has the department numbers and names.

[NeoPa]

I want the Full Name to show up in the Table. It works fine in the form, but I need it to show the Full Name in the table, without having to type it in.

As the point of not storing the data in the table has now been made three times and you have chosen not to respond to any of them, I can only assume that you accept the point. Hence I'm surprised that you request the same again :confused: It's almost as though you're ignoring everything anyone says.

Same with the Department Name. I need it to look up the department name when I enter the department number. I do have a separate table that just has the department numbers and names.

Does this mean it's the same as in that you want to update the data in the table? If not and you just need to know how this should be done then I answered that in my previous post. If that's not clear enough please ask for a clarification (explaining what you don't understand - so I know what level to pitch the answer) rather than ignoring my response and just resubmitting the question which simply takes us back to the start. If your question had been clear enough at the start we wouldn't be here now.

[New2Access]

As the point of not storing the data in the table has now been made three times and you have chosen not to respond to any of them, I can only assume that you accept the point. Hence I'm surprised that you request the same again :confused: It's almost as though you're ignoring everything anyone says.

Does this mean it's the same as in that you want to update the data in the table? If not and you just need to know how this should be done then I answered that in my previous post. If that's not clear enough please ask for a clarification (explaining what you don't understand - so I know what level to pitch the answer) rather than ignoring my response and just resubmitting the question which simply takes us back to the start. If your question had been clear enough at the start we wouldn't be here now.

Sorry, I don't understand. I'm not ignoring you. Just trying to understand what you are telling me. I am new at this, as you can tell from my signin name. If you are telling me that I can't have a lookup work in Access like it does in Excel - OK. I thought my original question was clear, but apparently not.

Is there another way to have a table (or whatever) that will show everything in the table PLUS the calculated full name and looked up department name? We need to have one place where we can look at all the records and all the information for each person.

I tried the suggestions you made and things didn't work. If this is bothering you too much, I apologize. Maybe I should just stick with Excel. The only bad thing is that Excel won't do the reports I want.

Thanks for your suggestions.

[NeoPa]

Sorry, I don't understand. I'm not ignoring you. Just trying to understand what you are telling me. I am new at this, as you can tell from my signin name. If you are telling me that I can't have a lookup work in Access like it does in Excel - OK. I thought my original question was clear, but apparently not.

This isn't about Access, this is about communication. I am trying to help you from the other side of a web page. This is hard enough at the best of times and depends on both parties expressing themselves clearly and responding to the other in a logical fashion. If I respond to a question and you respond by asking another question and or otherwise avoiding any response to my last, then I have to try to maintain multiple levels of converstion in my understanding simultaneously. Trust me, I'm not stupid. If I ask for information or make a suggestion there's normally a good reason behind it.
I can appreciate that you will not always know what I'm on about, but in that case I need you to explain that to me rather than trying a different route around the problem. Believe me when I say, from experience, that is very rarely a productive way to proceed, and will only confuse and complicate the task from the expert's point of view.
I understand this is confusing for you. Techniques that would normally work well to resolve a problem can be quite unhelpful when working together using a very restrictive medium (as essentially we are) such as a web forum.

Is there another way to have a table (or whatever) that will show everything in the table PLUS the calculated full name and looked up department name? We need to have one place where we can look at all the records and all the information for each person.

Almost certainly, but without more information from you I can't tell you what is the appropriate way to go at this time.

I tried the suggestions you made and things didn't work. If this is bothering you too much, I apologize. Maybe I should just stick with Excel. The only bad thing is that Excel won't do the reports I want.

What bothers me about this is that you don't communicate this information to me. How can I possibly help you if I don't know what's working for you and what's not. I can't see your work (database/spreadsheet etc), I only have clues that I use to put together a picture from which I must work.

I'm sure we can progress on this but you will need to respond to all my questions or suggestions so that I know how best to proceed. Even if it's simply to say 'What the flipping heck are you on about :confused:?'

[New2Access]

This isn't about Access, this is about communication. I am trying to help you from the other side of a web page. This is hard enough at the best of times and depends on both parties expressing themselves clearly and responding to the other in a logical fashion. If I respond to a question and you respond by asking another question and or otherwise avoiding any response to my last, then I have to try to maintain multiple levels of converstion in my understanding simultaneously. Trust me, I'm not stupid. If I ask for information or make a suggestion there's normally a good reason behind it.
I can appreciate that you will not always know what I'm on about, but in that case I need you to explain that to me rather than trying a different route around the problem. Believe me when I say, from experience, that is very rarely a productive way to proceed, and will only confuse and complicate the task from the expert's point of view.
I understand this is confusing for you. Techniques that would normally work well to resolve a problem can be quite unhelpful when working together using a very restrictive medium (as essentially we are) such as a web forum.

Almost certainly, but without more information from you I can't tell you what is the appropriate way to go at this time.

What bothers me about this is that you don't communicate this information to me. How can I possibly help you if I don't know what's working for you and what's not. I can't see your work (database/spreadsheet etc), I only have clues that I use to put together a picture from which I must work.

I'm sure we can progress on this but you will need to respond to all my questions or suggestions so that I know how best to proceed. Even if it's simply to say 'What the flipping heck are you on about :confused:?'

LOL - OK, let's try this again! I was trying this stuff and was getting frustrated when it didn't work. I REALLY appreciate your offer of assistance!

I'll start by explaining what I have so far...

I have one table (Department_List) that has department number and department name. I have a main table (Employee_List) that has employee name, first name, last name, hire date, department number, department name (these are the main fields). I had the data in Excel, which allowed me to calculate the full name (so I didn't have to type it again) and lookup the department name when I entered the department number (again, trying to save some typing). I imported all this data into Access, and built some reports from it. Everything was going along nicely until we hired more people! So I set up a form to use for the data entry to make it easier (HA!). So we just enter the employee ID number, first and last name, hire date and department number. So this data is being added to the table but I discovered that I would have to type in the full name into the table along with the department name. I would like to automate this process.

After your suggestion, in the table I went to the Full Name field and on the properties at the bottom set Row Source Type to Table/Query and entered [LAST_NAME]&","&[FIRST_NAME] as the Row Source. That didn't work. So I guess that's the first place I got messed up. I've actually renamed the database "MESSED UP FILE" <g> until I get this figured out.

BTW, it also takes me awhile to get back online because I have a full time job and this is something I am doing "after hours"

[NeoPa]

LOL - OK, let's try this again! I was trying this stuff and was getting frustrated when it didn't work. I REALLY appreciate your offer of assistance!

Not a problem. You're well and truly on my radar now :)

I'll start by explaining what I have so far...

Good idea.

I have one table (Department_List) that has department number and department name. I have a main table (Employee_List) that has employee name, first name, last name, hire date, department number, department name (these are the main fields). I had the data in Excel, which allowed me to calculate the full name (so I didn't have to type it again) and lookup the department name when I entered the department number (again, trying to save some typing). I imported all this data into Access, and built some reports from it. Everything was going along nicely until we hired more people! So I set up a form to use for the data entry to make it easier (HA!). So we just enter the employee ID number, first and last name, hire date and department number. So this data is being added to the table but I discovered that I would have to type in the full name into the table along with the department name. I would like to automate this process.

I commented on the advisability of storing the data twice (FirstName; LastName; FullName) earlier in the thread but I'll repeat it here as that may be easier to follow.

Check out this tutorial (Normalisation and Table structures) for a fuller explanation of the principles. I would recommend it to anyone new to databases.

Basically it's not a good idea. It is possible to do though. If you tell me categorically that that's the way you want to go then I'll help you to implement it. Be warned though, it's not recommended as it may make future work on this more complicated and allows the potential for corrupted (unsynchronised) data.

After your suggestion, in the table I went to the Full Name field and on the properties at the bottom set Row Source Type to Table/Query and entered [LAST_NAME]&","&[FIRST_NAME] as the Row Source. That didn't work. So I guess that's the first place I got messed up. I've actually renamed the database "MESSED UP FILE" <g> until I get this figured out.

That can't work.
For anyone trying to read this they have first set the Display Control within the Lookup section to ComboBox or ListBox to enable the Row Source Type property.
My suggestion was probably referring to the Record Source of the form that you would be using to do the data entry & modification.

BTW, it also takes me awhile to get back online because I have a full time job and this is something I am doing "after hours"

I'm in exactly the same boat myself, so I do understand.
There is never any pressure to respond quickly on the part of the OP (you). It is appreciated if you let us know if you find your problem is resolved (when it is), and 'Thank you's never go amiss as they keep our experts interested and feeling valued. Having said that, most of us have plenty of threads to keep us going so if at any time you're unable to 'attend' for a while, that's just a short break for us on this side :)

[New2Access]

Not a problem. You're well and truly on my radar now :)

Good idea.

I commented on the advisability of storing the data twice (FirstName; LastName; FullName) earlier in the thread but I'll repeat it here as that may be easier to follow.Basically it's not a good idea. It is possible to do though. If you tell me categorically that that's the way you want to go then I'll help you to implement it. Be warned though, it's not recommended as it may make future work on this more complicated and allows the potential for corrupted (unsynchronised) data.

That can't work.
For anyone trying to read this they have first set the Display Control within the Lookup section to ComboBox or ListBox to enable the Row Source Type property.
My suggestion was probably referring to the Record Source of the form that you would be using to do the data entry & modification.

I'm in exactly the same boat myself, so I do understand.
There is never any pressure to respond quickly on the part of the OP (you). It is appreciated if you let us know if you find your problem is resolved (when it is), and 'Thank you's never go amiss as they keep our experts interested and feeling valued. Having said that, most of us have plenty of threads to keep us going so if at any time you're unable to 'attend' for a while, that's just a short break for us on this side :)

OK - what do you think I can use that "looks" like a table but has all the information in it - including the dreaded full name and department name :) - that isn't a report. We want to be able to look at the data, sort by different fields - like department, full name, date of hire, etc, but not necessarily print it out in a report format. My reports are actually working OK, so I don't think I want to mess with them.

I'm so pleased - I just figured out how to do the smiley.

[NeoPa]

OK - what do you think I can use that "looks" like a table but has all the information in it - including the dreaded full name and department name :) - that isn't a report. We want to be able to look at the data, sort by different fields - like department, full name, date of hire, etc, but not necessarily print it out in a report format. My reports are actually working OK, so I don't think I want to mess with them.

A query seems to be the perfect answer to that question.
If you want it in an updatable form, however, you can use the table, but have some unbound controls that simply display the calculated data.

I'm so pleased - I just figured out how to do the smiley.

To discover how a non-text effect is produced, simply find one somewhere, click on Reply to the post, look in the resultant prepared message and you should find how it's done. That's how I discovered how to do links with the destination name text in them rather than just the URL.

[NeoPa]

If you want some help knocking together a query that fits your requirements, just let us know, along with the relevant info that we'll need to be able to come up with an answer.
Remember though, we talk about queries in here using SQL. That's simply because it's easiest for transferring a query specification. In Access, you'd normally design the query in Design view and have the table MetaData avilable to you automatically.

[New2Access]

If you want some help knocking together a query that fits your requirements, just let us know, along with the relevant info that we'll need to be able to come up with an answer.
Remember though, we talk about queries in here using SQL. That's simply because it's easiest for transferring a query specification. In Access, you'd normally design the query in Design view and have the table MetaData avilable to you automatically.

Dear Access Guru,

Is there a cute little symbol that I can use to portray myself as an idiot?

The query idea looks like it will work. The first time I tried it, I didn't get ANY data, but now it is showing all my records, just as it should. I will now add it to the actual database, not my "messed up" one. The query is just a way to view the records, not a way to make changes to the data, right?

I really appreciate your help, but most of all your patience. This is a great resource!

[NeoPa]

Is there a cute little symbol that I can use to portray myself as an idiot?

Your member status (Currently Newbie - under your username by a post) gives a bit of a clue. Obviously we get new members with that status who are just as much gurus as the other experts in here so it's not 100% reliable :D

The query idea looks like it will work. The first time I tried it, I didn't get ANY data, but now it is showing all my records, just as it should. I will now add it to the actual database, not my "messed up" one. The query is just a way to view the records, not a way to make changes to the data, right?

Right. But also wrong.
Essentially right in that a SELECT query (the type we want to use here) fits that description perfectly.
Action queries are also possible though. You can create a new table, add records to an existing table and also change the data held in a table, all with different types of Action query.
As I say, essentially you're right.