I have several fields that have a drop down list. Say the Field is called "Age" and I have choices in the drop down menu as 1, 2, 3, and 4. How do I create a query that would accept input from the user to show 2 of the 4 or 1 of the 4 or all of the choices using one query that has checkboxes, and possibly using a form to do so. I have a form that has a list of all entries. Now I just need to make it able to filter based on user input.
Say I have checkboxes for 1, 2, 3, 4, and all choices. Can a list filter only what I check? Can I create a form that would have all the choices on it and just click a button that says "submit" and have it filter, or print the form. How do you get the checkboxes to filter when checked, but not when unchecked? Where does Access store the true/false, on/off, yes/no responces that come from the checkbox? Is there a variable that gets set to a 1 or a 0 based on the status of the box?
I am building a database which tracks tasks assigned to employees. I want to have one of my forms displaying a view which shows at a glance which tasks are assigned to whom at what times of the day, with a click opening a task for editing. My current attempt is to create a crosstab query with the employees as the column headings ("dynamic crosstab" query) and a table of time values for the hours of the day providing the row headings, with the task as the value.
I then created a form based on the query with Datasheet view as the default view (apparently necessary to set properties/event handlers to query values. I got the idea from the Northwind sample database) However, the form fields do not update to reflect changes to the employee list and therefore the onClick handlers are not applied to newly added employees. What to do, besides for programmatically setting the event handlers?
i have a database that imports files with dynamic amounts of fields, runs querys on the data and outputs it to a speadsheet. The The problem is instead of making it run querys on each column, i have it running on them all collectively. Now i want to change it so a query is run on each column then output to its own spreadsheet. Does anyone know how to do this?
I've got a database that I need to import a vacation days table from an excel sheet. The biggest deal with it all is that the excel file & range it needs to search for the data is dynamic, actually I also need to be able to scan different sheets within the excel file.
Now, im not new to all this stuff, ive done all sorts of complicated excel manipulation in VBA as well as queries and custom processing, but I've been away from it all for roughly 8 mo. so im rusty.
If anyone has a suggestion about how i could go about loading the excel file (dynamically) and pulling information from it (i will be doing aggrigates like SUM & Count, however i can program these manually if need be.)
I have a form that shows a list of all of my records in my database. I want to be able to click a button called "Report" and have that print a report that has all the records I have filtered on my form. I have a report in the format that i want it in, however, currently it prints every record and not just what is shown on my form. (The form is dynamic and I want the Report to be dynamically based on the form) HELP PLEASE!
There are a number of what I call reference tables. These are fairly static and in the main will not change.
There are a further half a dozen tables all linked together.
The system basically takes an enquiry which may lead to a repair taking place. The repair will have a number of jobs and each job may have parts replaced.
What I want to do is have an identical database which to begin with will have exact copies of all the reference tables. The remaining half a dozen tables initially will be empty but grow with time.
The user wants to be able to copy the completed repairs he selects over to the 2nd database (which is initially empty). As weeks go by he wants to select further completed jobs that have not already been copied over to the 2nd database. The 2nd database will be updated so I can't just copy over all completed repairs each time.
I had envisage him doing this by using a front end to the 2 databases and firstly selecting which of the completed jobs which haven't been copied over. This is a fairly straight forward query that will update a field on the repair table.
Once he has done this I was thinking that I could run a query that would pull all the rows from the 6 tables from database 1 and update the corresponding tables/rows on the 2nd database.
I don't actually know how to approach that. The process needs to be simple for the user. ie 1. select repairs to copy over 2. run something to copy them over.
Is it possible to have the user enter the number of top records to choose in a query?
I know that you can enter a number or percentage in the Top Values property of the query but I would like the user to be able to enter this number each time they run the query since it may be different each time and not always, say, the top 10 records.
I need to do a DSum with criteria that picks up a previous month based on todays date, I have got the following but I don't know how to format the last part of the criteria.
If I take out the Month(DateAdd("m",-13,(Date()))) and replace it with 6 it works fine.
Sometimes i have a problem with dynamical queries. For some of these i need to put in the same parameter value two times, before it works. Why this occurs?
I have a dynamic cross tab query - thus the column headings will change each time it is run.
At present the column headings are displayed in alphabetical order - how can I change this so they are based on a different order - eg by the descriptions corresponding ID
I'm trying to run a query which fetches only the last 2 years of data for a given region from a table with several years worth of data (there are year, region, sector and rank columns among others). The region is passed into the query from a combobox from Form1.
The first problem was that some regions have up-to-date data and some not so much e.g. for Europe the "last 2 years" mean 2012,2013 for Asia its 2011,2012. In order to deal with this I've created a crosstab query which works well except for one thing - because the columns are dynamic (dependant on the region) the column headings change as well.
And here comes my question, how can i fix the column names to be e.g."Current Year" and "Prior Year" independent of the query fetching 2012,2013 or 2009,2010? I've tied different things with PIVOT... IN ... but with no luck.
Here's the sql for the query:
Code:
PARAMETERS [Forms]![Form1]![cmbRegion] Text ( 255 ); TRANSFORM min(DataTable.Rank) SELECT DataTable.Region, DataTable.RegionalSector, FROM DataTable WHERE (((DataTable.Region)=Forms!Form1!cmbRegion))
SELECT testScreen, Count(ID) AS testCount FROM SERPTestInput GROUP BY testScreen
It returns a count for each testScreen that appears in the table.
Then this query returns the same thing except it joins another table and adds the criteria that the status for the record must be successful.
SELECT testScreen, Count(testID) AS myCount FROM Results RIGHT JOIN SERPTestInput ON (SERPTestInput.ID=Results.testID AND Results.testStatus = 'Successful') GROUP BY testScreen
So both queries currently generate an 8 row table with the first column being the testScreen and the second being a count. I would like to somehow combine the two so I get a three column, 8 riow table. Column 1 would still be the testScreen, column 2 the total count for each testScreen and column 3 would be the successful count for each test screen.
In short, can the above two queries be combined into one?
I know that this could open a can of worms but is the above statement true. I have always avoided using delet and append due to database bloat. I know its more difficult to set the field types in a make table query but it means that the database doesn’t need to be compacted nearly as much as delet and append.
If my understanding is incorrect please explain top me why delete and append are better.
Hello All, I'm using MS Access 2003 for a web DB... Am wondering.. is it bad to use Make-Table queries for the web..? Or should I just do all the SQL joining and linking stuff just with more SQL code?
Just curious about the impact Make-Table queries have on the Web.. Slower? Not a good idea?
I have a linked table with 3 significant columns in it: Marque Model Volumes (there's actually about 12, but I only need these)
and I need to create a new table summarising the contents and creating new columns at the same time Marque Model Model_name (concatenate marque and model, easy to do) Vols sorted: Descending Rank (this is the problem)
I need to 'Rank' the table so the model with the highest volumes is ranked #1 the second highest is #2 and so on. Is there a command within access to allow this to happen? in SQL-Plus from Oracle I can use the Rownum command to create the entry, but this does not have an equivilent in access. At present, I'm creating the table without the rank field, then adding it in design view, setting it for autonumber, saving, and resetting it to number. This is long-winded and frankly, messy and wrong. I shuld be able to do this in one go, but I can't. I've been using access for about 6 years solid and have not been able to resolve this 3-month-old problem and it's driving me mad.
I have 4 queries needed to power one report. I've tried combining the queries into one so I can run the report off that. However, there is too much data and I get all sorts of errors. My only solution was to toss that data into a table thereby eliminating all the calculations and expressions and cleaning up the data. Is there a more elegant solution to this issue?
I have 4 queries needed to power one report. I've tried combining the queries into one so I can run the report off that. However, there is too much data and I get all sorts of errors. My only solution was to toss that data into a table thereby eliminating all the calculations and expressions and cleaning up the data. Is there a more elegant solution to this issue?
when i want to make a delivery note - on many cases the Delivery Note Number that shows has more than one house name, Plot Number and Product - at the moment it only shows one specific house name, plot number and product when i would like it to show every one of these under the delivery note number and delivery note date?
Linked field is shown both in Master and child tables, but not in the default subdatasheets made by when a relationship is made between a Master and Child Table.
I have two basic lists of employee names, ID's, phone numbers etc, with one field containing an attachment with a picture of their ID. I don't want to permanently combine these two lists of employees, as they work in different departments, but for certain events I need to be able to print a report with a list of all their names, ID numbers, and corresponding ID photos.
I tried to make a UNION query, but can't do it with the attachment field. If I leave that field out, it's no problem. To simplify, I have been doing a test run as follows, with just the name field and photo field (field 1).
SELECT [Starting Gate employees].[Employee name], [Starting Gate employees].Field2 FROM [Starting Gate employees] UNION SELECT [Farrier employees].[Employee name], [Farrier employees].Field2 FROM [Farrier employees];
I need to put together a make table query, already got all the fields sorted the way i want the problem is that query is located in my databases Front End and i need the table to be made in the Back End (giggity), the filepath for the back end is actually stored in a "Master Control" table if this makes the process easier...
I have a complex series of calculations to perform, and I have found the best way to achieve my end is to create new tables by the Make Table queries.
The trouble is I have many tables to create and the downstream tables refer to the previously created tables; I have recently discovered that queries perform much quicker if they are indexed (duplicates OK). I've looked at my tables created by the MakeTable queries, and they all seem not to be indexed.
I am developing a Make Table Query from 2 tables, one of which has an "Employee Name" field (lastname,firstname) and the other table has separate fields for LastName and FirstName. I've been able to accomplish almost what I need by:
WHERE ((([Table 1].[EMPLOYEE NAME]) Like [Table 2].[LAST NAME] & "*"));
Which works fine except when there are 2 employees having the same last name, then it generates duplicates. I suspect there must be a way to do this by incorporating the first name field in the sql statement but that's beyond my ability. I realize that names are not good things to base a query on, but the 2 existing tables have been preset and populated by others and I don't really have the capability to change them.
