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?
View RepliesI 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).
View 3 Replies View RelatedI have a form using a query as a control source so that is prompts the user for a parameter when they open the form.
I also have a listbox that is calling the same query.
The issue I am having is when I open the form it will prompt me twice to enter the same parameter.
Tables:
tblship
tblconsignee
tblbill
tblinvoicedetails
Query
qinv
Form
frminvoice
When the user enters the invoice number it populates the ship, consignee, billing address information. I have the list box to display line items for that invoice.
Currently I just have them enter the invoice number twice.
Is there a way I can take the user input and apply it to my other query?
Hello,
I have a table which contains:
InvNo - Autonumber (invoice number)
AccNo - Clients account number
Amount - Amount of invoice
I also have this data (minus the InvNo) in a linked spreadsheet. I would like to keep the spreadsheet as it is all set up for the printing of multiple copies etc.
The idea is, I want to import the data from the spreadsheet into the database, which will then apply an invoice number (because it is an autonumber), and then get that invoice number back into excel.
I have done it all, but cannot get it to give me the invoice number assigned to it.
I thought I would have a simple query that looks for records in the table that are the same account number, and amount (plus a few others I havn't included here), and then it could return the InvNo.
But, whenever I run the query, it asks me to specify the parameter, it doesn't seem to be able to read from the linked spreadsheet (it is openable, I can open it in access just fine).
In the query I have put (under criteria): "[LinkedSpreadsheet]![AccNo]" (and similar for the other fields I want to match).
What is wrong?
Thank you!
I have a reasonably complex query (3 subqueries into 1 main query) which gathers data from various tables into a single dataset based on a specified date.
I now need to generate a similar dataset but across a range of dates (a month) for reporting purposes. However, I can't just adapt the query and change the parameter from a "=#<Date>#" format to a "Between #<Date1># And #<Date2>#" format
The reason being, each date has to be treated individually and has to be queried as a standalone. It's to do with the type of data I have (one-to-many relationships between tables)
So what I really need to do is run the same query multiple times, for each date in scope, then stitch all of those datasets together into one 'giant' one.
How to do that in SQL (effectively, have one query produce the dates in scope, then join that onto the other query, passing each date as the parameter - I don't even think that's possible to be honest)
The other option I can think of is to use VBA to loop through the dates in scope, then use a QueryDef object to set the parameter and read the records for each date into a Recordset object. But then I have the problem of stitching all the Recordsets together, without looping through all the fields and rows each time.
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?
View 2 Replies View RelatedI 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
Help me please..Thanks
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.
Cheer Ezy
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.
Hello All,
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.
Thank you,
John
Hi there,
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.
Thanks
I hope someone can help me on my problem below:-
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?
Pls help.
Thanks.
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.
Thank you.
Jay B
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.
View 13 Replies View RelatedI 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.
Code:
DoCmd.OpenQuery "qryCreate_1"
DoCmd.OpenQuery "qryAppend_1"
DoCmd.OpenQuery "qryAppend_1"
DoCmd.OpenQuery "qryAppend_1"
etc
So is there a way I can run the make table query and then get some sort of loop to run the append query a number of times ?
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.
I set up what I thought was a simple query in my DB using it to create a report containing a subreport.
I kind of works, however Access is asking me for the parameter value [Extract Date] multiple times, when in theory it should only ask once!
In addition (and likely related) the Access is then repeating the output multiple times.
Hi,
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?
Thanks!
Andre
I'm trying to clean up one HUGE and ugly form by using subforms within tabs. I have a main form with "BottleID" as the key field and linking field. The tab displays pictures based on the BottleID and it works like a dream. My problem is that the "on_current" event happens 4 times on each record. For instance, I go up a record and it sets Picture1.value 4 times and it's the same thing everytime.
I'm wondering if the problems is due to bad table design. Everything is located within one table and I'm really unable to change this. It's been like this from the get go and too much to change to break it apart. Anyway, the subform is pulling data from the same table. I thought maybe this was the cause.
I'd post the database if possible, however there are hardcoded directory's and will not work unless they are all set up. Not worth it IMO. Any ideas? I tried doing the BottleID = forms!frmMain!BottleID instead of the auto-link in Access but it gives me several errors and I don't believe it buys me anything. Thanks in advance!
Preamble edit: I'm running Access 2003.
I have two tables, Students and AttendanceRecords.
Students just has studentID and studentName
AttendanceRecords has AttRecID, studentID, presence, thedate
I'm looking to create what looks like an Excel grid, with the last 10 days as columns and the student names as rows. All the cells in middle will be filled with the values of 'presence' for that student/day (e.g., P for present, A for absent).
Here's something I'm currently considering.
-I could make 10 queries, each using LEFT JOIN to connect studentName with presence & thedate on studentID, varying the 10 queries only in that 'thedate' will have a criteria of Date() -1 , Date() -2 , etc.
-If I'm understanding it correctly, I'll then have 10 tables, each containing 3 rows -- student name, presence, and the date (with each table having only 1 date repeated throughout).
-I could then join those 10 queries together on studentName, theoretically resulting in 1 big table with all the student names and the corresponding presence values for the last 10 days
If I do that, I could make a form in Continuous view and have each row show the studentName and 10 text boxes closely bunched up with presence values.
That seems very inefficient? Making 10 queries separately and then manually merging them seems redundant.
Also, now that I think about it, will the final product end up being read-only, or if the user changes one of the presence cells will it update the corresponding record in AttendanceRecord?
I have one table like:
ID Type No
-----------------
1 FL 2
2 DL 3
I need the following table to be created using first table , each record should be duplicated according to value in No field:
ID Type No
-----------------
1 FL 2
1 FL 2
2 DL 3
2 DL 3
2 DL 3
I have 2 tables (person / merit) currently the merti table has 4 fields (MeritID, PersonID, Issuer, Type) I'm trying to count the the Type field 7 times, once of each type (MC/MI/MN/MP/MS/MV) and once to count a grand total.
I want to produce a query to use in a mail merge that will list the total for each merit type and one grand total.
I've been trying a few different things including sub queries but I've not managed to get the query to produce a single record that counts each individual type.
I'm working with a table of bird survival data I am trying to summarize in a query. I've got a bit of a roundabout way to achieve my goal, but I'm curious if there is a simpler approach.
Background : In my table, each row represents a day I check a given nest and includes a [Nest ID] (not unique, multiple visits to each nest), a [visit ID] (auto numbered, so it's a unique value for each visit at each nest), the calendar day I visited a nest [Date], and [Survive] (1 or 0) depending on whether a nest survived or failed.
I'm trying to convert this detailed table into one that is more concise. Instead of each visit to a nest being a row, each nest becomes a row with 4 fields: The Nest ID, the minimum date (the day I found a nest), the last day a nest was checked (Max[Date]), and the last day a nest was checked alive (essentially max date where survival=1).
My current solution is to run 3 separate queries. The first queries the max date where survival=1, the second queries the max and min dates regardless of any other criteria, and the third brings both queries together.
I am curious if there is a way to create the same final product in a single query rather than doing multiple ones as I have done?
Is there any way to press a command button a certain amount of times based on a value in a text box.
i.e.
text box value = 5
button gets pressed 5 times with one click...