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.
How can i get this to work?
Hi All,
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:
QueryToRun - Yes/No - DateRun - NumberOfRecords
Query1 - Yes - -
Query2 - Yes - -
...
...
...
Query70 - Yes - -
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.
Thanks,
Evan
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?
I am working on a report that has some special characteristics.
Let's say I have a list of groups of Vendors in a table, complete with VendorID. I have 3 other tables that use the VendorID: Complaints, Complements, and Terminations.
Each of these tables has a date that the Complaint, Complement, and Termination notice was received.
Every Fiscal Quarter, a report is pulled that looks back over the 4 preceding quarters to determine if a 5% threshold has been crossed by any of the vendor-groups in regards to the amount of Complaints they received.
The equation used for that is : (complaints/vendors_in_group)*100
It is imperative that the information has the current fiscal year and fiscal month (which I am tracking with functions from MS website), and I need to be able to store the information attached to the fiscal year and month.So when a user goes to the form and inputs the desired Fiscal Year and Fiscal Month, the database can display the 4 previous quarters of information...split into Q1, Q2, Q3, and Q4.
What I would like to have happen is to be able to have one table where the information is stored, quarterly, so that it can be retrieved for the report.
Questions:
1. Is it possible to have one line, per VendorID, that has the total number of Complements, Complaints, and Terminations, as well as the threshold percent stored in a table? Right now, I am getting LOTS of duplicates and blank lines when I try to put them all together. It has the right data, but takes about 10 rows per VendorID.
2. It is very important that the total number of Vendors in a group be captured on that quarterly report, so maintaining that number, in the same table, is essential and must be tied to the VendorID.
3. I have looked at Union Queries and Crosstabs, but I just dont know enough about them to make it work.
Hi,
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.
Hope this makes sense!
Thanks,
Matt.
different queries with identical data types
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]));
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?
Thank you
Dan
Hi,
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 am using the query below
What I am trying to do is get 4 combinations
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
SELECT [Unsafe Act Unsafe Condition].*, [Unsafe Act Unsafe Condition].Department,
[Unsafe Act Unsafe Condition].Date, [Unsafe Act Unsafe Condition].[Source Of Tag],
[Unsafe Act Unsafe Condition].[Close Date]
FROM [Unsafe Act Unsafe Condition]
WHERE ((([Unsafe Act Unsafe Condition].Department)=[Forms]![frmHighLevelReport]![cboDepartment])
[Code] .....
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?
Thanks for your help
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?
View 6 Replies View RelatedI 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:
[Forms]![courseinfo].[comboname1]
[Forms]![courseinfo].[comboname2]
[Forms]![courseinfo].[comboname3]
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 am trying to do the good 'ol sales report (query) to include customers with no sales.
I have a customers table, account number table, sales table & sales (line) detail table. (all linked in that order)
If I run a query to show customers (in the customer table) with account numbers, that works
An unmatched query to show customer without an account number works (but of course the unmatched account number field isn't shown).
How can I get the two two be shown together with the "unmatched" having a null or 0 for their account number?
I am guessing in principle, the resulting solution can be modified to show customers without sales alongside those with sales?
I run a physical therapy office and patients come in for treatment either 3, 4 or 5 times per week. My database is used to track these frequencies (among other things).
I have 3 queries which count how many patients come in 5, 4 and 3 times/week.
In my main table I have fields called "how many 5's", "how many 4's" and "how many 3's".
I have tried to design an update query which will update those fileds in my main table to reflect the counts in the 3 queries mentioned above.
(I'm not using SQL view, I'm using the query design view)
In the "update to:" row, I use the Build function and locate the count I'm looking for.
Problem: when I run the query I get the error: Operation must use an updateable query.
I have a query that is showing two results for one product and I have no clue why. I have my tables in a one to many relationship and if I click on the + it shows the correct data for the product in question. Im guessing I have my table set up wrong its the only thing I can think of.
The table tbl_Carton has two entrys for a product (Flex Tape 2-1/16") (Product_PKEY #21) mabe I have it set up wrong? If I run my (qry_Switchboard ) and enter (21) its giving me an extra result for each of the two results thats suposed to be there.why?
I'm having a mental block on this one.
I have a table with a yes/no field. It's linked to a form with a yes/no box, but the outcome I want is that if the form box is unchecked, the query returns all the "yes" records - which is trivial; however I want to return ALL the records if the form box is checked.
In Access 2007, I put into the table, in a certain field, a certain word field, so I put into design view for that query, in that field, Like "Field" and even though the word field is in that table in that field, it doesn't show it in that particular query?
View 14 Replies View RelatedI need to do a calculation on the RESULTS of a query. Each record in the table I'm querying has a date stamp. I have a query that produces a list of the newest records for each machine. I want to calculate how old each of those records is based on today's date.
So far when I try to include the calculation in that query, it returns all the records instead of the newest ones. This makes me think I'm doing something fundamentally incorrect. The calculation is affecting the query results where I just want to do the calculation on the results. What I'm trying to ultimately do is have a way for the users to see a list of machines that are overdue for a certain procedure. I was thinking of putting a button on a form that will run the query and present the list, perhaps with some conditional formatting highlighting the machines that are overdue, etc. Do I need to just use 2 queries?
Have created a simple data collecting database with a simple query to narrow down some of the data , the DB all works fine and some queries are ok, but one the simple query where I want to look a one single set of data.Using the Like "*"&[Enter Search Parameter]&"*" gives me no results.
View 9 Replies View RelatedI have created a booking system for a travel agency and now when the booking is complete I want to export some summary data to the Excel. The issue is that I have separate tables showing lunch, tickets, tour guides, etc for every destination and I have also created queries showing the total for each destination but the issue is that I cannot combine all the results into one query and export that query to excel.
View 4 Replies View RelatedThe results of my union query of 2 separate tables looks like:
Name - Count of Participations
Joe Blow - 7
Harry Smith - 11
Kate Upshaw - 8
Joe Blow - 3
Harry Smith - 5
Kate Upshaw - 13
I need to combine the duplicate names and total their participations to make a report look like:
Name - Count of Total Participations
Joe Blow - 10
Harry Smith - 16
Kate Upshaw - 21
i have created a parameter query which will find a students best and worst time for each exercise they have done. so you enter the student ID when you run the query and it works fine but i have a problem i need to query to find two students in particular and then i have to create a report from this query on the two students identified and this is where i come up stuck. i have tried typing in both student id's into the criteria but this doesnt work no matter if i put and in it or not and im not sure how to get the job done.
View 7 Replies View RelatedThe Query
'SELECT Count(clubbox) AS MTSingles
FROM moves WHERE (((moves.cmrdate)>[Forms]![CPanel]![Text44] And (moves.cmrdate)<[Forms]![CPanel]![Text46]) AND ((moves.driverid)=[Forms]![CPanel]![Text38]) AND ((moves.move)=35) AND ((moves.clubbox)=32));'
returns about 60 records, how do i assign the results to a tempvars
We have a need to produce hex and ascii in the same results field.
The field with data will be bound to a 2D barcode (the square ones you see everywhere now). The format of the data is to look like this:
[)><RS>06<GS>xyz<RS><EOT>
The <RS>, <GS>, and <EOT> all need to be hex values as follows:
<RS> = Hex 1E, Decimal 30
<GS> = Hex 1D, Decimal 29
<EOT> = Hex 04, Decimal 04
When I run my Access query as follows:
BarCode: "[)>"&Hex(30)&"06"&Hex(29)&"xyz"&Hex(30)&""&Hex(04)& ""
It produces the following:
[)>1E061Dxyz1E04
So, it just converts it to ASCII; however, when I go to scan it, it actually reads that information also. I need the 1E, 1D, and 04 to actually be scanned as HEX for the validation to occur for our client.
Is it possible using an Access query to return these desired results? If not, I will have to look atother piece of software to produce the bar code labels.
From a Access database that I inherited. Users used to be able (from a form) run a query and then add data (i.e, new rows). But now that the the database is split and the backend is on MS-SQL, they no longer can do this. I do not think this will be possible. I think they will have to add data directly to the table or have another form for adding data.
View 3 Replies View RelatedSales will make calls and record data using a form (frmEnquirySpeak) which will save to tblSpeak. If the call results in a meeting being booked then it also updates tblMeeting with a date of the meeting (simple stuff so far!).
A sales person might record up to 14 types of action per day, qrySpeakPerAction counts the different types of call made on a day by day basis for reporting. qrySpeakGroupedAction summarises this down to 8 categories and applies a date filter selected on a reporting form (and this works well).
I now want to include the qryMeetingsBookedPerDay (counts m_date_raised and groups by m_date_raised...eg 2 on 24/12/14, 4 on 28/12/14 etc etc) with the qrySpeakGroupedAction...
My effort so far has resulted in only dates which appear on BOTH queries showing. This may not always be the case.
Every day calls will be made, this may or may not result in a meeting. So I see there being 2 options:
1 - Query to show the count of calls on a date or date range and number of meetings booked each day that calls are made (prefer this method, it sounds simpler)
2 - qryMeetingsBookedPerDay should auto populate days which dont appear as 0 (this sounds messy and not sure what capabilities/speed are like when managers want to spot patterns/trends over multiple years?)