1) Department + source of Tag+ specific Date
2) Department + Specific Date
3) Source of Tag + Specific Date
4) All records
This query is meeting my 2nd and 3rd condition however if I delete the parameters for 2nd and 3rd 1st combination works fine. But I want all four conditions met in one query
I currently have a combo box (combo121) on a form. I select the "Company" which is connected to "Company" in the query via [Forms]![courseinfo].[combo121]..That is working. When I press the "query" button on the form the selected company in the combo box is updated in the query and it updates the report templates.I also want to use the same system to populate a different report, using the same query. This works a bit differently.
Once the company is selected in the first combo box(combo121). I have other combo boxes which bring up students attached to that company. I have multiple combo boxes because I need select multiple students for the one report. These students are in fields "First Name" and "Last Name". So in the query I can only have those fields once. I have several links to the several combo boxes under "Last Name" as that is the "bound" selection for the combo box:
Problem I am finding is this is bringing up several lines in the query. So when I try to populate the report with all of the different Students I have selected for the query, it won't work properly because I can only put "First Name" & 'Last Name" fields on the report once.
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...
I have an access database which is going to present a front end webpage form. The form has several drop down lists and I need one of those drop down list to display only certain results dependent on what the previous drop down list has selected e.g. if Box A is selected as Aces then I need the next drop down box to only display A building
1-A building 2 A building 3 and not the entire list.
I enter personnel data into a table through a form. The same person may have multiple records in the table. With each record there are yes/no boxes to be checked if that person is verified at a certain station. Let's say PERSON A has six records in the table. Let's say on one of those records he has StaPrimary yes/no box checked (or true). I need a query to do the following: My query is to find out if any person listed in the table does not have any StaPrimary yes/no boxes checked. My dilemma is when I do the query if Person A has six records and one of the records has the StaPrimary yes/no box checked his name will still show up in the query because he has other recrods with the StaPrimary yes/no box not checked. I want to check all of each persons records in the table and if they have one StaPrimary yes/no box checked I do not want there name to show up in the query. How do I do that?
I have a approximately 70 queries in my database. I would like to be able to run a query which would run all of the queries and output the number of records for each query. Ideally, these would then be written to a table so that the user could then just read the values from the table for the latest results, rathe r than have to execute the whole thing again.
The user may wish to select which queries to run. I was thinking that I would need a table as follows called say tblQueryResults:
So my first dilema is to work out how to run all the chosen queries that the user wishes to run. The user will probably have all 70 ticked as Yes initially.
Should I run this from VB code with a whole lot of VB statements. I would like to loop through the whole table and collect a list of all the queries to run based on a positive Yes for some or all of the queries. The results must then go and be written into the same table under the date it was run and the number of records that was found for each query.
The whole reason for doing this is that queries which return no records need not be run by the user - saving the user time etc. I appreciate that this query will take a considerable amount of time - given that it could be as many as 70 being run one after another.
I have a simple report which shows data form a table through a basic query and displays it just as the table view. I was wondering if there is a way to get this information to display in two columns on a page rather than just one list which takes up multiple pages?
I have a question where I need to effectively invert the results of a select query.
I have multiple tables in the database, but the 2 I'm looking at are TBL.Trip and TBL.TripDiary
A trip is logged in the trip table, and then an operator logs a diary entry against the trip. One of the options that the operator selects is when they receive an email back from the traveller, this is logged as a diary entry.
I need the query to look at the DIARY_Action field and select all records in the TBL.Trip that DO NOT CONTAIN an entry for Diary Action "4".
I can produce a select query that gives me the results to see all trips that HAVE had a response, with a simple IS LIKE "4" query in the action field.
If I change this to IS NOT LIKE "4", I get the results that I need, but duplication due to the various other "diary" entries in the table".
I simply need the inverse of the IS LIKE query but cannot see how to remove the duplicates?
Is it possible to fill up multiple (say four or five) textfields from a combo box(or lookup) I looked in previous posts saw something about RecodSource, but couldnt figure it out?
I have a customers form, on which i have a textbox to allow the user to search and show a record by surname. I have got it working, but i do not know how to accomodate the possibility that there might be two members with the same surname, e.g. Smith.(the PK is member ID)here is the code:Private Sub cmdSearch_Click() Dim strMemberRef As String Dim strSearch As String 'Check txtSearch for Null value or Nill Entry first. If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!" Me![txtSearch].SetFocus Exit SubEnd If 'Performs the search using value entered into txtSearch'and checks this against values in strStudentID DoCmd.ShowAllRecords DoCmd.GoToControl ("Surname") DoCmd.FindRecord Me!txtSearch Surname.SetFocus strMemberRef = Surname.Text txtSearch.SetFocus strSearch = txtSearch.Text 'If matching record found sets focus in strStudentID and shows msgbox'and clears search control If strMemberRef = strSearch Then MsgBox "Match Found For: " & strSearch, , "Record Found" Surname.SetFocus txtSearch = "" 'If value not found sets focus back to txtSearch and shows msgbox Else MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", _ , "Invalid Search Criterion!" txtSearch.SetFocus End IfEnd SubCan anyone shed some light as to what I should do? Im looking to have a next button or something to show the rest of the records, or have the search button change to say next if there are more records.Thanks in advance
I have a form and I am not sure of the best way to populate the multiple fields on it.
The form consists of a textbox to select a date (txtDate) and then once this date is selected I want the multiple textboxes on the form to be populated from data I have in a table.
The name of each textbox is a concatenation of "txt" the number of the person which is a number from 1 to 4 and then a time eg for 12 Noon it would be 1200 so the textboxes are from txt10800 to txt42000 i.e. each person has textboxes from 8.00am to 8.00pm in half hour intervals.
I need to use two tables to populate these textboxes as in the main table there are codes that have a relationship to fields within another table that holds attributes for that code. (eg code 123 could equal Service A in the second Table but 123 is what is held on the main table) Also in the main table I have a calculated field that creates the name of the relevant textboxes on the form.
If there is a textbox that will not be populated as there is nothing in that time period then I would like this to be left blank or Null.
Am I best creating a query and binding the form to this query or to leave it unbounded and use a recordset within VBA
Doing Ctrl + ' will put in the value from the cell above it. But I want to do this across 4 columns. On one row, I am selecting across 4 columns and doing Ctrl + ' and only the first cell populates with the value above it.
Is there a way to get the 3 other columns to populate with the value of them (the values are different across all columns).
I am trying to create the form so when the first field (Start Date) has a date selected, it limits the following field (Report Date) to the matching available data, and same with the third field (Production Division) on the previous two.
I also need the form to allow new dates / production division combinations, but not duplicate.
I starting working with a cascade function but got confused and not sure if that is where I should be headed.
I have attached 2 different formats of the same sample data.
Now I have a combo box "cbo_items" and a text box "txt_selectedItems", I want to populate text field with items selected from the combo box, every time i select an item it appears in text box and to be separated with comma, so text box appears like this ( item1, item2, item3), and after finishing the whole thing the result appears in lable "lbl_result" as "total items selected: 3 items".
Im having a real problem in creating a query to return the results that I'm after. I have a wargaming Database. Units are created on seperate forms i.e Armour, Infantry etc. There could be a number of Battalions of each say Armour1, Armour2, Infantry1 Infantry2, the amount is endless. All of these battalions are linked to a game name i.e Battle 1, Battle 2 etc.
Im trying to show in a seperate table the name of each battlion that is involved. At present whaen I create my query I use the Arour Table and the Infantry table but if Ive only created 1 Armour unit and 2 Infantry unit, it shows the record twice. ie:
:confused: I have 3 identical fe be database running each with a table called cars that has identical form values just obviously different data. I want to create a table or query to display all the results from these but whenever i try and make a query i cannot seem to work it out. Anyone got any ideas? :confused:
My SQL is fairly basic so excuse me if this is something simple.
This isn't my database, I am trying to create a report on someone else's and it's a poor structure (sound familiar :-).
If I have created two queries; one of which selects results on students from one table and the other selects results on students from a completely different table. This means I have two Hi,
My SQL ability is fairly basic so excuse me if this is something simple.
This isn't my database, I am trying to create a report on someone else's and it's a poor structure (sound familiar :-).
If I have created two queries; one of which selects results on students from one table and the other selects results on students from a completely different table, how do I combine these results into one query so I can run a report on it?
To outline the situation, I have two different queries with identical data types but from two different tables. I need to query these two queries and (possibly) link in another ‘student information’ table so that I can display the results from both queries as if they all came from the same place.
The addition of the extra table in the final query would only be so I can add extra student data into the final report.
When you put a combo box to search the values in a form what happens if there are more than one (in my case) name that matches the criteria. ie: if you search for Smith and there are 10 people with the surname of Smith it brings up the first record. is there a way of doing adding a "search again' function so you can look for another Smith?
The below isn't the actual use but it is easier to explain like this...
I have a system set up on access and I have a front page (form) where I can enter the reference number (1141#2) of a building (YELDR for example).
This then fills in information in the other fields on the front page. For example address, number of doors and entry codes.
My problem is that this only displays one door and one entry code. I need it to display all doors and all entry codes so that I can just type in my reference number and have something that looks like the below but for all of them. It works fine at the moment but only for one of the results when there can be up to 10.
Reference No. YEDJR <---------------- I ENTER THIS
Address - high Street - London - UK <-------------- All of these fields are populated using the query
Doors - Green - front <-------------- All of these fields are populated using the query - Red - side <-------------- All of these fields are populated using the query - Orange - back <-------------- All of these fields are populated using the query
Codes - 10111 <-------------- All of these fields are populated using the query - 10221 <-------------- All of these fields are populated using the query - 10256 <-------------- All of these fields are populated using the query
I know this is probably nowhere near enough information but is this possible? I don't know SQL so i would have to use the designer.
This is the SQL which I have at the moment though (I didn't write it i just copied this from design view on the query)
Code:
SELECT [Site Info Data].[1141 #2], [Site Info Data].[1141 #2], [Site Info Data].[Site 2 Name], [Site Info Data].freq, [Site Info Data].status, [Site Info Data].[Site 2 Owner], [Site Info Data].[NGR #2], [Site Info Data].[Address #2], [Site Info Data].[Postcode S2], [Site Info Data].[Dish size #2], [Site Info Data].[Dish height #2], [Site Info Data].[Dish Bearing #2], [Site Info Data].RCLO FROM [Site Info Data] WHERE ((([Site Info Data].[1141 #2]) Like [Forms]![Front Page]![txt1141] And ([Site Info Data].[1141 #2]) Like [Forms]![Front Page]![txt1141] And ([Site Info Data].[1141 #2]) Like [Forms]![Front Page]![txt1141]));
I need to find all EIDs where the Code is 611 where an Event# starting with F was Cancelled along with the time of the cancellation. For these results I also need the Arrived Time for the Event# starting with E for that EID.For instance, a result I'm looking for would be:
EID Event# (F) Cancelled Time Event# (E) Arrived Time
I'm attempting to reverse engineer a Cascading Combo Box database into a Fire Extinguisher Audit app.The Cascading Combo Boxes.zip file is found at URL....I've successfully created the tables and forms, along with the appropriate Event Procedures. I've duplicated the one query, but there is no data.Do I have to populate the query with data? If so, why aren't there dropdowns in each cell like the original query? I'm not seeing any other table that contains all the data that the query has.
A search form, to search into those tables (It searches by "User")
The search form contains a listbox that shows results (listPC)
And the following code:
Option Compare Database Dim strUserPC As String, strUserTel As String Dim db As DAO.Database, rsUserPC As DAO.Recordset ', rsUserTel As DAO.Recordset Private Sub txtSearch_LostFocus() strUserPC = "" strUserTel = ""
[Code] .....
It works, but I have one problem. It only shows telephones or desktop, not both. It deppends on which line inside UpdateList is first.
That example searches into 2 tables:
-Telephones -Desktops
And searches by "user".
I want to list all telephones and desktops that a user has assigned.
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.
I’m looking for a formula that will take the last two digits of a “Code”, in Query, and search for the “Range”, in a Table. (Sometimes the “Range” may only have one code in it.) The value returned should of another column. (“Desc”.)
The formula should look at “Code: 123456”, in the Query, take the last to digits in the code “56” and find it in the “Range”, in the Table, returning a value of Supplies.
Im making a database about past bid results. In one table i have a list of bidders and which place they came in (along with some other information). I have 20 positions (1st place, second place and so on...), so i made 20 queries that search through the table for my company's name so we can see how many times we came in first, second, third and so on. Now i would like to bring the number of records in each query to a single form. I tried making another query to using the count feature on the other 20 queries but it keeps returning 0 as the number of results per query. But when i open each query up there are obvisouly more that 0 results in each. Is there a way i can bring up the number of results per query all on one form?
