I need to run a parameter query in which you enter a product number and the query (a) returns the list of components and (b) then automatically reruns the query for these components. In other words, I enter an input (a product number) the query gives me outputs (component numbers) and then reruns the query with each of these outputs as the input (i.e. I want to know what are the sub-components of these components). This process stops when all the components are raw materials -- this will be easily identifiable.
Does anyone know how to set-up this type of query?
Sub Service ID (primary key) Sub Service Main Service ID (foreign key)
one-to-many relationship
I would like to query the tables, so you search on the Sub Service and it returns all of the Main Services it come under. I would like this query to run off an Input Box that will allow me to type in the Sub Service or a word.
I have managed to get the Input Box, by putting the following code into the criteria :InputBox («prompt», «title», «default», «xpos», «ypos»). But when I type something in the search fails. I have chopped and changed the fields I search on, but can't get it quite right.
Can anyone help me on what fields to choose and what I need to set in the criteria to get the search running properly from the input box?
Also is there any way to program Access or VB to return the results in a message box e.g. **** is a Sub Service of ***** - listing all main services it appears under?
I have a field in my query which returns results based on a formula that is a function of other fields. The results are: Pass and Fail.
I want to make a query that returns only Fail rows. When I enter Fail as the criteria, a parameter box pops up requesting information be entered before continuing.
I have a field in my query which returns results based on a formula that is a function of other fields. The results are: Pass and Fail. I want to make a query that returns only Fail rows. When I enter Fail as the criteria, a parameter box pops up requesting information be entered before continuing.
I'm really hopeful that somebody can help us out here. I'll explain as best I can.
I've got a table, where details of particular online environments are kept. Each online environment is assigned to up to four people. By this, it may be assigned to nobody, or it may be assigned to four seperate people, or anywhere in between.
What i'm trying to create is a query where the user of the database can type in a particular persons name, and it will return all the details of any record where that person is mentioned in ANY of the four colums, which can then be used to generate a report for that particular individual.
However, i'm really unsure of how to do this, and am aware that there is probably a really simple solution to my quandry, that will leave me kicking myself up the rear like theres no tomorrow.
Any advice as to how to proceed would be hugely appreciated, genuinely.
I am trying to put together a quick database for my HR team. I need to be able to pull multiple date ranges to show which employees are up for an annual, 6 month and 90 day review.
Is it even possible to put multiple date ranges in one query, or is this going to get complicated? I have no real database background, and know absolutely no SQL so I'm flyin by the seat of my pants here...
During a client's stay with our agency, they are served among multiple contracts and are placed within multiple programs. Because clients move frequently between contracts and between programs, we have separate tables which have start and end date fields. It is common for any client to have multiple listings in these two tables, but with different event dates.
I am struggling to create a query that will capture each client's most CURRENT contract AND program. In the future, I'll want to create a parameter query that will allow the end user to enter a range of dates and capture all of the contract and program movement during a period of time.
how to query data in my database based on a number of different criteria.I have reached a stage where I can get all the data I need from one query, however I can't figure out how to further query this data to return records from a table with the most recent date only. I have searched the forum, googled and experimented myself but I am running into "Aggregate Function" errors.In this scenario there are 3 tables. tblJobs, tblEquipment and tblInspectionLog. Each tblJobs record can have multiple tblEquipment records attached to it, and each tblEquipment record can have multiple tblInspectionLog records attached to them.
I would like to query the database for what tblEquipment records have been assigned to a tblJob ID and also return only the tblInspectionLog record with the latest Inspection_Date field.At the moment I am able to see tblEquipment records attached to tblJobs, however duplicate records appear due to multiple InspectionLog records associated with the equipment.
I have tried to filter records from tblInspectionLog using the "Max" criteria under Inspection_Date field in my query. This however returns an "Aggregate Function" error.
I am really new at access 2003 and queries s forgive me if this question is really dumb/simple/confusing...here goes
I created a query where I ask the user to provide the info for the date range..<[Before date]
it worked fine.
I then added something to the database and ran it again and it now asks me for my date not once, but twice..i then copy and paste query with a new name and it asks me for the same info 4 times
Hi again everyone, With help on my first question Ive now got my cascading combo boxes working to a stage that shows all the information I need,
Currently Ive got 4 cbs and they work bring in and updating depending on what is selected in the previous etc etc.
combo1 = 2 combo2 = 5 combo3 = null combo4 = null
how can I tell it to use combo2 since there is no values selected in 3 and 4
Ive currently got the combo boxes in a form of their own, basically in my main form Id like to be able to click on a button to update a value, and then it will bring up the form for the combo boxes, then id select the values till I had what i needed, and then it would fill in the value on the main form with my selection from the combo boxes,
to make it abit harder, on the main page, there is 3 values that will need to get their value from the combo boxes, will that just mean I have to make 3 forms with the combo boxes in them, one for each of the values on the main page, or could I just have one form and maybe put a button saying (Use this Selection for Value 1)(Use this Selection for Value 2) etc etc.
Ive looked through the net alot and cant seem to find any examples that suit my question... but if anyone knows of a good example that would be awesome.
hi I've got a series of analytical data tables and queries setup.
one of the queries will do a length weighted average based on a user input FROM and TO distances, this query works fine and produces a report.
What I have is a table that contains a list of m FROM TO distances,
rather than picking them one at a time I'd like to set up a report that would get the FROM TO info from the Table, call the query and report each of the FROM TO distance averages on a line in the report.
What's the easiest method to use to set this up. any code snipets would help to get me started and any help greatly appreciated.
I am creating a database that is supposed to capture the employee's type of work, time it takes to do the work, etc...
So far, it does what it's supposed to do but my record's are all over the place. E.g. i have tables called:
tblDailyActivities - this table is supposed to store all record without sorting.
tblemployees - this table is supposed to have a list of employees and when maximized, all records associated to that particular employee should be listed.
However, the i have right now is that instead of listing it under the particular employee, it lists the employee's name individually for each item listed.
What i want is to for the tblemployees to be listed like tblfunctions.
see attached database since i probably am not making any sense right now.
I have a report with a subreport. I have added a parameter to the query to prompt for [manager] or is null to get all managers. However, on the report when I enter a manager my count on the subreport still returns the count for all. So, I added the same prompt to the query for the subreport. It returns the right count, however, it prompts for the manager multiple times. How do I get it to prompt only once yet get the right total?
I'm trying to 'rank' x5 categories that overlap. To determine the ranking i'm utilising date & time (if the dates are equal). I've managed to do this comparing x2 of the categories but struggling with the logic for x3,x4 & x5 categories
I have the data in separate tables by category date & time and in one large table.
Hoping someone can assist as my head is starting to hurt.
A user wanted to key in the data in a single form where she can select where a particular procedure is located. The procedure could be duplicated and placed in a few departments.
My tables are as follows:- tbl_Proc_Dept:- ProcNo - Text (Primary) TrgConducted - Yes/No DeptAbbv - Text (Primary)
tbl_Dept:- DeptAbbv:- Text (Primary) DeptDetails:- Text
I created a form where i have the following fields in the form:- 1. ProcedureNo - Text 2. TrgConducted - CheckBox 3. MainDept- ComboBox (DeptAbbv data field) 4. Finance – CheckBox (DeptAbbv data field?) 5. Admin – CheckBox (DeptAbbv data field?) 6. Purchasing – CheckBox (DeptAbbv data field?) 7. Facilities – CheckBox (DeptAbbv data field?) 8. MIS – CheckBox (DeptAbbv data field?)
How can I add multiple deptabbv field in a single form?
I have a report that is based on a query. In the query, I have it prompt for a criteria for a field. It prompts for the name of an event, so only the participants of that event are displayed.
Also on that report, I have a subreport, which is based on that same query, but returns different data.
My problem is that since both the main report and the subreport are based on the query that prompts for input, it's prompting me multiple times for the same input when I view the report.
Is there a way to have Access use the same prompted input for both reports, so I don't have to enter it more than once?
Hey all, i would appreciate some help with Access here.
I am an attorney, and i am trying to set up an access database for all of my cases.
I have one table in which i keep the names, contact information, etc of all the witnesses who i have used or will be using (especially expert or law enforcement witnesses who i will be using in multiple cases).
I also have a main table in which i have a record for each case. Because I have multiple witnesses for most of my cases, I have had to create multiple relationships between my main cases table and my witnesses table.
My question is this: How do I specify when setting up a report (or a form/query/etc) that when i ask for the witness's address i am looking for the address i made with a particular witness, specify that relationship between the two tables, as opposed to the other relationships between the same two tables.
I hope that makes sense, and i would very much appreciate if someone could give me instructions.
Here is my delima, I have a report that I am able to type in a keyword and it will pull up that report exactly as I type the keyword, however, let's say that I want to pull up someone else's report I cannot. I have to exit out of my report and then go back into it and resubmit the new search.
I have a situation where I am using a maketable query to create a table and then I need to use append queries to then add additional records to the created table - some of these are just run once and some multiple times.
if possible, I do not want to hard code the query multiple times i.e.
I have created a query with parameters and a report from the query. The report is requesting the paramater multiple times but the query is running fine. How to resolve? Again, the query requests the parameter once but the report multiple times (6x).
I've narrowed my problem to a subreport that prints multiple copies of the report based on the number of items generated in the subreport.
i.e. If I have an instructor registered to teach three classes in the subreport, the main report will print three time (identical). An instructor with two classes will print twice (identical).
No button...just good old "print" how? why? where?
At the moment I am working a little project at work and am stuck on how to calculate departments due date based on actual order due date.
example:
OrderID: xxxxxx-xx DueDate: 10/8/13
Now let say i have 5 different departments but I want/need to know what should be the due date for each of those departments based on the due date on top.
