Queries :: 1 Or More Query Inputs Into One Query
Sep 4, 2014
I would like to run a query that allows we to enter as many criteria in one field as I want
For example
I have an order for wheel for a car order no "1234" then another order comes in for another wheel order no "23456" not the same wheel type
Each wheel could contain 10 parts to make the wheel both orders could contain 5 parts that are the same
What I want to do is enter as many order numbers as I want then when I run the query and it groups all the parts together for me to order in that query.
View Replies
ADVERTISEMENT
Nov 27, 2014
I have a form with several fields and combo boxes on which the user will select different criteria to generate a report.The default value for each of these fields/cbo's is "*", so if the user changes nothing from the default, I execute a basic "SELECT *" query.
If however the user enters some value (selects a specific date range, customer number, salesman,etc), I would like to 'easily' modify my query to accommodate the entered search criteria.Trying to construct SQL when there are many search options like this is difficult. I took a stab at using QueryDefs but I'm struggling to find example VBA code as an example.
View 1 Replies
View Related
Sep 4, 2014
I have a query that allows 2 inputs for the same field ( batch no )
if you enter "pco0093" only you get 4 results
if you enter pco0094 only you get 4 results
both have the same part numbers is the results
When I run both together pco0093 and pco 0094 and use the "group" function on the field batch no I get 8 results ( as expected)
if I use the " where" function I expected to get the 4 results but with the " quantities doubling
What I am getting is more than 4 results but 5 with a part that was not on any of the single results
File attached ....
View 9 Replies
View Related
Jan 25, 2008
I have a form that has three different pull down menus. When I click a button on the form it opens a second form that is filtered by a query. In order to allow the user to leave a pull down menu blank the query has 6 different columns. Three are headed with the field name and three are headed with the form reference, under the form reference it either says Is Null or Is Not Null. I have a total of 8 options in order to cover all possible scenarios. I would like to be able to add more search parameters but my query is getting ridicules. :confused: Is there a way to build the same thing with a code?
Thanks!
View 1 Replies
View Related
Feb 10, 2006
Hey guys, my problem is this:
I want to run a query that runs off of two list boxes on a form. However, I want both these parameters to filter a single field in my query. Why I want to do this is I have several types of accessories made by several manufacturers, both included in the "Description" field. I don't want to include an entirely new field for manufacturer because it's only 4 out of about 5 items that need this.
Is there a way this can be done or will I be forced to include a manufacturer's field.
Thanks in advance
Nathan
View 3 Replies
View Related
Mar 14, 2008
I have a database with 96 tables.
8 tables per month
one query pulls the desired information.
My question is this, currently the query looks to all the January Tables.
Can I configure one query to request user input(month) and then the query looks to the tables based on the users input(month).
View 4 Replies
View Related
Mar 24, 2013
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?
View 3 Replies
View Related
Mar 4, 2015
In some cases I create pass-through queries and use these in an Append or Make-table query to bring data locally.
All is well and fine until source data changes and the pass-through query runs too long and times out.
If needed, I can extend the timeout value in the Parameters of the pass-through query no problem, but when I try to open the Append or Make-table query in Design view to do the same, the pass-through query is first triggered and then throws the timeout, and I cannot access the Design view of the Append or Make-table
Is there a way to open an Append or Make-table query in Design view without invoking the source query?
View 1 Replies
View Related
Nov 20, 2013
I am wondering if there is a quicker way to export a query to excel then have the data in that query removed from the original table. (effectively cutting the data from the table and exporting to excel)
I understand that this can be done by exporting the query to excel then running the same query as a delete query to remove the data but I just wondered if this is the most efficient way.
I have experience of VB in excel but currently only use the basic macro builder in Access though if Access VB is more efficient I can easily learn.
View 5 Replies
View Related
Apr 2, 2013
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.
View 3 Replies
View Related
Aug 12, 2015
I have a query where these are the fields:
ProductRevType
RevLag
RevFlowThru
CloseMoYr
ProjRevDate
CurrentMRC
ProjRevMRC
The ProjRevMRC field is an expression that reads:ProjRevMRC: IIf([ProjRevDate]>=DateSerial(Year(Date()),Month(Date()),1),[CurrentMRC]*[qry303a_ SFADetailMRC_ONLY]![Rev Flow Through],0)
When I run the query, it works perfectly, but when I created a crosstab query to show totals by month, I wanted the totals to be zero for the months less than the current month. Is there a way for the crosstab query to execute the expression and put zeroes for those months?
View 4 Replies
View Related
Jul 15, 2014
I have a field that is giving me the number of business days between a period of time and then I want to subtract that number - the person's PTO time to see the actual days they were available...when I simply type the number in (see below) it works great but I want to set up a prompt that will ask me how many PTO Days to calculate as it will be different for each person I am quering...is this possible?
View 9 Replies
View Related
Oct 8, 2013
I am using an Access 2010 DB to keep track of a schedule. Essentially, at least one person needs to be signed up to work for every hour of every day in a week.
Tables:
Days with 7 records
Hours with 24 records
Workers with as many people that sign up to work the different hours
Schedule signifying the worker, day, and hour which are signed up.
As of now i have a query that relates these results and gives me a line detailing the worker/time information for the slots that are signed up for.What I'm TRYING to do is to create a query that gives me BLANK worker info when there is no one signed up for a particular hour.Currently my Schedule table has the following:
WorkerID | DayID | HourID
----------+---------+--------
1 | 5 | 12
4 | 5 | 13
16 | 5 | 15
What I'm looking to do is have this table matched up with another table (or query) that provides every combination of day/hour. When an day/hour combination is skipped, the query will be able to "fill in the blank" with a row. Like this:
WorkerID | DayID | HourID
----------+---------+--------
1 | 5 | 12
4 | 5 | 13
| | 14
16 | 5 | 15
View 2 Replies
View Related
Oct 1, 2014
I have a query with three sub queries, all returning a number from different tables. But when any one of the sub queries returns a blank, the entire main query is blank.
How do I stop this from happening? I have tried NZ() on the sub queries and on the main query, but the blank still happens.
View 1 Replies
View Related
Aug 12, 2014
I have a database which among other things records how jobs are received i.e.: Telephone, Email, Mail, Facsimile or Web.For each client I want to identify the percentages of each method of receipt against the total of jobs received and during different time periods.I have created a make table query for all jobs received between variable dates for a client entry of the name of the client and the start and finish dates are required to run the query.
I have a crosstab query set up to count each method of receipt and a final query to work out the percentages using the total from the crosstab query fields divided by the total of all methods.I have a macro set up to replace the table with new data when I want the stats for a different client between new dates, therefore the different methods of receipt may vary for the less active clients i.e.: they may only have telephone and email .
My problem is if I choose a client where we have not received a job by a particular method (say web or facsimile), the last query working out the percentages has fixed names to cover each method but naturally produces an error when it cannot find a corresponding method of receipt. I have experimented with NZ() without success.My question is can I either have preset standard names of the column field in a crosstab query? Alternatively in the query calculating the percentages, can I include code to ignore a non-existent field in the crosstab query.
View 9 Replies
View Related
Jan 13, 2014
Been working on this for a while now and can't get it to work how I'd like after trying a few things.
I have a form ("Production Form") from where users input start and end times for various processes against a particular product. Currently, I have (or had) a query (and linked report) "ProductionDurations" where durations for each of the processes were calculated for different product runs.
I have since tried to adapt this query to include reference values contained in another query ("ProductionDurationsPerBulkLitre", based on a "Products" table) for how long each process should take for each production, by multiplying by the volume processed. Here's what I have at the moment in the "ProductionDurations" query:
Quote:
SELECT DateDiff("n",[BlendlineCIPStartTime],[BlendlineCIPEndTime]) AS BlendlineCIPDuration,
DateDiff("n",[FlavourMixStartTime],[FlavourMixEndTime]) AS FlavourMixDuration,
DateDiff("n",[BlendlineStartTime],[BlendlineEndTime]) AS BlendlineDuration,
[Code] ....
Rather than returning what I require, the above bolded part is returning a calculated value for each production against each of the products. What I require is a calculated value for each production against the product that has been selected on the form.
View 14 Replies
View Related
Apr 5, 2013
First query = Sum Products:
Code:
SELECT Sum(Tab1.Inputs) AS SumOfInputs, Sum(Tab1.ValInp) AS SumOfValInp, Sum(Tab1.Outputs) AS SumOfOutputs, Sum(Tab1.ValOut) AS SumOfValOut, Products.Product, Products.VAT, Products.UM
FROM Tab1 INNER JOIN Produse ON Tab1.ProductID = Products.ProductID
GROUP BY Products.Product, Products.VAT, Product.UM, Tab1.ProductID;
Second query :
Code:
SELECT Nz([SumOfInputs],0)-Nz([SumOfOutputs],0) AS Stoc, Nz([SumOfValInp],0)-Nz([SumOfValOut],0) AS ValStoc, IIf([Stoc]=0,0,([ValStoc]/[Stoc])) AS CMP, [Sum Products].Product, [Sum Products].SumOfInputs, [Sum Products].SumOfOutputs, [Sum Products].SumOfValInp, [Sum Products].SumOfValOut, [Sum Products].VAT, [Sum Products].UM
FROM [Sum Products]
GROUP BY [Sum Products].Product, [Sum Products].SumOfInputs, [Sum Products].SumOfOutputs, [Sum Products].SumOfValInp, [Sum Products].SumOfValOut, [Sum Products].VAT, [Sum Products].UM
HAVING (((Nz([SumOfInputs],0)-Nz([SumOfOutputs],0))>0.09 Or (Nz([SumOfInputs],0)-Nz([SumOfOutputs],0))<-0.09));
I need to combine those two query sql code to make only one query.
View 3 Replies
View Related
Jul 10, 2015
I work with access web database. In access web aggregate functions are disabled in query design. So I made a query in client and i thought then i can insert this data to another web table. but i don't know how to update this table.
Because the source table of the query is also updated.
I upload the database with a table in it and a query. I want to add the data of this query to another web table....
View 1 Replies
View Related
Nov 16, 2006
Hi,
I am trying to creat a DB that can potentially recieve input from a serial port which has a unique ID on it and updates the corresponding feild within the DB. I don't need to demonstrate full functionality (although if I have time I might give it a go!).
What I want to show is that the DB can accept a unique ID from this external source. The ID will just be to identify a user and a peice of equipment, potentially the feilds in the table would be updated with information like, time of session, duration, speed. I am assuming I may need to use VB to do some programming and create an incoming event. Just wondering what the most logical solution is. I want to bring up an interface when an input is recieved, like a user interface or pop up which says eg. "Hello Jim". Then it is possible to demonstrate that they have been detected.
Any help would be hugely appreciated!
Thanks,
Minxka
View 5 Replies
View Related
Jul 9, 2013
In simplified terms, I have reportFinalResults based on data from queryA which is based on results from queryB which uses data from table1, table2, etc.
If I update table1 and table2 and then open reportFinalResults, will the changes to table1 and table2 be reflected in reportFinalResults?
Or do I have to open/run queryB and then open/run queryB and then open/run reportFinalResults to show new data?
For precautionary measures, I have a macro to open reportFinalResults that first opens/closes queryB and then queryA.
View 4 Replies
View Related
Apr 15, 2013
I have 2 tables, Event and Person Particulars.
In an event, groups of 2-5 persons may be tagged to this event by a randomly generated number (using autonumber).
Let's say Tom (social security number: 12345X) is tagged to events 2, 5 & 6. There are of course other persons together with Tom in the above 3 events.
If I would like to find out who are the persons who are in events which Tom had participated in, how do I find them using a query?
Currently, I'm thinking of using a searchform where it would return his "associates" if I just query using his social security number, i.e. 12345X.
View 10 Replies
View Related
Dec 18, 2014
I have created a query that counts the AppSizerPerUD for every User Drive (UserDriveID). Now I want to assign these values to the other query (UserDrive Usage) for every UserDriveID. So I want to create a new column in the UserDriveUsage query that will contain the AppSizePerUD. In this case in the first two rows must be 23.6, then one row 18.38, the third one 45,39 etc.I need something like a VLOOKUP in Excel. I have tried DLOOKUP but probably I am not uisng it correctly.
View 3 Replies
View Related
Dec 1, 2013
Is it possible to create an update query where the Update To field is set to the results of a previous query? I've been playing around with the Design View but I haven't figured out how to do this yet.
View 3 Replies
View Related
Jun 12, 2007
Hi everyone,
I am fairly new to Access, and need some help with what I thought would be an easy project. I have an Acceess table that contains data on settings for machines in my plant. There are columns for part #, Machine #, part type, etc. All in all there are about 20 different columns. Now what I need to do is have a form where a user can enter one or two fields, and print a report that contains all the information from all 20 columns.
For example, the user would enter Part number "AB1234" on machine "15-1" and would get the following data from the table:
Part # part code Equip. Temp height etc
AB1234 A 15-1 100 5.3 ...
AB1234 B 15-1 200 6.3 ...
AB1234 C 15-1 250 7.3 ...
They could then somehow choose exactly which record they wanted (example : ABC1234 B) and print a report that contained just the data from that selection.
I hope I explained this clearly enough. Basically I want a user to be able to enter 1 or 2 selection fields and then be able to print a report that contains info from 1 row in 1 table. Any help would be appreciated. I am somewhat new to this and havee a basic understanding of Access and A moderate understanding of VB. Thank you in advance for your help.
View 1 Replies
View Related
Aug 29, 2005
Howdy. I have a question on design of tables. Most of the databases I have seen developed here (and in books) assume that there is some kind of individual input of data. Thus, one major table for input (of various sources) worked well on my other projects. This one doesn't seem to fit the mold.
I have Excel/CSV files from five different vendors. Each deals with a different kind of data. That is, while all of them will have a few common elements, there are several significant differences in the other fields that I cannot bring them into the same table. For instance, in one table one field might be "Size" and refer to inches, while another table would have size, but it refers to the portion of the page (1/4, 1/3, etc.). Thus each of the five vendor tables have unique fields.
Tables:
Input tables:
tblTV
tblNPP
tblRAD
tblOOH
tblONL
Ref Tables:
tblSEGMENT
tblMEDIATYPE
tblDMA
tblDAYPART
For instance, fields for tblTV
TVID
Station
DMA
Daypart
Program
Length
DateStart
DateEnd
TRP
NumberSpots
Cost
MediaType
Cons/Bus
CreativeDescript
CreativeOffer
Segment
So, my questions:
1. Is it acceptable to have five input tables, rather than one?
2. These five vendors will update their input data on a daily weekly basis. This, there will be changes to some fields and addition of many new records. What is the best way to handle this?
3. In addition, we will have 2-5 people updating several fields (i.e. last five in tblTV above) that the vendors cannot supply. Will forms be the easiest way to accomplish these updates?
3. Relationships will be critical in getting this set up correctly. Any suggestions?
Any kind of direction, suggestions will be very much appreciated.
View 8 Replies
View Related
Mar 23, 2005
I have a form that contains 5 fields and want to ensure that duplicate information is not entered. I am aware that when creating the form you can set Duplicate Records to No but each record has an AutoID which I assume means that even if the information is the same it will not register as a duplicate record due to the different AutoID.
There should only ever be 5 records entered per day. One for each region
I have created a Sanity Check that is displayed as a sub form that shows if the relevant information has been entered for a given day in Bookings and Backlog but this doesn't stop the user from putting in the same information twice. i.e. the User starts inputting the information gets called away and then trys to put it in again but doesn't check the sub form.
The Table in which the information is stored is called Bookings and Backlog.
The 5 fields on the form which is called Bookings and backlog are
ID (Autonumber)
Date (Short Date)
Combo26 (Text) (5 Regions:- North, South, East, West, Central)
Bookings (Number)
Backlog (Number)
I have put the following statement in AfterUpdate on the Combo26 field
If Me.Date = DLookup("[Bookings and backlog]![Date]", "[Bookings and backlog]", "[Bookings and backlog]![Date]=[Date]") And Me.Region = DLookup("[Bookings and backlog]![Region]", "[Bookings and backlog]", "[Bookings and backlog]![Region]=[Combo26]") Then
'Define Message Box
MsgBox "The Region you have chosen already has data entered for this day." & vbCrLf & vbCrLf & "Please check your records and amend were neccessary.", vbCritical, "USER INPUT ERROR"
Else
End If
However I'm not to hot with writing VBA and probably got something wrong somewhere as it doesn't seem to do anything and lets the user progress regardless. I have tried amending the above script and asked it to look at just one field but this just seems to give the message regardless of the information selected in the Combo box.
Can someone give me an indication as to what I have done wrong so I can put it right and in a language that is easy to understand so that if I have a similar issue later on I don't need to post another question.
View 3 Replies
View Related
