Combining Fields On A Form
Dec 20, 2006
Hay Folks,
I'm having trouble with a subform. My subform (dataform) has a lot of fields. I want to reduce them by combining some fields with an expression. This would save some space on the form.
The problem.
I cant get the expression right to show real values instead of key-values.
Here's the sample expression for the control-field:
=[Roadtype] & [Rnumb] & "-" & [position] & " " & [from] & "-" & [to] & " " & [lanetype] & [letter]
Most fields are of the lookup type, which have a rowsource-expression to show the desired value. I.e. for [lanetype]...
SELECT [qryBPSverhardebaansoort].[lanetype], [qryBPSverhardebaansoort].[Omschrijving] FROM qryBPSverhardebaansoort;
Can anybody help me on track?
Thanks a lot.
View Replies
ADVERTISEMENT
Jul 17, 2013
I trying to combine three columns that I have into one column without combining fields.
Currently what I have:
(see image below)
What I want:
ID-----MOC
##----name1
##----name2
##----name3
##----name4
##----name5
etc
The list I have will be much longer and will be changing frequently, which is why I can't just go on excel and manually do this.
View 14 Replies
View Related
Mar 15, 2007
Hi Everyone,
I hope I'm posting in the right place, I've been trying to solve my problem using queries so I thought it might be appropriate here.
I have a database for a health care service which contains among others the following four tables...
Table 1 contains client details, primary key [ClientID] plus other client details.
Table 2 contains Episode of care details, primary key [EpisodeofcareID], [episodeofcareDate] etc...
Table 3 contains all test results for assessment 1, primary key [CoreID], [CoreDate] plus numerous scores for individual tests.
Table 4 contains all test results for assessment 2, primary key [HonosID], [HonosDate] plus numerous scores for individual tests.
Table 1 is linked to Table 2, and Table 2 is linked to both Table 3 & 4.
I have set up a search procedure whereby the user enters a clients name which then opens a list box of all clients with that name. When the client selects from the listbox I want a second listbox to open which has three visible columns. One giving the date of each episode of care for the client, the second giving the date of each assessment completed and the third giving the name of the assessment carried out.
I'm managing to get the date of the Episode of Care to display on the first visible column but I'm getting stuck on the next two. This would seem to involve somehow combining data from different tables into individual columns on the listbox. For example I need the second visible column on this listbox to list all the dates of assessments for the chosen client from tables 3 & 4. (e.g. [CoreDate] and [HonosDate]). Column three would then give the title of the assessment next to the date in column two. None of the tables have a field listing titles as this is determined by which table the data is entered into. I've been trying to solve this by queries without much success.
Could anyone give some ideas as to how I could solve these problems?
Many Thanks
John
View 9 Replies
View Related
Oct 12, 2007
Hello,
I need to find a way to modify the following structure:
Name, Page, Grid
Acton Rd, G10, 12
Acton Rd, G10, 4
Acton Rd, G10, 8
Adams Ct, F6, 2
Into something like this:
Name Page Grid
Acton Rd, G10, '4, 8, 12'
Adams Ct, F6, '2'
What is the best way to go about tackling this?? I need to create this in a table so that I can export it from Access and into a .dbf to be used with another program.
Thanks,
View 3 Replies
View Related
Jan 26, 2006
I have 2 seperate fields within a select query (In this example Field 1 - Country and Field 2 Number).
I have combined the 2 together into one field.
However a space appears within my results i.e
Field 1 Country Field 2 - Number
----------------- -------------------
England 4
USA 10
Combined fields:
Field 3 - Combined Country + Number
--------------------------------------------
England 4
USA 10
How can I remove this space within Field 3
Thanks
View 4 Replies
View Related
Aug 15, 2006
I have fields lastname, firstname, spousefirstname, spouselastname -
I would like to display:
Koenig, Mark & Jane (not a problem)
- but in some cases the spouselastname is different so then I would like to display:
Koenig, Mark & Jane Doeinger
This is how it displays with my code:
Koenig, Mark & Jane & Doeniger
I can't seem to get rid of the & between the spousefirstname and spouselastname.
Code:SELECT Potentials.Agent, [LastName] & ", " & [FirstName] & IIf([SpouseFirstName] Is Null Or [SpouseFirstName]=""," "," & " & [SpouseFirstName]) & IIf([SpouseLastName] Is Null Or [SpouseLastName]=""," "," & " & [SpouseLastName]) AS Expr1, Potentials.Address, Potentials.City, Potentials.State, Potentials.ZipFROM Potentials;
How do I get rid of the second &
View 5 Replies
View Related
Sep 17, 2005
Hello
I have a political database with a separate field for street number, street name and apartment number. I've been asked to prepare a query that will only list the first member of a household. Example, the query would only show one member from a two person household if they both have the same address. I figured that I would somehow prepare a query that combined these 3 fields in a expression and then display the total row and choose FIRST. That didn't work, perhaps my syntax was off. Here's what I tried:
Expression: (Trim(StrConv([AddressStreetNumber]&" "&[AddressAptNumber]&" "[AddressStreetName],3)
Then I set the Total line to FIRST
Is it my syntax or is it that I can't join separate fields together like you can in a report ?
View 2 Replies
View Related
Nov 10, 2006
Not exactly sure if a query is what I need in this situation or if it is what I need how to get there.
In the attached db example on the case form
there is a section for technicians to go in and take credit for steps that they performed as part of the overall case
So clv1 might be done by User A
Then clv2 might be done by User B
But the next case it might be switched.
I need a method getting the sum of the total clv's field for each technician in two different ways
1 would be the total clvs for USer A for the current month,
2nd would be the total for the year- or actually a prompt for a date range
Tried using the query wizard but it doesnt combine the names
Then I tried an individual query on each set - that worked but then I only get the ones in the first column - not all the clvs that they did.
Hope that makes sense - is there a way to do this or am I in the wrong forum for trying to figure out a way?
Well the db example is imb so it cannot be attached
Thanks for any help.
View 13 Replies
View Related
Feb 15, 2007
Hello,
I have a table with employee numbers in four fields (Leader, Facilitator, ect...). How can I combine those numbers into one column trough a query?
Thanks in advance.
Keith
View 1 Replies
View Related
Sep 15, 2007
Hi,
Im combining 3 fiels with the following code.
Newfield: [Field1] & " " & [Field2] & " "[Field3]. So after eacht field a space is placed but when field 2 had no data there are 2 spaces between field 1 and 2. How do i solve this
You can react in euther english or dutch
Tank you in advance
View 1 Replies
View Related
Feb 21, 2005
In a form I can easily combine and display the content of two text fields in a third text field with the formula =[field1] & [field2]. Very well. Now I would like to combine and display only the first letter of each of the two text fields. This I would expect to go somewhat like this =(letter,1,1[field1]) & (letter,1,1[field2]). How is the correct formula?
View 4 Replies
View Related
Sep 12, 2004
Hello,
How can I combine two seperate fields into one field? For example
field1 has 123 field2 has Smith St. I want to put it in one column
that has 123 Smith St.
Thanks
View 1 Replies
View Related
Jan 5, 2005
Hi,
I was trying to combine some fields into one with a search query,using sql. I was going to search for a postcode then add the first two lines of an address (which are at present in 2 fields) together seperated by commas ",". For instance:
Strd
12 Rock View
Str
Marston Cresent
change to :
12 Rock View, Marston Cresent
I can add fields together with an expression but cannot divide them by commas, any help appreciated.
M-.
View 6 Replies
View Related
Aug 21, 2013
I would like to make a report to show how many employees and which employees are attending to which colleges/universities.
In my data (800 records), I have two fields which is "College 1" and "College 2" for each employee.
There are values that are enter in college 1 for some employees, other employees have the same value that is enter in in college 2. How do I get to show a report that has all the employees who attended the same college in either college 1 or 2?
For instance, this is my raw data:
Name - College 1 - College 2
Bob - University of HI - Honolulu CC
Sandy - Honolulu CC - University of HI
Clare - Kapiolani CC - University of HI
John - University of HI - Windardward CC
I want my report to show:
Colleges -
University of HI
Bob
Sandy
Clare
John
Honolulu CC
Bob
Sandy
Kapiolani CC
Clare
Windard CC
John
View 4 Replies
View Related
Jan 21, 2014
I know how to concatenate fields in a query but have not done it with memos before. I have three memo fields and I want each to show up in one field with a bullet in front of each. Memo1 may be empty and memo2 and memo3 may have something or memo 3 may have some text but the others are empty so I need to be able to list the memos without the empty spaces. Is a query the best place to do this or in the report and how do I do it?
View 4 Replies
View Related
Jul 27, 2014
i wish to combine multiple fields (there are no fixed number of fields, they vary depending on the data, so i guess union queries are out of the question) into one large field. For example:
TABLE 1:
PNumber
PName
C1
C2
C3
1
AAA
0.1
0.2
0.3
2
BBB
0.4
0.5
0.6
So i wish to combine the fields C1, C2 and C3 into a larger fields containing all the data. So considering the example above, it should look like this:
TABLE 2:
PNumber
PName
C1+C2+C3
1
AAA
0.1
1
AAA
0.2
1
AAA
0.3
2
BBB
0.4
2
BBB
0.5
2
BBB
0.6
I plan on entering data into TABLE 1 using a form and running a query, or some code etc so that it looks like TABLE 2.
View 14 Replies
View Related
Jun 5, 2007
Hello All,
I am trying to figure out the best was to combine fields from multiple rows into one row & field.
Example: I have a table that contains footnotes and products. With a simple query I would get the following 3 rows:
Product........................................... .......Footnote
V.I. Capital Appreciation Fund.................3
V.I. Capital Appreciation Fund.................5
V.I. Capital Appreciation Fund.................1
What I want is one row and the 3 footnotes combines into one field:
Product........................................... .......Footnote
V.I. Capital Appreciation Fund.................3, 5 ,1
Any help would be greatly appreciated.
View 14 Replies
View Related
Mar 6, 2014
I'm creating a query which pulls together the numerical values of 32 separate fields. Each field has the potential to have a value in it ranging between 1 and 9, but most fields will be blank.
I want to prefix the value (if the value is not null) with a 2 or 3 character-long code relating to the field name.I then want to combine them all together in a query so I can easily copy and paste the output to a spreadsheet.Here is what I have so far, with just 9 of the field names, and it's already looking a bit clunky.
Code:
IIf(IsNull([zam_adm]),"","ADM") & [zam_adm] & " " &
IIf(IsNull([zam_cts]),"","CTS") & [zam_cts] & " " &
IIf(IsNull([zam_crs]),"","CRS") & [zam_crs]& " " &
IIf(IsNull([zam_IPB]),"","IPB") & [zam_IPB]& " " &
IIf(IsNull([zam_opr]),"","OPR") & [zam_opr]& " " &
IIf(IsNull([zam_owl]),"","OWL") & [zam_owl]& " " &
IIf(IsNull([zam_pmi]),"","PMI") & [zam_pmi]& " " &
IIf(IsNull([zam_wl]),"","WL") & [zam_wl]& " " &
IIf(IsNull([zam_sgt]),"","SGT") & [zam_sgt]
The output, unfortunately, is putting spaces between empty fields and I'm not sure how to prevent this in the syntax?
View 3 Replies
View Related
Nov 14, 2013
How i would best combine values in a table to produce a 'primary key id number.'
For example: the first letter of a city in the ID and the next number available/auto number - Portsmouth -> P233
I know i can create this in a query however i want it as the unique ID for that record entry in a table. If that doesn't make sense i can try to elaborate some more.
View 14 Replies
View Related
Jul 10, 2013
I have two tables. One table is a list of classes with the number of enrolled students:
Class............StEnrolled
English 1A........6
English 1B........12
English 1C........20
English 1D........25
Reading 1.........4
Reading 2.........15
And the other table is a list of "combined" classes (meaning they are, essentially, the same class and should be counted as such):
Class...........CombinedWith
English 1A.......English 1B
Reading 1........Reading 2
Using the first table, I currently generate a query (and build a report) that displays class enrollment levels. What I need is for this query to identify combined sections from the second table and display them as such:
Class........................StEnrolled
English 1A/English 1B........18
English 1C.......................20
English 1D.......................25
Reading 1/Reading 2..........19
View 7 Replies
View Related
Dec 2, 2014
I have 3 cross tab queries that are completely identical with the exception of the field that they pivot. Each field is searching for the same values just in different columns, with the end goal being to get the sum of the values for each pivoted column. I'm wondering rather than having 3 almost identical queries is there a way to use a crosstab to sum the values from each of the three fields rather that having 3 queries which then have to be aggregated in a fourth?
QUERY1
Code:
TRANSFORM
IIf(Count(PT_LEVEL.UNIT) Is Null,0,Count(PT_LEVEL.UNIT)) AS CountOfUNIT
SELECT
PT_LEVEL.INF_YEAR,
PT_LEVEL.INF_MONTH,
PT_LEVEL.UNIT
[code]...
View 2 Replies
View Related
Jul 22, 2014
I have a form (DropDown form) that has 3 drop down fields, you select your values from the drop downs and you would push a command button that runs an event procedure which runs a query (DropDown qry test). The user should have the option of picking any combination of fields to filter by. Or no combination, which would return all values in all fields. So I am basically using the form as parameter's for the query.
The problem I'm having is that my query is returning values for one field AND values for another field. Even if the other values selected are not in the same record. It's not combining the fields together to filter. For example: you pick a Project name and Supplier name, the query will return records that have the project name you selected but it will also return records with the supplier name you selected that have a different project name.
I've attached screen shots of the form and the design view of the query (the screen shot cut off the last column name. It is meant to say "Expr3: [Forms]![DropDown form]![Combo7]").
Using Windows 7,
Access 2010
Is there a way to select multiple values from the drop downs?
View 14 Replies
View Related
May 29, 2014
I'm creating a database which would automatically assign a unique workorder number in the "WorkorderNumber" field of the "Workorder" table. Note: this will not be the Primary number for the work order.The WorkorderNumber will be developed by combining fields from the "System" table. Fields used to create the WorkorderNumber from the "System" table are:
1) Location [currently in the field is "MAX"]
2) CalendarYear [currently in the field is "2014"]
3) NextWorkorderNumber [currently in the field is "1"]
I need the following to happen to the "WorkorderNumber" field of the "Workorder" table:
a. I need the field to read as followed: Max-2014-00001
b. I need the number 00001 to autonumber to 00002 on the next entry of a new request. [MAX-2014-00002].
c. I need to be able to control how the "WorkorderNumber" field populates by changing the "CalendarYear" and "NextWorkorderNumber" fields within the "System" table without messing up prior workorder numbers already populated.
View 1 Replies
View Related
Apr 13, 2008
I created a form in Access that retrieves data from a table. Inside the form, I am able to access/populate data fields with data from the table. I also have data fields, inside the form, that requires key-in data. I have some how lost the ability send all data field information to a second table and clear existing data fields for new entry.
Questions: What settings, code or buttons can I use to send/store data field information to a new table? What settings, code or buttons can I use to automatically clear all data fields from my form once data has been sent to the new table?
View 7 Replies
View Related
Jan 29, 2014
I have a totalquery that runs fine and give me the sum for both fields I'm looking for but I can't get the outputs to fill the fields on the form. I have tried the Dcount query in the control source but that just returns an error and locks up access.
Code:
SELECT [Tble-wcDelays].Causedby, Sum([Tble-wcDelays].HoursDelay) AS SumOfHoursDelay
FROM [Tble-wcDelays]
GROUP BY [Tble-wcDelays].Causedby, [Tble-wcDelays].LinkingID
HAVING ((([Tble-wcDelays].LinkingID)=[Forms]![Frm-ePlusCent]![cleanID]));
That is the query.
View 14 Replies
View Related
Jun 20, 2006
Hey Everyone,
I am stuck trying to figure out this problem. I have a main form "frm_tirelog_600" which has 4 combo boxes "cboleg",
"cbocar", "cbopos", "cboserial" on it. The first 3 combo boxes are used as criteria on 1 of 3 subforms that I have.
The fourth combo box "cboserial" is used for the criteria in a query on another subform that I have called "frm_mount_600_subform",
which is independant from the main form, I hope I have explained that clearly enough. What I need to be able to do is
have the subform fill in 3 of the fields on it "leg", "car", "pos" with the value from the 3 combo boxes on my main form. I
can get it to show in the fields but not write to the table. I have searched the forum and have not been able to locate anything
that would work and really need any assistance with this. Im not real strong in the coding department which is where I
beleive this could be done.
I have attached a copy of my DB which I hope will better explain it. Any help would be greatly appreciated
View 8 Replies
View Related
