Listing Data Rows As Coulmns
Jun 27, 2005
Hello all,
I have a crazy question. Is there a way to write a query that would display data rows with common ID's as if they were columns in a query? It's for visually representing row data as if they were one record in a list. Basically, I would like to take the following data...
fldID fldDetail fldValue
1 Shape Round
1 Color Blue
2 Shape Square
2 Color Red
And display it in a recordsource like each is one record:
ID Shape Color
1 Round Blue
2 Square Red
Is there a way to do this? HTMS.
View Replies
ADVERTISEMENT
Dec 16, 2013
My aim is to chart the career history of many people in my DB and my intention is to achieve this with two "Main" tables with many smaller tables linked to it.
Table 1 will contain the generic data about the individual, employee number, name, DOB, etc etc (there are many more fields) and Table 2 would hold their career history. Where they have worked, when, etc.
When viewing Table 1, I see all data, including a sub table showing career history, which is great. What I would like to do however, is have a query that will return all data from table 1 and Table 2 that refer to the employee number.
Attempts that I have made thus far, mean I have to enter the employee number twice (I would prefer to enter this only once) and then it gives me many pages (in report view) each page has all data from table 1, and one entry from table 2.
I would like to see all data from table 1 and then all data from table two, listed.
View 4 Replies
View Related
Aug 22, 2005
I have a query which is grouped by year, facility name, and billing type and needs to display several columns of aggregate values for each of these, e.g. count of cases, sum of cases reviewed, average length of stay, etc. It would look like this:
2003 ABC Hospital Billed DRG 123 22 6.5
2003 ABC Hospital Billed Per Diem 23 0 4.2
2003 DEF Hospital Billed DRG 456 120 12.3
2003 DEF Hospital Billed Per Diem 56 10 2.1
However, if a facility does not have any Per Diem billings for example, how can I generate a row for it anyway? I cannot use a crosstab since I have multiple different values to display. I tried the following union query:
SELECT YR,FacilityName,BilledDRG,NumberOfCases,AverageLen gthOfStay,CasesReviewed,CasesDenied,AvgOflos,[Billing Eligibility] FROM qryDistHIVCasesStatsDAC
UNION SELECT YR,FacilityName,BilledDRG,0,0,0,0,0,null FROM qryDistHIVCasesStatsDACDRG
UNION SELECT YR,FacilityName,BilledDRG,0,0,0,0,0,null FROM qryDistHIVCasesStatsDACPD
ORDER BY YR, FacilityName, BilledDRG;
where qryDistHIVCasesStatsDAC
is the original query,
qryDistHIVCasesStatsDACDRG shows the year, facility name, and "Billed DRG"
qryDistHIVCasesStatsDACPD shows the year, facility name, and "Billed Per Diem"
but then I would get e.g.
2003 ABC Hospital Billed DRG 123 22 6.5
2003 ABC Hospital Billed DRG 0 0 0
2003 ABC Hospital Billed Per Diem 23 0 4.2
2003 ABC Hospital Billed Per Diem 0 0 0
2003 GHI Hospital Billed DRG 456 120 12.3
2003 GHI Hospital Billed DRG 0 0 0
2003 GHI Hospital Billed Per Diem 0 0 0
what I want is:
2003 ABC Hospital Billed DRG 123 22 6.5
2003 ABC Hospital Billed Per Diem 23 0 4.2
2003 GHI Hospital Billed DRG 456 120 12.3
2003 GHI Hospital Billed Per Diem 0 0 0
In other words, for each year, each facility should have a row "Billed DRG" and a row "Billed Per Diem" and only one row of each.
Thanks for your assistance. I hope I have explained this adequately.
View 5 Replies
View Related
Sep 27, 2007
Column A Column B Column C Column D
1*010101001000000 CF 5
010101002000000 RF
010101002000000 RF
010101029000000 CU
1*010101001000000 CF 10
010101351010000 RF
2*010101354000000 CF 11
2*010101354000000 CF 13
010101358000000 CY
1*010101001000000 CF 5
010401328100000 CF
NOTE in the actual data won't be there it is just to show you what I am referring to.
Is there any way in another column in a query to say if Column A repeats itself somewhere else in Column A AND if those two that are repeated both have a "CF" code add column C (which is just an amount for each number)
So for example in this data there are 2 numbers that repeat themself that have CF codes. For these I would need to total them in column D for the first number resulting in:
Column A Column B Column C Column D
1*010101001000000 CF 5 20
010101002000000 RF
010101002000000 RF
010101029000000 CU
1*010101001000000 CF 10
010101351010000 RF
2*010101354000000 CF 11 23
2*010101354000000 CF 13
010101358000000 CY
1*010101001000000 CF 5
010401328100000 CF
Any ideas?
Thanks,
Mike
View 14 Replies
View Related
Jan 11, 2006
Hello All,
I am have some difficulies in the following query:
The result i need is Summary5 ResultG/001, G/0015 (Room) and soo on
Field Names Are:
Room & Summary5
I realy am not sure as how to do this, if it help the reason why i need to do this is for a report that will show "Asbestos Found In Rooms ...."(Summary5:Asbestos Found In Rooms" "&[Room]) is what i tried but this does not show it right, any ideas.
Alastair
**************************** Resolved With Thanks to raskew (Bob) & Matt Greatorex*****************
View 11 Replies
View Related
Dec 19, 2005
Hello
I have a table with a list of lectures and the dates and times they are running. I then have a related table which lists the feedback marks and comments for each lecture. I want to enter them via a form and have set up a form with the the lectures and then a subform for entering the feedback. This all works fine. However I would like to be able to have the lectures listed by date order and wondered what the best way is to make this work so that the user can simply scroll through each lecture on the form and it automatically goes through the list by date.
I hope this is clear and thanks for any help.
Kay
View 1 Replies
View Related
Mar 11, 2007
Hi, I have data from a form on my website in a text file, that corresponds to each visitor's input, each 13 lines in the form belongs to one visitor, as shown (twice) at the end of this message.
What I would like to do is have each visitors inputs translated to ONE row, with 13 columns/fields each. It could be appended to the same table or preferably generated in a new one. Note, there are no blank fields, some won't have data after their title, i.e. addy_line_2: is often blank, but at the very least, addy_line_2: or another field name is always there.
It would ROCK if I could also automatically take the name of each field out, i.e. each name is continuous characters up to the : (colon) ...
Thanks in advance, my Access knowledge is obviously limited, I'm sure this is fairly simple!
The fields:
Phone: 213-555-1212
Submit: Continue
addy_line_1: 1000 Melrose Place
addy_line_2:
badge:
city: Los Angeles
email:
homegroup:
name:
program:
state: CA
volunteer:
zip:
Phone:
Submit: Continue
addy_line_1:
addy_line_2:
badge:
city:
email:
homegroup:
name:
program:
state:
volunteer:
zip:
thank you thank you thank you thank you thank you !!
View 1 Replies
View Related
Jan 19, 2006
Hi everyone,
I can't get my head around this so I'm looking for some help if possible please, there are two questions, the first is:
When in the query, I want the criteria for the date selection to be a question, ie. "[Week Start Date?]" but I want the actual criteria selection to be from the start date plus 5 days, the only way I've done that so far is to do ">=[From?] and <=[To?]", which uses two questions and I don't seem to be able to do ">=[Week Start Date?] and <=[Week Start Date?]+5" which seems basically correct, but I expect I'm writing it incorrectly (basic access knowledge I'm afraid :( )
The second question (after I've got the 5 day date criteria sorted) is that the query produces a table that shows basically the following:
Name Store Date Visited
Tom Bury 18/01/06
Tom Bury 19/01/06
Tom Diss 20/01/06
Dick Thetford 18/01/06
Harry Diss 20/01/06
The query is based on a part week period with the starting date ALWAYS a Monday, so no more than Mon-Sat will appear, I want to put the information into a table or query, so the result ends up as:
Name Store Mon Tue Wed Thu Fri Sat
Tom Bury 18/01/06 19/01/06 'Blank' 'Blank' 'Blank' 'Blank'
Tom Diss 'Blank' 'Blank' 20/01/06 'Blank' 'Blank' 'Blank'
Dick Thetford 18/01/06 'Blank' 'Blank' 'Blank' 'Blank' 'Blank'
Harry Diss 'Blank' 'Blank' 20/01/06 'Blank' 'Blank' 'Blank'
*Where the blanks are simply left empty, rather than putting in the word 'blank'
Essentially converting the "[From?]" (as stated earlier) or "[Week Start Date?]" to Monday, that date + 1 to Tuesday, etc, BUT putting multiple dates relating to name and store criteria onto one record :eek:
Beyond me I'm afraid, any pointers would be seriously appreciated, I expect I'm approaching the problem from the wrong angle.
Regards
Tony
View 1 Replies
View Related
Jan 19, 2006
Hi everyone,
I can't get my head around this so I'm looking for some help if possible please, there are two questions, the first is:
When in the query, I want the criteria for the date selection to be a question, ie. "[Week Start Date?]" but I want the actual criteria selection to be from the start date plus 5 days, the only way I've done that so far is to do ">=[From?] and <=[To?]", which uses two questions and I don't seem to be able to do ">=[Week Start Date?] and <=[Week Start Date?]+5" which seems basically correct, but I expect I'm writing it incorrectly (basic access knowledge I'm afraid :( )
The second question (after I've got the 5 day date criteria sorted) is that the query produces a table that shows basically the following:
Name Store Date Visited
Tom Bury 18/01/06
Tom Bury 19/01/06
Tom Diss 20/01/06
Dick Thetford 18/01/06
Harry Diss 20/01/06
The query is based on a part week period with the starting date ALWAYS a Monday, so no more than Mon-Sat will appear, I want to put the information into a table or query, so the result ends up as:
Name Store Mon Tue Wed Thu Fri Sat
Tom Bury 18/01/06 19/01/06 'Blank' 'Blank' 'Blank' 'Blank'
Tom Diss 'Blank' 'Blank' 20/01/06 'Blank' 'Blank' 'Blank'
Dick Thetford 18/01/06 'Blank' 'Blank' 'Blank' 'Blank' 'Blank'
Harry Diss 'Blank' 'Blank' 20/01/06 'Blank' 'Blank' 'Blank'
*Where the blanks are simply left empty, rather than putting in the word 'blank'
Essentially converting the "[From?]" (as stated earlier) or "[Week Start Date?]" to Monday, that date + 1 to Tuesday, etc, BUT putting multiple dates relating to name and store criteria onto one record :eek:
Beyond me I'm afraid, any pointers would be seriously appreciated, I expect I'm approaching the problem from the wrong angle.
Regards
Tony
View 2 Replies
View Related
Jun 30, 2006
Hi all,
I have a query which runs off a table. I have the following Fields as columns in query: WeekID, A, B, C, D, E
For each week, the letters correlate points given. So for week 1, "A" could have 1 point, "B" could have 3 points, etc.
I want to run a Query that will show A, B, C, D, E as rows like the following:
Letter_Week 1_____Week 2_____Week 3
A_______1_________5___________3___
B_______3_________4___________2___
C_______2_________1___________7___
D_______6_________3___________3___
D_______3_________1___________4___
I can't run a crosstab because it will only crosstab values within all of A, B, etc.
Any idea guys? Thanks in advance, as always.
Caliboi
View 3 Replies
View Related
Apr 6, 2014
I need to change rows data into column.
I have a table name as Temp_Report1 with fields Lab_No, Test Name, Reslt, Test_master
Lab_no
Test Name
Result
Test_Master
4.414
Hemoglobin
00
HAEMATOLOGY
4.414
Red Cells
01
HAEMATOLOGY
4.414
E.S.R
02
HAEMATOLOGY
4.414
Reticulocytes
03
HAEMATOLOGY
etc
The another table where I want to copy data is New_repo2 with fields Lab_no, v1, v2, v3 etc. Need as
Lab_No
V1
V2
V3
V4
V5...
4.414
Hemoglobin
Red Cells
E.S.R
Reticulocytes
Etc
How to change this from rows to one column.
View 1 Replies
View Related
Jul 19, 2015
I have data for multiple account numbers (for work) and dates, and I need to identify when there is a change in account number in order to add a new field with a count - which counts sequentially starting with 1 and then starts over at 1 when the account number changes.
Ex. The red column is what I am trying to create
Account Date_To Counter
12345 06/30/2015 1
12345 05/31/2015 2
12345 04/30/2015 3
19999 06/30/2015 1
View 2 Replies
View Related
Dec 21, 2011
I have an Excel sheet with data organized like:
1 A
1 B
2 A
2 B
2 C
I want to reduce the first column to unique records, and have the second column listed on the same row. (I'm assuming I'll need Access to do it.) Is there a way to produce a table like this?
1 A B
2 A B C
The final result would need to be something I could put back into Excel.
View 3 Replies
View Related
Feb 23, 2006
Hi guys ,
I'm very new to Access , but I need to do this and I can't figure it out. I have a form that looks like this :
http://img64.imageshack.us/img64/5566/formemail4qz.jpg (http://imageshack.us)
I'm trying to add up all the emails in a single string , but I can't access the records one after the other. The best I can do is display the currently selected record's adress ( The one with the black arrow in front ) . I tried browsing through the Fieldname.Value propertie ( in this case Me.Courriel.Value ) . I though I could cycle through all the Controls in the form , but Me.Controls.Count returns 3 , so I figured all the records must've been embedded into one control. Can anyone help me :( ?
(Sorry if this is a documented question , I've tried searching but couldn't find anything )
View 3 Replies
View Related
Oct 11, 2013
I am designing a bead ornament database for my mom to track inventory of beads, inventory of finished ornaments and cost/pricing. I have a table that joins the Item and bead part with the quantity of each bead part needed, it has the following fields: ItemMatlID, ItemID, BeadPartID, QtySo I have multiple rows of ItemIDs for all the BeadPartID & Qtys.Now I need a query with 1 line per Item and all BeadParts and their quantities. However, I need the BeadPart to be a row data and not a column heading. So a crosstab is out, I believe since it wants to make the BeadPart the column heading and not data in the query.
View 7 Replies
View Related
Aug 29, 2014
I am trying to merge data from multiple rows into one row for each unique ID. I can replicate on how to do this on a table, maybe a query or something like that. Attached img shows
View 3 Replies
View Related
Apr 7, 2015
I am trying to change the data in my rows to columns...I have data as per the attached picture in columns B-I...I would like the rows to be pasted to columns as per columns M-Q...so row 1 ends up as the first lost of data in M-Q and row 2 ends up as the second lot of data.There are numerous rows in my actual file.
View 6 Replies
View Related
Mar 13, 2007
Hi,
Having recently correlated the impossible pile of photos that I have on CD, including a lot of re-naming etc... I have now managed to get them down onto DVD.
I there an easy way to import a directory listing into an access database other than inputing the lot by hand? I don't wan't to add thumbnails just the file name and the directory it's in. I can embelish other details later via ASP, just need the bulk of the work done easily.
I have some experience of database design but not huge amounts of programming, other than the ASP side of things so if anyone has a complex answer please go gently with me!!!
Al
View 4 Replies
View Related
Jun 20, 2006
Hello,
I currently have a DB that has a field in a table that records which shift (A, B, or C) that some data was collected on. I am trying to write a Query in order to filter the results so that either the data from shifts A,B, or C show up, or if "All Shifts" is selected, then all the data shows up. I have another table set up that records the shift selection from a form. I was trying to use an Iif statement but it does not seem to be working for the "All Shifts" option. Selecting "Shift A" or "Shift B" etc. works fine.
IIf([ShiftSelect]="All Shifts","Shift [ABC]",[ShiftSelect])
**[ShiftSelect] containts the users selection for which shift they want to view.
I have also tried this with a wildcard statement (Like "*") instead of "Shift [ABC]", to see if I could get any data to show but I still get nothing when "All Shifts" is selected!
Let me know if you can see the problem, or know another way to go about this query!
View 2 Replies
View Related
Mar 15, 2006
I have a Form named frmClearances that lists the name, age, Club Clearing From, Club Clearing To and the Date of Clearance... this is used for my local sporting Club.
I have created reports that separate names into "Clubs Clearing From" so that each Club is aware of who has gone. The Reports are named...
clrSportingClubName
I have searched this Forum and all over as well, including Tutorials and I cannot understand how I can make a list box that contains the names of the reports so that they can be selected individually when required for each Club.
I copied the SQL for each query and placed it in the RecordSource of each Club Report so that I did not have numerous queries.
I have even copied code from other list boxes to then changed a couple of things but that has been to no avail.
Could somebody please explain to me how I do this? Thank-you very much in advance for any assistance.
View 2 Replies
View Related
Jun 22, 2005
I have a report that i am trying to list in the header section, a listing of all the names for a particular field, if it is in my report. I will use these names to send the report to.
View 1 Replies
View Related
Feb 23, 2006
So I am using 2003. On the report I have created I have this one entry that exists for all my records, it is a Name, and at the bottom of the report I am trying to create a summary that will list each of those names. I already have a count going, so I thought maybe I could tap that, but not every record has a name listed. I am farely inept at coding.
What I want is this:
(Institution 1) Accrediting Institution: ACICS
(Institution 2) Accrediting Institution: ACCST
(The part above is Done, what I need in Below)
(SUMMARY) Accrediting Institutions: ACICS, ACCST
Thanks
View 4 Replies
View Related
Sep 23, 2011
So I have a textbox on a form that contain values. I want to convert it to a ComboBox. Since a lot of these values are the same in a lot of records (for example, "Inventor"), I want the entered values to appear in the ComboBox dropdown so they can just be selected instead of typed in. I converted the textbox to a Combobox by right-clicking on the textbox => Change To => ComboBox. It changed successfully, but I want it to list the values in the dropdown, but it doesn't.
View 1 Replies
View Related
Dec 19, 2005
Hey folks.
I have an Access db needing populated via a worksheet with unknown # rows and known # of columns. Before pulling this data in I must do a row count to determine how many records will be added to the db. Otherwise, it would be a very simple thing to just copy the contents of the worksheet over.
Known:
- Only using DAO.
- The access table is already created matching the column count in the worksheet.
- The spreadsheet will have an unknown row count.
- Some rows in the spreadsheet will have various cells empty.
- A completely empty row will mean EOF.
Need:
- I need to determine how many rows are in the spreadsheet before copying into db.
Problem that occurs from attempt:
- Overflow issue. I seem to be counting every single row on the worksheet, even though only 2 contain data.
Any ideas out there? Pasting below my latest attempt.
Dim strStorage as integer
strStorage = CLng(xlsWST.Rows.Count)
Thanks folks.
-Tethys
View 4 Replies
View Related
May 19, 2014
I have imported 3 different excel worksheets into Access. I then created a query in which I made some relationships and select the fields that I need to make a new table. The problem is, when I run the table the final product is missing about 20,000 rows of data. I have used this protocol successfully for other other files, but for some reason, with this dataset Access is pasting 10,000 rows into the new table, where the original file has 39,000.
View 1 Replies
View Related
Oct 14, 2013
The key field is the specimen number and specimen type. Currently for every antibiotic there is a row with its result. My output is 3 complete worksheets so a lot of data. I need to create a database with one row per specimen number/type to include whether it is S/R/or I for each type of antibiotic. The example probably makes more sense. but I'm assuming that 65000 rows * 3 sheet can then be reduced to around 18,000 rows.
View 4 Replies
View Related
