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??
I have data from a survey with qualitative responses. For a single qualitative question, I moved the ID & responses into a new table and categorized the response according to a bucket/theme, where each column is a new bucket. I now have 10 columns. Each response is represented in 1 or more columns. I used an excel formula to copy the response data into the column itself.
Example:
A1 // B1// C1 // D1// E1//... L1 ID // Response // Cats // Dogs // Elephants //.... Column 10 1 // I like cats // I like cats //(null)//(null)// ... (null)// 2 // I like cats and dogs // I like cats and dogs // I like cats and dogs //(null)//..// 3 // etc.
However, now I'm realizing that Access always wants to show data for all records, or at most I can limit using a WHERE clause in my query.I want to use Access to generate this report:
1. Section 1: Show all responses from the Cats bucket where there is data 2. Section 2: Show all responses from the Dogs bucket where there is data 3. and so on
I know how to do summary values, and I know how to do filtering that apply across the whole report, but this seems like more advanced filtering, where I want to see selective details differently for each field.
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 form that each day needs to be filled in by staff of their activities.
By selecting a date, I want to the textbox to display the contents of the comments memo pad field in the table (tblToday...columns are t_date and t_comments).
My very limited access and previous SQL knowledge has eluded me and cannot fathom how to get the text box to show data based on the date selected?
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.
I have a subform (based on table "Source") that allows the user to create a new record in another table "Events" (it copies some entries to facilitate entering new data). The data should then be dispayed in another subform. However, the newly created redord is does not show in the subform although I use Me.Refresh. When I close the form and then open it again it works - but there has to be an easier way.
How can I reload the data from the table. I have looked but am unable to find the right method.
The code:
'Create New Record in Table Events
Call AddEvent(EventDate, Country) 'This function opens the table and adds a record
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];
I have two combo boxes that exist on a form record (i.e. two combo boxes for every record on the continuous form): mainCategory and subCategory.
Currently, I can choose a mainCategory and depending on the mainCategory, I have certain values available for the subCategory.
What I want to do is only show the values that have not been used for the subCategory (the mainCategory can always show its default value).
An example of what I mean is below...
The comboboxes and their row data:
mainCategory (combobox) ------------- A (combobox rows) B C
subCategory (combobox) ----------- for A: 1, 2, 3 (row for the selected mainCategory) for B: I, II, III, IV for C: red, blue
Sample scenario: 1. For the first record on the form, a user selects "A" from mainCategory and "1" from the subCategory for "A". 2. The user adds a new record to the form 3. For this new (2nd) record on the form, a user selects "A" from mainCategory and can only select "2" or "3" from the subCategory combobox. The "1" from the subCategory combobox is no longer available for choosing, unless the user changes the first record's "1" in subCategory to another value.
The 001: to the first '_' is the important bit this is defining a "Router" what I need to do is total up how many '001:*_' there are, not including duplicate numbers.
Firstly if I understood this (http://www.access-programmers.co.uk/forums/showthread.php?t=92001) post correctly I need to have all the VPIAddress the same. If this is correct is there a way to get access to delete the data after the first '_' ? (I would of posted in this tread but i didnt think that would be right, if im wrong mods move the post :) )
Secondly will the info in that post help me to reduce the data into this :
So that I can then see how many Physical "routers" there are
If any one can help me and tell me that my idea will work then tell me how to do it id be extremely happy!
Even if any one says that wont work and gives me the correct way to do it and does a virtual "Clip around the Head" and call me a Daft Bugga I would still be happy because I wouldn't have to trawl through the entire 16000+ entries deleting and modifying.
I have two one-many related tables: Transactions and Site Details. Most of the Transactions information can be filled in first, and then the Site Details filled in when they are ready.
How do I get a query based on both tables to show those transactions with missing site details? I have tried IsNull(SiteName) in the SiteName field in the Design View, but I don't get any results at all (for records with no SiteName). The answers I've seen in Google seem to be variations on the theme, but I am probably missing something simple (as usual).
I orignally posted this with a question about tracking revision history. I thought it might be a similar solution, since I want to add information to the blank fields when the information becomes available. But I need to find those transactions WITH blank fields first.
Hi Was wondering if someone can guide me in the right direction. this is my first post.
I have a form with a combo box listing 900 pupils but the list is in no particular order. How can I sort the surnames in alphabetical order and for those changes to reflect on my form.
The problem I am having is I can sort it out in alphabetic order but the changes dont reflect onto the main form.
I have a subform for a hotel list, I have a combobox on the subform that show the list of the hotels, what I need is to create some fields on the subform wich shows me all the details of the hotel as address, email, facilities once I have selected a hotel from the combo list. How can I achieve this! Thanks Marco
I am trying to do a simple Do-while loop to display master and relevant transactions in form/subform environment under Access 2003.Below are some of my statements:
Dim dbs As DAO.Database Dim TRANS1, TRANS2 As DAO.Recordset Set TRANS1 = CurrentDb.OpenRecordset("Master", dbOpenDynaset) Set TRANS2 = CurrentDb.OpenRecordset("Transaction", dbOpenDynaset)
[code]....
then I ha e a main and subform creation with at least the variable "text456" to ensure the master transaction code (i.e. the transcode) has been located and are in the main form. In the subform that I have created, I have a condition as [forms]![mainform].[text456] under the "transcode" field in relevant query file. I have also checked that in the property of subform, both the child and parent linkages are stored the "transcode" variable.
But when I run the program, it only show the "transcode" in main form but no data showing in the subform.
I have a main report with several subreports. Some subreports have other nested subreports. all of the reports and subreports are based on queries. when a query returns no values, i would still like for the reports and subreports to display in print preview. (currently, they display in report view but not in print preview.)
I tried a few things:
1) I set all report section can shrink options to "No" - (this did not work)
2) I set the can shrink options for all textbox controls, within each report section , to "No" - (this did not work)
3) I moved all controls from the details section of the subreport to the report header section - (when I did this, I got the subreport to show in print preview when the query for a single subreport returns no values but when i have a subreport with nested subreports that have no value, none of them show up in print preview)
I've searched...moving the controls from the details to the report header was the best thing i could find, but again, this only works with a single subreport and not with multiple nested subreports.
I want the report not to show data with zero balances.
Example: if my report has 10 line items and 3 of these line items have 0 balances, then the report should only show the 7 items that do not have 0 balances. (the report should use up 7 lines / rows only.)
See the attached report for a better understanding...
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)
