I have a Volunteer Database that has teams. There is only one team leader but some of the teams have two or three assistants. Currently I have a query that lists all the assistants for each team vertically.What I want to do is list the Assistants horizontally under headings like Assistant 1; Assistant 2; and Assistant 3 for each team on one line.
View RepliesHere is how my table is set up:
System1 Subsystem1
System1 Subsystem2
System1 Subsystem3
I need a query that puts the data into one record, different cells:
System1 Subsystem1 Subsystem2 Subsystem3
Can this be done?
Thanks,
MJ
Hi, In MS Access,
In the form design,
How can I change the Vertical Scroll bar color?
I want to keep it for using, but the color is always grey color.
How can I change the color of the scroll bar?
Thanks.
Hi there, I got a little data transformation problem:
I have got a source table:
ID 1 2 3
1 x y z
2 xx yy zz
That I wanna transform to the following format:
ID ID2 Comment
1 1 x
1 2 y
1 3 z
2 1 xx
etc...
The problem is, that I need a routine that takes the column names of table 1 and uses them as values in table 2. So basically, every column of table 1 (1,2,3, etc.) should become a new data record in table 2.
Any ideas how to solve this?
Thanks!
Steve
bob@smith.com, 2010-01-10
bob@smith.com, 2010-02-10
bob@smith.com, 2010-03-10
bob@smith.com, 2010-04-10
ronda@q.com, 1984-01-02
ronda@q.com, 1990-04-02
ronda@q.com, 1996-08-02
want above data to appear as
bob@smith.com, 2010-01-10, 2010-02-10, 2010-03-10, 2010-03-10
ronda@q.com, 1989-01-02, 2010-04-10, 1984-01-02, 1990-04-02, 1996-08-02
How to Creating a Three-Level Navigation Form, First and second level must be in horizontal orientation, third level desirable be to vertical orientation and dependent of level 2.
View 1 Replies View RelatedI have a form which I would like to enable scrollbars both horizontally and vertically (the main reason being, the application is designed for users with large / wide screens but may at some point be required on smaller screens)
Currently I have the scrollbars switched off and the form maximises but everything goes to pot if I resize the window. I'd like to add the scrollbars so if a user wants to resize the window to, say, half the screen, they can still scroll across to the 'hidden' parts of the form.
The problem with this is, if the form is maximised - or at least large enough to show the entire form - the area of the screen where the horizontal scrollbar resides is white (whereas the background colour of the form is a pale green) Which is quite unsightly.
I know I can remove the problem by removing the horizontal scrollbar and the navigation bar (which is already gone!) but then I can't scroll?
Is it possible to have a horizontal scrollbar but not have that white space at the bottom when the scrollbar is not required?
(Doesn't appear to be a problem with the vertical scrollbar?...)
I need to replicate an existing report that has a number of 3 columns across the page and has 35-40 rows down the page...it's like a table in Word or a spreadsheet in Excel. I need to print the column and row lines.
Is there a simple way to include these in the report other than me drawing each and every line?
I have a series of Unbound control boxes in one row. some text extend to two lines and I want everything to be centered both horizontally and vertically.
Can't figure out how to vertically align the contents of the controlb boxes...
I have a text label that I would like to have vertical instead of horizontal. In the properties box, I go to the other tab and set 'Vertical' to yes and the text rotates. Except it rotates 90 degrees clockwise. (The text starts at the upper right corner and reads down, you have to tilt your head to the right to read it.) This seems the wrong direction to me and to my office mate - all the tables we've seen with vertical text has the text rotated 90 degrees counterclockwise (text starting from the lower left corner and reads up, you have to tilt you head to the left to read it.)
Is there any way I can make the text rotate counterclockwise instead of clockwise? I really don't want to create many little graphics.
Thanks
Not sure if this is possible but I have a few text boxes on a report set to vertical yes under properties. They print fine, but on the pre-printed forms I'm using they print upside down. Is there a way to realign they way the text prints when using the vertical yes setting? I really need they text to be inverted. I'm I asking an impossible question? Thanks..
View 1 Replies View RelatedIs there a way to create a query that will transpose table columns into rows:
Genes Cytoband
xxxx yyyyyy
xxx yyyyyy
xxx yyyyyy
xxxxxx yyyyyy
xxxx yyyyyy
xxxxxxx yyyyyyy
xxxxxxx yyyyy
xxx yyyyyy
xxxx yyyyy
xxxxxxx yyyyyyy
xxxx yyyy
xxxxxx yyyyyy
xxxx yyyyyy
xxxxxxx yyyyyy
Is there a way to display the table in a single row separated by commas:
xxxx yyyyy, xxxx yyyyyyy, xxxxxxx yyyyyyyy, xxxxx yyyyyyy
I am trying to generate a simple report that can be imported to excel for analysis. Currently the database documents failure causes associated with a repair. I need a "Report" that does not duplicate the failed items in the event there is more than one cause associated with the failure. some repaired items may have up to 3 causes of failure. What I would like to do is have the query show one record instead of three records (for the cases were three causes of failure were identified for a repair)
Current query format
Code:
facilityID Failure Cause
1 Electrical Surge
1 Mechanical Wear
1 Corrosion
2 ......
2 ......
.... ......
Desired query format
Code:
FacilityID Failure Cause(s)
1 Electrical surge, Mechanical wear, Corrosion
2 .....
.... .....
I was reading about a SQL transpose call but I do not need all fields transposed (only the failure causes transposed and concatenated into one field).
Test database attached with a query that shows the data I want to transform into the desired format.
I am fairly new to developing with Access.
I would really value some advice on changing text direction in crosstab query column labels.
I have just created a crosstab query to function as a training matrix. It has rather long column labels (there are good practical reasons for this), which makes the query very wide and difficult to print one one page.
Is there a way to change the text direction of the column headings so they are printed running up the page?
I know how to change text direction in Word or Excel, but not Access.
At the moment I am copying the query results into Excel and manipulating the text direction in Excel, but this is rather cumbersome.
Thanks in anticipation of any help. :cool:
I have a table with below fields and content:
container_nr_no Type time
AAAA1233456 210 12:30
BBBB1222234 45g1 13:30
And I would like to generate a output text file like below:
FLD00101=1
FLD00102=AAAA1233456
FLD00103=210
FLD00104=1230
FLD00201=2
FLD00202=BBBB1222234
FLD00203=45G1
FLD00205=1330
FDL00.... should be generated automatically, starting with 101 for 1st container and 201 for 2nd and so forth. The output should be in vertical manner.
Query of daily activities spent hours
1) to be group as weekly total
2) then need to transpose it for reporting.
My table fields are Date, SpentHrs and Code (activity code description).
I have a query which uses values in two hidden text boxes, in order to populate a sub form.Unfortunately some times the data in the text box contains brackets within it as follows:
'120/60 ZR17 (55W)'
When this occurs the query returns no data, even though records with a matching code exists in the table I am working with where Speed is 'FR'.
Code:
WHERE (((stockdyn.SPEED)="FR") AND ((stockdyn.DESCRIPN) Like '*' & [forms]![frmSearch]![Text10] & '*'))
How can I get around this, as surely if I used quote marks it would take "[forms]![frmSearch]![Text10]" as the value I am searching for.
I have a text field that contains a comma delimited list of numbers.
I am trying to read one of these numbers from OLEDB through to an access database.
I am trying to do the following:
SELECT SPLIT(CSVData, ValueNo) as ReturnValue FROM MyCSVDataTable
Where CSVData is a split field containing values like "123,234,345,..." and ValueNo is a value to identify which one i want.
Any way of achieving this using the standard SQL functions. I am reading the data via OleDB and therefore cannot use a user defined VBA function?
I have a question regarding counting of text values base on their status and using that result to a calculation.
Say, I have a table of Demand of Positions, wherein, I have a specific Job Title for a certain Department that have number of workers needed (demand quantity) and a table of candidates for that job title and their status, say, Arrived, Visa Processing, Visa Applied, Visa Issued, and With Ticket.
What I would want is to make a summary out of the two tables, where the query will count how many candidates are there in that specific job title and have a field of status say, field of Count of Arrived, Count of Visa Processing and etc., and a field where I can add all of the count of candidates per status and deduct the result to the demand quantity where that field would be named Balance.
The problem is that the status varies on every candidate on that specific job title because the status field is used to track the progress of each candidate and this scenario will make the query blank because there would be no such record due to their status.
I tried making a summary following my requirement and you will see that in the attached file together with the SQL code of that query that the balance field value is blank.
I have one field where string contains several words separated by semicolon and my goal is to be able to remove duplicates within the string and keep only unique values. Here is an example:
initial field:
xxx;yyy;ppp;yyy
targeted result:
xxx;yyy;ppp
How this could be achieved ?
I am currently developing a database to provide a friend with an auction tracking and ordering system.
I will have a number of questions the first is related to the Home Page/Dashboard/Summary form I am creating. I basically have a few sections, one of which is a combo box offering the user to select an auction to view in the summary section. This summary section contains the list of lots but I also hope to to expand on this and create multiple text boxes, each containing the answer to a number of queries (totals etc).
I have written all the queries and can see the results however as the form does not have a record source I would like to know how to make each text box populate with the result of different queries (ideally in vb - I am using ms access 2010)?
I have tried a few things, control source doesn't apply as I have no record source (i guess). I investigated Dlookup/Dcount but am unsure if these apply for the same reason. I understand I am likely to need a recordset etc in vb and have already tried a few things but unfortunately none of them work.
In Access 2010 I have a Data Entry Form on which I have an unbound textbox in the header that the user can put a default date in. In the body of the form is a bound textbox that records the date and the default value is set as =defaultdatestat (obviously the name of the box in the header).
Problem: The default date shows up perfectly until a value is put in any of the other text boxes.
For further info : If you put values in text boxes default value disappears; if you then push escape the default value reappears when the values in the text boxes disappear.
I have a table with one field and 83 records. I need to transpose this one column to 83 columns, so I would have a table with 83 columns and one row…. Is there any way to do this?
Appreciate
Maryam
Hi - Question number two..
Is it possible to transpose the subform data set. I have a subform which lists alleles. There are a variable number of alleles in the subform, which is linked to a second subform. Each allele has a size and a frequency.
It is listed in a tabular format, but the default is top to bottom layout. Is there anyway to display this left to right?
Cheers,
I have a table with one Field and 83 records. I need to transpose this one column to 83 columns, so I would have a table with 83 columns and one row…. Is there any way to do this?
Appreciate
Maryam
Hi,
I've attached a small JPEG file showing some query results and some data transposing I'm trying to work inside of Access. The top box shows the how my query results currently come up. The lower box shows how I would like to transpose this data so it can easily be exported to other things. I can handle the export part, but I am having trouble with getting the results to fit into my desired format while still in Access. I don't want to convert it outside of Access b/c that would add another step of things to do.
Any ideas would be appreaciated..
Thanks