Queries :: Run Query Multiple Times (different Data Parameter) To Generate Single Dataset

Mar 30, 2015

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.

View Replies


ADVERTISEMENT

Queries :: Control Source - Parameter Prompt Multiple Times

Jul 15, 2014

I 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?

View 3 Replies View Related

Queries :: Run Multiple Expressions On The Same Data Field In A Single Query?

May 11, 2014

I am trying, and failing quite spectacularly to create a single query that will contain multiple expressions on the same field.

Please see attached jpg for example.

SQL: SELECT Count(StockData.status) AS FaultyPCs
FROM StockData
HAVING (([StockData].[Status] In ("Waiting Repair") And [StockData].[DeviceType] In ("Thick PC")));

What I want to be able to do is then add additional expressions to filter and count in the same way for "Thin PC" "Thin Laptops" "Thick Laptops" and so on.

But when I add one of these expressions to the next column in the builder, it seems to apply itself to the expression to it's left, causing that one to now give a result of 0.

Is there a way of separating one from another or do I have to create a separate query for each expression and then use another query to pull them all together (as I have done in another situations - but this will involve creating probably 20 separate queries.

View 7 Replies View Related

Queries :: Dynamic Query Needs Parameter Two Times

Oct 18, 2013

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 Related

Queries :: Data Integrity Report - Multiple COUNTs In Single Query

Sep 4, 2013

I need to produce a Data Integrity report that lists the users of a separate database and the count of errors that they make, separated by error type.

Currently my query displays all the users who made errors, and the total errors they made. But I don't want the total - I want to break this number down by the types of errors that are made.

I modified the SQL to make it easier to read. How can I take the below statement and make a few expressions that count up the specific values? The field I need to dissect is "Type Error" and a couple of examples of error types are "A1" and "B1"

Code:
SELECT DISTINCTROW [Errors Table].User, Count([Errors Table].[Type Error]) AS [Errors]
FROM [Errors Table] INNER JOIN [Workcenter Profiles] ON [Errors Table].PWC = [Workcenter Profiles].PWC
WHERE ((([Errors Table].[Review Status])="Error Corrected")) OR ((([Errors Table].[Review Status])="Error Not Corrected")) OR ((([Errors Table].[Review Status])="Error Not Correctable "))
GROUP BY [Errors Table].User
ORDER BY [Errors Table].User;

I've seen it done with multiple queries joined together, but I'd like to avoid that if possible...

View 5 Replies View Related

Add A Single Field Multiple Times In A Form

Oct 3, 2006

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.

View 1 Replies View Related

Parameter Prompting Multiple Times?

Aug 15, 2011

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 1 Replies View Related

Reports :: Requesting Parameter Multiple Times

May 29, 2014

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).

View 3 Replies View Related

Queries :: Generate A Query From A Table That Has No Data

May 19, 2015

I currently have tables that are in the database but they require the user to import the data from an excel file. This works fine however due to the data being imported I only require certain columns from the import, this is where I would use a query.

The data of the import will always have the same column headings. Firstname, lastname, usernumber etc..Unfortunately i'm not quite sure how I could get a script to generate a query of the selected columns after an import is completed.

View 8 Replies View Related

Queries :: Query Filter Out Data From A Table Between Times On 2 Dates

Jul 24, 2014

Running Access 2010 and developed this query to filter out data from a table between times on 2 dates (day before report run and day of report). Covers data from a shift that carries over to the next day.Trouble is, the PC i developed on still operates the query as expected. However, on the PC the database resides (not networked just stored) and operates, the query brings up no data at all unless I remove the Time filtering.

This PC used to operate correctly up until early this year (about 18 months operated correctly) when the PC was replaced due to failure. Access version is the same and I am at wits end to what the cause is. Here is what my query looks like:

Quote:

SELECT Breakdowns.BreakdownDate, Breakdowns.Time, Breakdowns.Shift, Breakdowns.Downtime, Breakdowns.Equipment, Breakdowns.Conveyor, Breakdowns.Fault, Breakdowns.Stopper, Breakdowns.Gate, Breakdowns.Dolly, Breakdowns.Carrier, Breakdowns.FaultType, Breakdowns.Comments, Breakdowns.Tradesman
FROM Breakdowns
WHERE (((Breakdowns.BreakdownDate)=Date()) AND ((Breakdowns.Time) Between #00:00:00# And #6:29:00#) AND ((Breakdowns.Shift)="Night")) OR (((Breakdowns.BreakdownDate)=Date()-1) AND ((Breakdowns.Time) Between #22:30:00# And #23:59:00#) AND ((Breakdowns.Shift)="Night"));

View 14 Replies View Related

Modules & VBA :: Generate Multiple Records In One Table From Single Record In Another Table

Sep 20, 2014

I am building a simplified re-order point system - if inventory position drops below a certain level (the yellow level is this case) one or more purchase order lines has to be created in another table.

I have one table with the following field and data:

ItemId Red Yellow Green Multiple Inventory position
0001 10 30 50 5 45
0002 5 40 47 5 23
0003 11 20 30 10 5

I would like to generate new records (in another table) based on the above fields and three records.Basically the end result should look as the following:

ItemId Qty Start inv Aggregated inventory Prioritization
0002 5 23 28 Yellow
0002 5 28 33 Yellow
0002 5 33 38 Yellow
0002 5 38 43 Green
0002 5 43 48 Green
0003 10 5 15 Red
0003 10 15 25 Yellow
0003 10 25 35 Green

The logic is quite simple - if inventory position is less than the yellow value new order lines should be created in multiple qty (based on the multiple field) until the aggregated value (in table 2) is above the green value.The priotization value should be based on the start inv (in tbl 2) compared to the values in red, yellow and green in tbl 1.

View 8 Replies View Related

Queries :: Multiple Combo Boxes In Single Query Criteria

Nov 20, 2013

I managed to get separate combo boxes to filter out results on a query, however now I have a slightly different problem.

I managed to get the combo boxes to filter records in different query criteria, i.e criteria A for field A, criteria B for field B, etc.

Now I would like to have several combo box filters in one criteria field, however no matter how much I try to move the code about, they either filter out nothing at all, or show up blank records as they're filtering one after the other, i.e filter for A, and then B, which clearly won't work as there will be no B if you've already filtered for A.

The code I'm using in each criteria box is;

Like "*" & [Forms]![MyForm]![Combo1] & "*"

and this works as a single criteria in a single field.

View 5 Replies View Related

Queries :: Counting Multiple Dates In A Single Query Field

Feb 8, 2015

I'm creating a database for my wife to use in her work, one of the fields is dates visited and the user should enter "00/00/0000, 11/11/1111, 22/22/2222, 33/33/3333" in this format. The field is a large text format because there could be anything from 1 date to hundreds. What I need to be able to do is in my query I need to enter 2 dates and have access return a how many dates exist between those 2 date values. eg It will pop up a box asking for start date and then another asking for end date and then it has to return the count total of how many dates exist between those two date values. I don't know VB and have had only basic training in Java and C# none of which involved Access. The only way I can think of doing it is to have access count the number of commas within the date field required but I don't know the formula required assuming it can even be done.

View 1 Replies View Related

Queries :: Dataset - Group By Query Without Summing

Jan 15, 2015

I have a a dataset that has recorded encounters with a number of Banded (identified) animals. It is currently setup so that each encounter is its own row; with all the information, including "Band_Num" repeated, but "Encounter_Date" being unique. I wanted to query the table and get it to display the results as "Band_Num" on a row, followed by x columns for with each unique "Encounter_Date" represented. I thought a "Group By" function would work, but that requires a sum, which is not useful.

Complicating this is the fact that not every individual is seen a set number of times, so Animal 1 might be seen 3 different dates, but Animal 2 only on 1 date.

View 4 Replies View Related

Queries :: Multiple Genre Parameter Query In Movie Database

Feb 3, 2015

I have over 800 films which I've converted from DVD to iTunes format. I have added them to iTunes and extracted the data to Excel. From Excel, I have imported the data into MS Access.

So, now I have a movie database of my films. See attached fields for all tables and the Relationships.

As you can see, I have created a separate Genre table as this will be my main search parameters.

I can run a simple query which returns the search results for example 'Comedy'. My problem is, I am wanting to do a search for films with for example 'Comedy' and 'Drama'. Whichever way I try this, I don't get the right answer. I either seem to get duplicate records or no results returned.

I attach the query I am running which works for 1 Genre only!

I am not sure whether I have some fundamental problems with it and it's better to start from scratch?! I have the raw data from Excel which I could use to start again.

View 4 Replies View Related

Modules & VBA :: Running Queries Multiple Times In VBA

Dec 10, 2014

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.

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 ?

View 4 Replies View Related

Queries :: Using Same Field Multiple Times Under Varying Criteria?

Dec 30, 2014

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?

View 3 Replies View Related

Queries :: Count A Field Multiple Times (sub Totals And A Total)

Jul 18, 2013

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.

View 4 Replies View Related

Queries :: Field Appears Multiple Times But Different Criteria For Each Column

Jun 2, 2013

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?

View 3 Replies View Related

Queries :: Parameter Query Using Form To Collect Data

Jun 24, 2015

I have a parameter query using a form to collect the data. I put LIKE and wildcard enclosing the parameter to bypass the criteria if the data box is empty. It works well. I have another parameter of date type that uses between two dates. How can I bypass the criteria if I leave the data boxes blank?

View 8 Replies View Related

Queries :: Customizing Access Parameter Query - No Data

Apr 22, 2015

I don't want my user to type in the parameter value for a query in case of miss spelling. Therefore, I'm using a dialog box form with a combo box field. The row source of the combo field is a table with one field for the list. I've added VB code (Event Procedure) to a buttons on the dialog box which says to run a query after click. I've created the query for the info I need displayed and am using the forms combo field as the criteria.

Private Sub cmdOK_Click()
DoCmd.OpenQuery "qryRequestsbyBranch", acViewNormal, acEdit
DoCmd.Close acForm, "frmDepartmentList"
End Sub

The query runs except I'm not getting any data.

View 14 Replies View Related

Automaticaly Run Query Multiple Times

Jan 24, 2008

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.

View 2 Replies View Related

Queries :: Setting Parameter Labels Is Causing Query To Return No Data

Jul 3, 2014

My parameters are linked to a form and say:

[Forms]![FormName]![Field] or [Forms]![FormName]![Field] Is Null

Ordinarily this works fine in returning either the selected value or all values if left null.

I need to pull in data from a Crosstab query, which means setting my parameter labels to [Forms]![FormName]![Field].

The problem is that setting the parameter labels is conflicting with pulling all records if the form dropdown is left null.

If I keep my parameters simple and just say [Forms]![FormName]![Field] then the query works with the crosstab data, but I can't do that. I need to show any records if the dropdown is left null.

The crosstab data isn't specifically the problem but needing to set the parameter names seems to be

I think I may have found a workaround by labeling the column headings in the crosstab, which means I don't have to assign parameter labels

It would still be good to know if there's a way of making it work with the parameter labels but this will do for now...

View 7 Replies View Related

Comparing Multiple Dates And Times In A Query

Mar 8, 2007

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

View 4 Replies View Related

Prompted Multiple Times For Query Criteria

Sep 27, 2006

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?

View 1 Replies View Related

Data From Multiple Tables On Single Form

Oct 14, 2004

I have a tabbed form that contains multiple rows of data just like the following: (it's a service checklist for a store where the user makes choices from the combo boxes: e.g. good, fair, poor, etc)

Paint (txtHrs), Action (cboPaintAction), Condition (cboPaintCondition)
Carpet (txtCarpetHrs), Action (cboCarpetAction), Condition (cboCarpetCondition)
Counter (txtCounterHrs), Action (cboCounterAction), Condition (cboCounterCondition)
etc.

Since the combo boxes describe data that relates only to that element I can't put all the data needed into a single table. There are 40 of these rows so does that mean I have to create 40 tables? If so, how do I add the data to them from the form? Any help with this problem would be greatly appreciated!

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved