Combine 2 fields into one field

[spectraflame]

Is it possible to combine the information from 2 fields into a single field and store the result in a table?

I would like to be able to join the current date and the users name into a single field called "Last Accessed By".

Example:

Current Date field equals = 09/10/04
Users name field equals = Spectraflame
Last Accessed By field = 09/10/04 Spectraflame

Matthew

 

[Joe4]

Let's say that your table has the following field names:

dteCurrentDate
strUserName
strLastAccessedBy

Then to update the value in strLastAccessedBy to be a combination of the other two, field, simply create an Update Query by doing the following:

1. Create a new query from your table
2. Add strLastAccessedBy to the query
3. Change the query type to "Update"
4. In the "Update To" line of the strLastAccessedBy field, enter:
[dteCurrentDate] & " " & [strUserName]
5. Click on the Run button to run the query and update the field

Now that I told you how to do that, let me tell you why you may not want to do this at all. Usually updating fields in a table from other values in that same table is unnecessary and not recommended (it violates the 2nd Rule of Data Normalization).

It is usually recommended to calculate this field in a query, and use this calculated field in reports, forms, etc. It is not necessary/recommended to store in a table that which can be calculated in a query.

The big advantage to using this calculated query is that any changes to your dteCurrentDate and strUserName fields will automatically update/change your calculated field (it is dynamic). But if you choose the Update Query method, it will not update the strLastAccessedBy field unless you were to re-run your Update Query every time you added or changed a value in the other fields. This creates a lot more potential for Data Integrity problems.

 

[spectraflame]

My question relates to my previous post concerning service order number and combining the Department name, current year, and order number.

Basically what you are saying is that I should not be doing this and that I should use separate columns to collect this data and only combine them on a report or a form?

Using your suggestion of having everything separate in its own field, I would then be able to combine the desired end result on the form or report but still retain the database structure.

You will have to forgive my ignorance with Access. I have never really gotten into database management before and this stuff is a little new to me.

Thanks for your comments,
Matthew

 

[Joe4]

Since this was a new thread, I haven't seen anything regarding a previous post.

To see what I am referring to, let's assume we have the same example I used in my first post. If we create a new query, select our table that contains the Date and User Name fields. Then we can create a calculated field, by entering the following in the Field space:
strLastAccessedBy: [dteCurrentDate] & " " & [strUserName]
(we do not need strLastAccessedBy in any table at all, it exists only in this query).

Now any report or form you create/use probably has lots of other fields. So we can either add all the other fields to this query we just created, or we can combine this query with other tables/queries to create another query which we will use as the Control Source of our forms/reports.

 

[spectraflame]

Given what you have stated, that makes sense to me. Basically I will be able to produce screen or hard copy results of managements requests, but be able to better manage the table and its records.

Since I have multiple departments, 6 in all, and management wants to see the department name in front of each service order, would your suggestions require me to have a unique entry form and a unique report for each department?

Not sure if this matters at all, but I am now planning on have a separate table for each department. The only reason for this was that management wanted the service orders for each department to be in sequential order.

Matthew

 

[Joe4]

You should be able to house all of your data in the same table (no need for 6 different ones). You can use queries to sort/separate the records by Department.

If you want to get a little fancy, you can create a form with a combo box that you can use to select records by department. Here is a thread in which I helped someone do this. It involves some VBA and SQL.

http://www.mrexcel.com/board2/viewtopic.php?t=104832&highlight=

 

[spectraflame]

The only thing with storing them in the same table is the numeric order number. Meaning that I may have 10 records in the table. If I use the auto number to give me an ID# to use for part of the order number, how would I keep the number consecutive by department like:

ID #1 - Electric - Order # would be E-1
ID #2 - Water - Order # would be W-1
ID #3 - Water - Order # would be E-2
ID #4 - Maint - Order # would be M-1

This was my only reasoning for having each department in its own table.

Matthew

 

[Andrew Fergus]

The only thing with storing them in the same table is the numeric order number. Meaning that I may have 10 records in the table. If I use the auto number to give me an ID# to use for part of the order number, how would I keep the number consecutive by department like:

ID #1 - Electric - Order # would be E-1
ID #2 - Water - Order # would be W-1
ID #3 - Water - Order # would be E-2
ID #4 - Maint - Order # would be M-1

This was my only reasoning for having each department in its own table.

I agree with everything previously suggested by jmiskey and can add nothing further there. However, your last post posed a couple of questions that require a little further analysis.

If you split the data for each department into separate tables then you are going to make any future analysis of the data across departments very difficult. As suggested by jmiskey you are better off keeping all of the data in the one table and using filters etc to access department specific data.

I can understand why your managers want sequential numbers on the orders for each department, but I'm not sure why they need to be consecutive - although it is not ours to question why. I suspect the managers want to be able to look at the last order for the "Water" department, say "W-9", and think there are 9 orders for the "Water" department.

I used to use autonumbers but I no longer use them - as you will see why in the following example :

If the managers want to use the numbering system to know how many orders there are for a department by looking at the order number then they may be making a false assumption given the way autonumbers work. If you partially create a record (create a new record and press escape to delete the record prior to saving it) then the next record you create will have an autonumber that jumps the one that you didn't save. You can't rely on the autonumber to give you a consecutive series of numbers. This is particularly so where users aren't aware of how autonumbers work. Try it and you will see what I am referring to. (N.B This is the case for Access 2000 but I can't vouch for this with later versions of Access)

A workaround for this problem is to have another field in your orders table (in addition to or in place of the autonumber) that provides a unique, sequential & consecutive number for each department. For each department, you could write the last order number into a separate table that has just the one record in it (e.g. for tblLastOrderWater the one and only record in the table for the previous example will be the number 9). This number is then called and incremented to give the next order number for the Water department and the last order number written back into the table when you are finished. Each department will require it's own tblLastOrder____ with an initial value of 0.

Alternatively you could calculate the last number used for each department on the fly using a "max" query once the user has selected a department for the order entry - although this can get tricky if the user wishes to enter multiple orders for multiple departments at the same time.

If this is all too hard, then you might want to convince the managers that consecutive numbers are not feasible (without unduly complicating the database and data structures and given the fact you can't rely on the autonumber) and that non-consecutive sequential numbers will be the order of the day. You can always calculate the number of orders for each department through a query, if that information is required.

Hope this helps, Andrew.

 

[Norie]

I can understand why your managers want sequential numbers on the orders for each department, but I'm not sure why they need to be consecutive - although it is not ours to question why. I suspect the managers want to be able to look at the last order for the "Water" department, say "W-9", and think there are 9 orders for the "Water" department

Surely to get the no of orders for a department the best way is to create a totals query with a count on department.

 

[spectraflame]

Their reasoning for want sequential numbers for each service order per department is for filling purposes. OUr organization still relies heavily on paper. Each service order, when completed, is printed and then filed according to department, month, and year. They just want to be able to place them in sequential numeric order. Does not really make a whole lot of sense to me because even if the numbers were not sequential, they could still be placed in numeric order. I think the reasoning for this is because of when they used to write each service order by hand.

The way I invison the order entry form is that the user select the department and then keys in the appropriate information. Once the order is complete, they have the option to print the current record or close the entry form and return to the previous menu.

I agree with the comments of writing a query to count the number of orders on a daily basis. Maybe even a simple report that lists the order numbers per department, but I doubt if management will be able to grasp that concept. I am just supposed to do and not question.

Thanks again for the suggestions.
Matthew