I ahve a table called occupation with 2 fields, one is ID (autonumber) the othet is occupation name.
I have another table with job info from sun to mon - with 7 fields for occupation lookup. e.g. monoccup, tueoccup etc.
I have a query and I pulled these fields into it, and the occupation names showed up, then I tried to build an expression and th output should have been the occupation name, but I'm getting the occupation ID.
The join shows occupation id from the occupation table linking to all 7 fields in the job table.
How can I get the expression to output name instead of ID?
Whenever I export reports to PDF, the output appears zoomed and clipped. No extra pages are generated as they would be if I'm going over margins, it's just the report is clipped.
The report looks perfect in preview mode, and it looks perfect when going to an actual printer. However, when using OutputTo to save it as a PDF, this is when the report content is clipped.
I open the report in preview mode first so events are fired that show/hide various objects based on fields in the recordset.
I've tried reinstalling, and I've tried this on two different machines, one running Windows 7 and one running Windows Server 2008...both with the same results.
I have a simple query to calcualte a profit margin on daily sales lines and I use a quick and dirty expression to calculate the margin in the query so I never need to drill it down further than that level (I don't want to go as far as putting the output into a report as it is only for use when double checking lines for errors which get fixed there and then in the database).
So far so good, however the margin output is a bit awkward to read as I can't seem to format it as a simple percentage. The field properties page doesn't like doing anything with the expression and even typing in a format manually has no effect, so I end up with figures like
Is there any way to format this output to show only 1-2 decimal places and be in a proper number format so I can sort them in ascending order properly?
I am in need of assistance with Microsoft Access. In the reporting function of Access I am trying to have a summarization total of indirect time and direct time based off a Auditor Timesheet form developed. On the report I am able to see both the direct time and the indirect time hours seperate per auditor based on the query that is run to seperate direct and indirect hours and each individual auditor stats.
but the problem I am having is trying to summarize on the report the total direct and indirect hours as a whole.
Please let me know if this a simple solution by building an expression in the field for the report or some type of VBA code input which would suffice?
I am trying to get the output of several nested IIF expressions to show in only one filed in the table created. The output needs to be captured in a table. I am staring with create table to get it to work before I go ahead and use the sql to update the original table (the table the data is from)
I can get each to run fine independently and the whole thing runs too, the issue is it wants to put each expression in a new column, thus it is forcing me to label the expression.
Can I change this to only output in one column or add some more sql to do so?
Here is the SQL (table and filed names have been changed to protect the innocent)
BTW, I cannot change the Table the data comes from, I am stuck with the single table structure (BOO).
************************************************** ************* SELECT TABLE1.ID, IIf([TABLE1]![STAGE]="D", IIf([TABLE1]![PROJ_ACTUAL_D_DATE] Is Not Null,"Tier 1", IIf([TABLE1]![APPROVAL_D]="Y","Tier 2",
Including check boxes (representing the query fields) in an interactive access form, in order to decide which fields should be visible.
I think the solution is building an invent in VBA for each check box, however I'm not an expert in Access VBA and don't know how to write the code.
In the example that I've uploaded, in the Form1, when I run the query, all fields are displayed, i.e. VENDOR, REGION, CUSTOMER and MATERIAL are displayed. How can I manage it in the form with a listbox to display only the REGION or MATERIAL for instance.
I have a list of all previous employers for a job applicant. I can enter up to 10 but I only want the boxes to show if there is a previous position for that box. So, if there are 3 previous positions, I want to see 3 lines. If there are 10, I want to see 10. I have fields [Company_1]. [Company_2], etc. I only want company 2 to show if the person has 2 previous positions entered into the form.
I am working on a school project and am stuck on the last part. I need to produce a query for use as a report. The fields in the query are:
Building Teacher 1 Teacher 2 Teacher 3 Teacher 4
Archive 1 Archive 2 Archive 3 Archive 4
"building" is the search parameter for the query
I need to produce a list of names of teachers, if there is nothing entered into the corresponding archive number e.g. no data in archive 1 and 3 , so show teacher 1 and 3.
Below is the SQL I have on a query. This query is made up of 4 other queries. If any of the fields = zero how can I make a zero appear? Right now the field just shows blank if it is equal to zero. I really want the zero to appear on a form that I have created from the query. Thanks for all help.
SELECT MonthlyEcnReceived.Received, MonthlyEcnImplemented.Implemented, [MonthlyEcnW/Structure].[W/Structure], [MonthlyEcnW/StructureImplemented].Implemented FROM MonthlyEcnReceived, [MonthlyEcnW/Structure], MonthlyEcnImplemented, [MonthlyEcnW/StructureImplemented];
Using expression builder what expression do I use to not display the filed in my query if the field is formatted as currency and is blank (showing £00.00)
I need to write a query that includes the values of 2 fields from the following record. e.g
index Name Total Name2 Total2 1 Dave 8 Steve 4 2 Steve 4 Mike 6 3 Mike 6 Pete 4 4 Pete 4
I can get a partial success when I use the DLOOKUP function along with a sequential indexed field but I am working with 10,000s of records and any subsequent query based on the results of this takes an absolute age to run.
I am trying to find out how to view the field names for variables while in datasheet view in Access. Or, alternatively, I would like to know how to toggle between datasheet and design view for a particular variable. Basically, I have a huge database and I need to be able to find the field name for a variable that I have identified in datasheet view, but right now all I know to do is go into design view and guess which field name it is, click on it, see if the caption matches what I see in datasheet view, and keep doing this until I find the match. I did not create this database which is why I am not familiar enough to know what goes with what.
I have a field in my database which holds the Image URL in text format for each items picture. Is there a way to make this column show the actual image, instead of the URL?
I have the following table: Blast NoHole NumberHole Depth TapeBCM/MORE mORE bcm 1.002007.909.757.9077.03 1.002008.209.758.2079.95 1.002018.009.750.000.00 1.002018.109.750.000.00 1.002027.809.757.8076.05 1.002037.209.757.2070.20 1.002047.909.757.9077.03 1.002057.709.757.7075.08 1.002062.809.752.8027.30 1.002077.409.757.4072.15 1.002087.809.757.8076.05
And i want to show only the last occurance of the Hole Number field. e.g:
Blast NoHole NumberHole Depth TapeBCM/MORE mORE bcm
However I cannot figure out if the LAST function dose this. When i have applied it hole 202 & one of the 200's dissapeared. However 202 should not, but 201 should??
Hi, I have two tables: tblOutTransmittals and tblTransmittedDocs. A document is sent with a transmittal document which the recipient signs to confirm that they received it. I've created a query that will show all the drawings/documents and the information about the associated transmittals.
What I want to do is show display records with a unique CCNum and preferably the one with the most recent TransmittalDate.
I've attached a screenshot and here is the sql: SELECT tblOutTransmittals.CCNum, tblTransmittedDocs.DrawingNum, tblTransmittedDocs.RevisionNum,tblOutTransmittals. * FROM tblOutTransmittals INNER JOIN tblTransmittedDocs ON tblOutTransmittals.DocID = tblTransmittedDocs.outTransmittalID WHERE tblTransmittedDocs.DrawingNum="32-35554"
I've tried it using the DISTINCT keyword but that would only work if I'm returning one field. I tried Group By as well but couldn't get that to work.
How do you display the names of field for a table in a combo box. I know how to do it in mySQL "DESCRIBE employee_data", is there a SQL command in access to do this?
How do I easy create a checkbox that displayes a field when I check it. For example, if I check a box named I agree, it shows a field with a phonenumber to call
How can i make a query which shows the most used data in a field, i saw how i can show the biggest or smallest but i didn't see how many times is each data used in a field.
My field is for years, and i wanna see each year and how many times it is used.
I have a database about certain accounting datas from week by week and it's growing. I should make a Crosstab query for see the amounts weekly, it's will be exported to an excel workbook, wich have macro's(this is the problem, because the exported field will be bigger week by week). The difficulty of this query is the future weeks. I want to see all of the weeks in the columns. I made a table wich contains the weeks(Hetek_1.CW) wich I want to see, and the source is also contains the items accounting weeks.
Code: TRANSFORM Sum(CWall_hetekkel.[Knyv# ssz# kltsg]) AS [SumOfKnyv# ssz# kltsg] SELECT CWall_hetekkel.[Ktgh# kdja], Oka.Oka FROM Oka RIGHT JOIN CWall_hetekkel ON Oka.Oka = CWall_hetekkel.Oka WHERE (((CWall_hetekkel.[Ktgh# kdja])=1250 Or (CWall_hetekkel.[Ktgh# kdja])=1251 Or (CWall_hetekkel.[Ktgh# kdja])=1252 Or (CWall_hetekkel.[Ktgh# kdja])=1253)) GROUP BY CWall_hetekkel.[Ktgh# kdja], Oka.Oka PIVOT CWall_hetekkel.CW;
I have txt input [txt choice] which has a number (1-9) and then a query which uses the following formula to define which records are visible linked to a Yes/No field
Code: =IIf([Forms]![frm selection]![txt choice]="4",Yes,Yes or No)
If the choice is "4" then it works fine, but if the choice is anything but 4 it still shows only the Yes items. If i change the code to
In my table I have Picture (OLE) and Link_String (Text) On my Form I have the OLE field and the text field.
In the text field I would like to put in a reference to a file on my computer (example: P:PicturesTest.jpg) and the picture OLE field should then show the linked to picture.
In forms, I frequenty use the following expression to get the results needed from a field's column: =EmployeeID.column(1).
However, when I try to use the column function in a query it doesn't like it: EmployeeName:[EmployeeID].Column(1)
What am I doing something wrong?
Along the same line, if I want to use =EmployeeID.column(1) for an unbound control in a form, why must you put the bound field on the form just to get the info? In this case, I want to do this to display the employee's name - not the primary key - without the scroll bar. Every time I do this, Access performance analyser tells me to use fewer controls, but you can't if you have to have the reference. The same thing seems to be required in queries, i.e. include the unaltered control to get an expression.
Help and advise, as always, will be appreciated! Christine
