Combining Multiple Coloums Into One.
Feb 16, 2007
Hello all,
I have an issue I can't quite get my head around. I have a table with four coloums that list Emplyees by Number. I need to pull thase number and combine them into one colomn through a querry (I think). I don't need to join the coloums in the same record. Example...
Now... leader Pilot Admin Facilitator
11800 11801 11802 11803
Needed... Participants
11800
11801
11802
11803
Thanks for the help,
Keith
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
Jul 16, 2007
Here is the situation that I'm hoping that someone can help me with. I'm working with a database that tracks our condo units - from prospect coming into the system until we close them as a buyer. All the units are setup in the system so a salesperson will select from the units available. All that works fine when I create reports. The problem is trying to get the parking and storage on the same reports with the unit information. The problem is that there are multiple parking/storage units "attached" to a single unit and I cannot figure out how to get them to all appear on a single row of the report. As an example -
I have units A, B, C
Parking units p1,p2,p3,p4,p5,p6
Storage units s1,s2,s3.
Unit A uses parking units p1, p2, p5 and storage unit s2.
Unit B uses parking unit p3 and storage unit s1.
Unit C uses parking unit p4, p6 and storage unit s3.
How do I write a query/report that would show:
Unit Parking Storage
Unit A p1, p2, p5 s2
Unit B p3 s1
Unit C p4,p6 s3
Thanks,
Chester Campbell
ccampbell@jfreed.com
View 3 Replies
View Related
Sep 4, 2007
I'm trying to make a payment report which prints a separate pay sheet for each employee based on what job they did. I don't want to get into too much detail here, but basically, a name is associated with each action in a setup like this:
Job1......Job2......Job3......Job4......Job5
Joe.......Bob.......Jim.......Fred......Jeff
Joe.......Joe.......Jeff......Jim.......Jeff
Bob.......Bob.......Jeff......Bob.......Jim
and so on and so forth.
I want to have a query that gives one column that looks like this:
Bob
Fred
Jeff
Jim
Joe
I already know how to sort and group by, but how would I write a query that contains one column, then another column when that one ends, then another, et cetera?
(I need to know this, because it would be a lot cleaner to show one report for all 5 jobs than a report for each job separately, and of course, that's how the boss wants it!)
View 3 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
Dec 4, 2006
Hi,
I'm putting together a (hopefully) highly automated database that will allow me to produce clear reports for various areas of responsibility in a new job.
I have 3 excel spreadsheets linked directly to access, containing work queue reports. I then run queries that copy new work items into a main table, followed by a query that looks for work items that no longer appear (i.e. work is finished and closed) and then automatically inputs the date.
For reporting purposes i initially want a basic output, i.e. work items raised, and work items closed, for each area of resposnibility. The only trouble i've found is that i have to do a different query for each output, then another query that pulls all the other queries together in order to maniuplate into a graph...
So at the moment i have two queries per responsibility, and one pulling those two together i.e.:
New Work Items:
SELECT Count(ticketsds.id) AS [DS New]
FROM TicketsDS
WHERE (((Format(ticketsds.[date received],"mmm"))=Forms!ReportingDate!comboMonth));
Closed Work Items:
SELECT Count(ticketsds.id) AS [DS Closed]
FROM TicketsDS
WHERE (((Format(ticketsds.[datefinished],"mmm"))=Forms!ReportingDate!comboMonth));
Then i have this query pulling the two results together:
SELECT DSClosed.[DS Closed], DSNew.[DS New]
FROM DSClosed, DSNew;
Is there anyway of combining the first two queries into one, rather than have 3??
Any help would be greatly appreciated, as if i could find out how to do this it woudl enable me to streamline other queries....
Many Thanks!
Gareth
PS I've tried 'UNION' but that just tags the other results under the same field name which i can't use to create graphs...
View 2 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
Mar 12, 2007
I have a table with a field called "description"
another field in fame table called "length of cable"
In description field it contains among other things, the length in it . It may be at various locations in the test data in the field. The length may be in the form xft or xxft xx foot xxfeet xx feet..
I have another field that I need to put the length in a standard form like xx feet
(The field will have different units in it later not just feet.)
I am using update query
with
criteria of Like "*3ft*" Or Like "*3 foot*" Or Like "*3 ft*" Or Like "*3 foot"
with update to (of the length of cable field ) set to 3 Feet
The data has many lengths
1ft
2ft
3ft
4ft
5ft
6ft
7ft
10ft
12ft
14ft
15ft
20ft
25ft
and one....
I would like to automate it to one query and hopefully avoid the problem I have when doing it as is, *5ft* also finds 25ft and 15ft incorrectly for the goal of the update. same on 12ft 14ft
' and " are the symbols for inches and feet.
Also some of the data is in the form xx' or xx" I hit a wall on this due to the ' character
need to do the same to that as well.
Any suggestions '''
before I lose any more hair ...
View 6 Replies
View Related
Oct 4, 2012
I have created a DB who's sole purpose is to track employee availability and job positions available (a poor man's version of a scheduling database, if you will). I have 2 listboxes on one form. Listbox A shows all the employees available for work, and Listbox B shows the available work positions that require an employee. I am trying to create a functionality behind a button that will allow me to highlight one row in Listbox A and one row in Listbox B to combine the information in a seperate table (still not created) and refresh each Listbox to remove the entries. Similarly, I would also have to create functionality behind another button to undo the above, if needed.
View 1 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
Nov 21, 2006
I have three tables, with the following data (fields separated by "-" here):
Unit - Customer Number - Customer Name - Type of A/R - Total A/R
Unit - Customer Number - Customer Name - Type of A/R - Total Overdue
Unit - Customer Number - Customer Name - Type of A/R - > 90 Day Balance
I would like to combine these tables into one table with the following field names:
Unit - Customer Number - Customer Name - Type of A/R - Balance
When I append the data, it comes out like this:
Unit - Customer Number - Customer Name - Type of A/R - Total A/R - Type of A/R - Total Overdue - Type of A/R - > 90 Day Balance
I would like this new table to include all rows from the original tables, sandwiched on top of each other. As far as I can work it, the Append Query only adds the data as add'l columns; not as add'l rows. I tried changing the field names entirely, so that the final field in each table is called "Balance." However, I still get the same result as above - the columns are added, instead of rows. I swear there's a way to do this, I just can't remember it!
View 1 Replies
View Related
Sep 17, 2013
I have a form that I use to filter a sub-form. In the form I have 4 toggle buttons that filter the corresponding fields in the sub-form quite well. What I would like to have is when one toggle is selected, the user can select a second or third toggle to further refine their inquiry. I am attaching the code that I used for the individual filters.
Code:
Private Sub Toggle_Filter_DOB_Click()
If Me.Toggle_Filter_DOB = True Then
[Forms]![Phase 2]![FormPhase2_sub].Form.Filter = "DOB = #" & Me.ATS_DOB & "#"
[Forms]![Phase 2]![FormPhase2_sub].Form.FilterOn = True
Me.Toggle_Filter_DOB.Caption = "Filter On"
[Code] ......
View 4 Replies
View Related
Jul 17, 2014
I have a database which is importing several Excel workbooks, each with multiple worksheets. Every workbook has 20 worksheets, with the same 20 worksheet names. When they are imported they come in as one table for each worksheet, named tblWorkSheetName_X with X starting at 1 and increasing for each worksheet brought in with the same name. So if the worksheet names are A-T I have tblA_1 through tblA_6 and likewise for B - T.
I would like to combine all of the tables which come from similar worksheets into one table (one table per name).
I.e. I want to combine the data in tblA_1 through tblA_6 into a singular tbl_A and likewise for tables B through T. So in the end I will have one table for each worksheet name A-T. t how to code this successfully?
View 14 Replies
View Related
Aug 8, 2014
I'm trying to set up a way to import and combine excel files that contain multiple data sets. So for example, each excel file has a summary heading which consists of the first 3 rows.
Each data set thereafter consists of approximately 50-60 rows of data that I would like. There are approximately 1400 groups of data. Each group has a label which includes the state and the store number. I would like to automate a way to copy the state and store number information down each data set as well.
I have approximately 200 excel files that I want to load into access and have it format it so it basically will end up 1 big file with State, Store Number, relevant information from the data set. I've seen a module do this before, can't remember how to do it.
View 4 Replies
View Related
Jan 16, 2014
Im trying to work out the best way to combine results from a table. Not knowing the correct terminology I didn't want to post in a specific section of the forum until its clear in my head.So I have a query currently which shows the entries into my database for example (these aren't the column names precisely as I know some are reserved words its just to demonstrate):
UserA - Hours - Date - ProjectA
UserB - Hours - Date - ProjectA
UserA - Hours - Date - ProjectB
UserA - Hours - Date - ProjectB
UserB - Hours - Date - ProjectA
UserA - Hours - Date - ProjectA
What I am trying to do is combine UserA's entries with ProjectA and UserA's entries with ProjectB (seperatly) and UserB's with ProjectA and UserB with Project B (again seperatly).So the end result would show a total of User A's hours on Project A and separately ProjectB and the same for UserB.
View 12 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
Sep 30, 2014
I am working on a report that has some special characteristics.
Let's say I have a list of groups of Vendors in a table, complete with VendorID. I have 3 other tables that use the VendorID: Complaints, Complements, and Terminations.
Each of these tables has a date that the Complaint, Complement, and Termination notice was received.
Every Fiscal Quarter, a report is pulled that looks back over the 4 preceding quarters to determine if a 5% threshold has been crossed by any of the vendor-groups in regards to the amount of Complaints they received.
The equation used for that is : (complaints/vendors_in_group)*100
It is imperative that the information has the current fiscal year and fiscal month (which I am tracking with functions from MS website), and I need to be able to store the information attached to the fiscal year and month.So when a user goes to the form and inputs the desired Fiscal Year and Fiscal Month, the database can display the 4 previous quarters of information...split into Q1, Q2, Q3, and Q4.
What I would like to have happen is to be able to have one table where the information is stored, quarterly, so that it can be retrieved for the report.
Questions:
1. Is it possible to have one line, per VendorID, that has the total number of Complements, Complaints, and Terminations, as well as the threshold percent stored in a table? Right now, I am getting LOTS of duplicates and blank lines when I try to put them all together. It has the right data, but takes about 10 rows per VendorID.
2. It is very important that the total number of Vendors in a group be captured on that quarterly report, so maintaining that number, in the same table, is essential and must be tied to the VendorID.
3. I have looked at Union Queries and Crosstabs, but I just dont know enough about them to make it work.
View 4 Replies
View Related
Feb 19, 2006
I have just started using MS Access to enter customers into a database. I constructed the database and delegated the .mdb file to my coworkers so that they can enter their customers as well. Now I would like to combine all the customers entered by all the employees into one general table/database containing all of the customers entered by all of the employees. If anyone knows how I would go about doing that I would really appreciate the knowledge. Thank you.
View 3 Replies
View Related
Apr 6, 2007
Hello,
I maintain an Access database at my workplace, and have been asked to bring in information from other company locations. Unfortunately, there are conflicts; e.g. part number 12345 might be something totally different at another facility than it is here. My first instinct was pretty obviously to add a Location column and all queries would include that as well as the ID. However, someone else is telling me we should build metadata into a new part numbering convention. An example:
PlantA.12345 (all one field) would be a totally different part than
PlantB.12345
He says it would be very beneficial to ensure that, seeing only a part number, you would know where it was from. I understand his point, but it just seems like strange database design.
Has anyone done something like this, or considered it??
Thanks in advance.
View 6 Replies
View Related
Feb 1, 2005
Hi,
I want to join (combine) two tables that have only identical column fields in common into one master table. There is no relation between the two tables. This master table I will use afterwards as basis for a excel pivot table. How can I do this?
Suppose I do not have identical column fields. Is it still possible to combine the tables and that in the new table the missings are filled with eg zero's.
Thx,
Agamemnon
View 9 Replies
View Related
Jun 22, 2007
I'm modifying a database for someone in our marketing department. The DB tracks shirts that are given to employees.
The area I'm having a problem with has to do with the shirt style.
The current categories offer:
Long Sleeve (White)
Long Sleeve (Black)
etc..
I want to combine those two so they fall under the simple category "Long Sleeve"
The problem I'm having is that when I do a find/replace, it will allow me to change all of the Long Sleeve (White) to Long Sleeve, but then when I go to change Long Sleeve (Black) to "Long Sleeve", it creates duplicate entries because the same order contained x amount of Long Sleeve (White) and x amount of Long Sleeve (Black).
Is there an easy/ automated way to combine these results into "Long Sleeve" and add the quantity fields together to show the total amount of shirts ordered?
View 3 Replies
View Related
Jun 1, 2005
Hello. I have a problem implementing two separate SQL statements in dreamweaver from an Access 2003 database. The queries work fine in Access but is causing many problems in dreamweaver as they are separated.
I very much so need help in combining these two statements to form one SQL statement. Any help will be greatly appreciated. Thanks again.
SQL Statements
FIRST QUERY
SELECT tblApp.Time
FROM tblApp
WHERE (((tblApp.BranchID)=[Forms]![frmApp]![branch]) AND ((tblApp.Date)=[Forms]![frmApp]![date]));
SECOND QUERY
SELECT tblTime.Time
FROM tblTime LEFT JOIN qryAppointmentsOnDate ON tblTime.Time = qryAppointmentsOnDate.Time
WHERE (((tblTime.Branch)=[Forms]![frmApp]![branch]) AND ((qryAppointmentsOnDate.Time) Is Null));
Rixx
View 4 Replies
View Related
Jul 3, 2005
http://www.warwickwebvisions.co.uk/MA/query.gif
The image above shows results from a query I've created. However, I need to group the results by Product Id and display a sum of the individual Product Id's. So I should have something like:
Commercial Loan Mr Steve F Cobblestone LNCLCLRE £299,491.33
Demand Deposit Mr Steve F Cobblestone DD033 £7,402.81
Demand Deposit Mr Steve F Cobblestone DD043 £3,929.78
Time Deposit Mr Steve F Cobblestone TD013 £12,195.60
Time Deposit Mr Steve F Cobblestone TD018 £961,924.12
Time Deposit Mr Steve F Cobblestone TD025 £113,483.58
Any ideas how I construct a query to display the results as per above??
View 1 Replies
View Related
Sep 9, 2005
I am pretty new to access and struggling with a query problem. I currently have a table containing data like:
Name_____________Color Chosen
Bill..............................Red
Bill..............................Blue
Tom............................Red
Mark...........................Green
Bill..............................Green
Tom............................Blue
And I would like to be able to get to
Name______________Colors Chosen
Bill..............................Red, Blue, Green
Tom............................Red, Blue
Mark...........................Green
Can anyone suggest a query to get me this second table. Theoretically the maximum number of Colors that one person can choose is 4.
Many thanks in advance
Chris
View 5 Replies
View Related
Dec 13, 2005
hi
i want to comibine some queries into one table
the first quierie contains like 6 records and the second one contains 3 records
the problem is is that when combining these columns into the one query the second contains more then 3 records when it should be only displaying data for the 3 bits ?
View 2 Replies
View Related
Feb 22, 2007
:confused: I need help desperately. I have two tables I am trying to comine in a query. Seems easy enough right? Yet the simple task has become impossible. I have two tables with the same feild for data, both containing 8 coulmns. Ex:
Table A-January
Columns
A B C
Data: 1,2,3
Table B-February
Columns
A B C
Data: 4,5,6
I'm trying to create a query that will contain the data 1,2,3,4,5,6 but am getting 11111111111,222222222,33333333333,4444444444,55555 55555,6666666, or getting 1,3,5. I've tried refining the data in many ways, even tried just created a table. Hand keying it in seems rediculous due to the end numbers being over 10,000. Any advice??? Please??? :(
View 14 Replies
View Related
