User Inputs Table When Query Runs.

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 Replies


ADVERTISEMENT

Getting Data From User Inputs

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

Multiple Inputs For A Query

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

Query Runs All Day

Feb 28, 2007

Dear pro,
I am having difficulties running a simple query that it runs all day and I have to kill it at the end of my date because it does not finish. Here is the statement that I would like some suggestions on how to improve it for faster results:
SELECT code, acct nbr, date, user, sum(amt)
FROM Tbl a
WHERE code IN
('AB',
'BC',
'CD',
'AA',
'BB',
'CC',
'ZZ'
)
AND date BETWEEN TO_DATE ('20070101', 'YYYYMMDD')
AND TO_DATE ('20070131', 'YYYYMMDD')
group by code,acc nbr,date,user

thank you...in advance for all your help..

Wilman.

View 1 Replies View Related

Mulitple Inputs To A Single Field Query

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

Queries :: Way To Construct A Query From Selected Inputs

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

Query Runs Slowly

Aug 8, 2006

I have a table with a rotating work order number (from 1 to 9999 then starts over) and because there may be more than one record with the same work order number I need to find the most recent one. Here's the query I'm using the find the most recent record for each work order number:
SELECT * FROM [Work Orders] AS wk1
WHERE NOT EXISTS(
SELECT * FROM [Work Orders] AS wk2
WHERE wk1.[Work Order #] = wk2.[Work Order #] AND
wk1.[Call In Date] < wk2.[Call In Date]
);

Right now the table has a litttle over 10000 records and it takes anywhere from 10 seconds to 60 seconds to return the results. Is there a way I could possibly speed up this query?

View 2 Replies View Related

Queries :: 2 Inputs For Same Field - Query Result Not Correct

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

A2003 Query Runs Under XP, Not Vista

Feb 26, 2008

I apologize up front for how long this is, but the queries involved are a bit complex.

I developed a database in Access 2003 running on XP sp2 to score events for our dog club. Everything works. I recently had to migrate to Vista. I installed Access without a problem. No changes to the database were made. Almost everything works except for a few queries. I continually get the error "Object invalid or no longer set." This is not running on a network - it is local on the hard drive. Setting XP compatibility mode and runing as administrator do not help.

The query that is actually causing the problem is the third in a chain, meaning it queries a dataset produced by another query, which in turn queries a dataset produced by a third query. This all works under XP.

I have narrowed the problem down to the ORDER BY statement in the query (I prefer to work in SQL). If I remove one of the sort criteria, everything works, except the results are displayed in the wrong order. It does not matter if I click the "run query" button on my form, or simply execute the query from the list of queries, the same error occurs. This is where I'm stuck.

Here is the chain. This query pulls data out of multiple tables and performs some point total calculations. This one is rather complex, but works fine. It produces a blob dataset of all entries for all events and sets points for each entry.

SELECT tblResults.Event_ID, tblEvents.Event_Name, tblClass.Class, tblDogs.Dog_Name, tblDogs.ID, tblDogs.Owner, tblDogs.Sex, tblResults.Dog_Wt, tblResults.Cert, tblResults.Ex, tblResults.Cplt_Wt, tblResults.Time, tblResults.Final_Wt, tblResults.Dist, tblResults.Place, IIf(tblResults!Cplt_Wt<400,0,5) AS Bonus, IIf([Bonus]=0,0,IIf(tblClass!Class="D",(tblEvents!D_Count-(tblResults!Place-1)+[Bonus]),IIf(tblClass!Class="C",(tblEvents!C_Count-(tblResults!Place-1)+[Bonus]),IIf(tblClass!Class="B",(tblEvents!B_Count-(tblResults!Place-1)+[Bonus]),IIf(tblClass!Class="A",(tblEvents!A_Count-(tblResults!Place-1)+[Bonus]),(tblEvents!O_Count-(tblResults!Place-1)+[Bonus])))))) AS Points, Round(tblResults!Cplt_Wt/tblResults!Dog_Wt,2) AS xbdywt
FROM tblClass INNER JOIN (tblEvents INNER JOIN (tblDogs INNER JOIN tblResults ON tblDogs.ID=tblResults.Dog_ID) ON tblEvents.Event_ID=tblResults.Event_ID) ON tblClass.Weight=tblResults.Dog_Wt;


That resulting dataset feeds the next query, which also works fine. This one sums the points for each entrant by class for the season, only selecting entrants that have been in 3 or more events.

SELECT DISTINCTROW qryAll_Results.Class, qryAll_Results.Dog_Name, qryAll_Results.Owner, Sum(qryAll_Results.Points) AS Points
FROM qryAll_Results INNER JOIN qryPulls_Entered ON qryAll_Results.ID=qryPulls_Entered.ID
WHERE qryPulls_Entered.CountOfID>3
GROUP BY qryAll_Results.Class, qryAll_Results.Dog_Name, qryAll_Results.Owner, qryAll_Results.ID;


This last query is the one causing the headache. This one pulls the 10 entrants with the most points for a class, sorts them by points, then assigns 1st-10th place.

SELECT TOP 10 qryOverall_Totals_All_Classes.Dog_Name, qryOverall_Totals_All_Classes.Owner, qryOverall_Totals_All_Classes.Points, ((SELECT COUNT(*) FROM qryOverall_Totals_All_Classes AS T WHERE T.Class = "A" AND T.Points > qryOverall_Totals_All_Classes.Points)+1) AS Place
FROM qryOverall_Totals_All_Classes
WHERE (((qryOverall_Totals_All_Classes.Class)="A"))
ORDER BY qryOverall_Totals_All_Classes.Class, qryOverall_Totals_All_Classes.Points DESC;

If you remove 'qryOverall_Totals_All_Classes.Points DESC' from the ORDER BY statement, the query runs. If you leave it in, it generates the error "Object invalid or no longer set". I've tried using the alias, but that didn't work either.

Any thoughts? I can post the database somewhere if someone needs to see the whole thing.

View 5 Replies View Related

Query Is Too Complex... On Report, But Query Runs Fine

Oct 18, 2005

Hi...

I have a query that when I run it normally (just click on it) then it runs fine. (It is a union query, getting it's data from 8 other queries (who has their dependancies)

But when I want to run a report from it, Access gives me an error saying "query is too complex".

I am flattered, but I would prefer access to work than say I write stuff that is too complex for it. :cool:

Any ideas?

I am confused by the fact that it runs when I double click the query, but the report bugs it out.

-Reenen

View 1 Replies View Related

Update Query No Longer Runs As Transaction.

Feb 22, 2008

Hi,
I have been using the following query, literally for years, without any changes. I run it from code using db.execute, and I do use the dbfailonerror option.

UPDATE TST3 SET [date] = Mid([timedate],8,2) & '/' & Mid([timedate],10,2) & '/' & Right([timedate],4), [time] = Left([timedate],2) & ':' & Mid([timedate],3,2) & ':' & Mid([timedate],5,2), Serial = [serial] & '3';

One of the things it does is to add a '3' to the end of the [serial]. [serial] is the primary key in the TST3 table. You might think that there would be a problem if, say, I have a list of serials containing
35
56
1
13
and I'm trying to update them to
353
563
13
133
But this has worked OK in the past. NOW I'm getting a KV Error when it tries to update the 1 to 13, because there's already a 13 in the table.

Even stranger, when the query fails, all the rows BEFORE the offending record DO get updated. So the query fails, and I end up with:
353
563
1
13
(and yes, I DO have dbfailonerror set)

So, it looks to me as if update queries are no longer running as transactions.
I am pretty certain that action queries have always been run transaction-wise in the past... if the query fails, the whole thing should fail. WHY is the transaction processing no longer working for this update query? Has anyone else noticed this?

I recently ran microsoft update and am now running
Access 2002 (10.6771.6830) SP3.

View 7 Replies View Related

Append Query Adding All Records Evertime It Runs

Aug 8, 2005

I have an append query that everytime it runs it will simply duplicate the records. ex.. sample table has 11 records if you run it again the tableB will now have 22 records. I need it to only update the records, so if someone makes in change / new record in tableA it will only update the records in TableB.
I have the Primary Key set to Yes no duplicates.
TableA Primary Key is ID
TableB Primary Key is CustomerID

Thank you,

Chuck

View 3 Replies View Related

Query Runs When A Policy Number Is Input In A Form

Jan 26, 2007

I have a form set up and would like to have field update to a table when a policy number is input into the form. The fields are extracted from a linked table and are not viewed on the form but need to be written to a table to create a report. I'm confused on the sets to take to handle this. I think i need to use the "onchange" property and set up a macro that runs a query but how does the query write to the table?

View 1 Replies View Related

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

Calc User Totals Table 1 And Enter In User Records In Table 2

Oct 28, 2004

I have two tables. The first contains details of a budget holders money allocation for a given period, and the other tracks their spend on products over that period. How can I generate a query to calculate the total running spend for each user from the "budget spend table" that will be written into the users record in the "budget allocation table".

My aim is to show details of budget allocation, total spend to date and remaining budget for each user in an Order form / report.

Can anyone please advise me on how to do this or suggest another way of doing it. Any help would be greatly appreciated.

Thanks in advance.

Regards Peter

View 5 Replies View Related

User Table That I Can't Normalize. Trying To Query 20 Fields!

Jun 15, 2006

I am using a table a user created which is like:
Member ID (key field)
Visit Date
Dept 1
Expense Code1 (combo box E through I)
Dept 2
Expense Code2
This goes on through Department 20.

Now they want to know how many E's for one month. I am stumped on how to normalize this or if it is even possible! I thought maybe there is a way to search the table as if it were a spreadsheet doing a "countif" function??

Any replies much appreciated!

View 1 Replies View Related

Getting User Input For Field And Table On Update Query

Jun 5, 2006

Is there an easy way to get user input like the parameter value box in an update query, where you want the user to specify the table name and field name to run the query on?

View 1 Replies View Related

Help With External Inputs!!

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

Tables, Strucuture, Multiple Inputs

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

Trying To Prevent Duplicate Data Inputs

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

Opening Certain Forms Depending On Inputs

Jul 22, 2015

Essentially I am trying to build a macro or even a VBA script which would open an initial form with various data entry options, and then depending on the options chosen it would open specific forms sequentially with some preset data values.

I can go into more detail if needed; however, the problem is at my work (lab in an industrial plant) my coworkers and I waste a lot of time inputting data as our forms just include every field (which only around 10% of fields are utilized for each entry) because certain circumstances will dictate what fields need to be filled in, so for each entry most fields are left blank. These circumstances are entirely predictable as to what fields I will need for each entry so I thought I would easily be able to figure this out on my own but I have been unable to do so.

View 4 Replies View Related

Calculate Future Dates Based On Inputs

Jun 14, 2007

Hello,

Was wondering if it is possible to create a query or another method that would calculate future dates based on inputted info ?

For example a person inputs on a form a date completed (06/14/07) and then also selects a frequency of when this has to be revisited....monthly, quarterly, semi-annually.

So based on the date completed that the person inputs I'm trying to get the date if they select monthly of 7/14/07 (using above date example).

Any help would be greatly appreciated.

Thank you

View 3 Replies View Related

Finding Records Based On Text Inputs

Sep 13, 2005

Hello

I am trying to set up a user friendly front end to my database. I would like to have a form which allows the user to enter information into text boxes and then press a button which finds the relevant record(s).

I would like to use it to pin point an employee's record based on at least one piece of information, but possibly combined with up to four more by entering into these boxes. For example personnel number, surname, first name etc.

Is this possible and how do I go about setting it up?

I am aware of the filter function and queries but I can't seem to get either to operate in the way I have envisaged. They don't appear to offer the most user friendly solution, at least not in the format I have tried.


Apologies, but I am still getting to grips with access to be honest.

View 1 Replies View Related

Forms :: Retrieving A Number Based Upon Two Inputs

Oct 17, 2014

I am looking for an elegant way to retrieve a number from a table and display it in a form, where the number is determined by two values that will be input by the user to the form.

In particular, I want to be able to enter into a form:

- a date; and
- a class of asset

And have the relevant rate of depreciation for that asset class and that year appear in the form.

Sounds trivial and I guess it can be done in a number of ways. I have tried DLookup. It looks like it should work, but I have not been able to get the syntax such that it will accept a control value as a field name in the first argument of DLookup. (This would involve a table much like a spread sheet with a column for each year. I guess such tables are frowned upon and I have also heard negative comments about Lookups.)

I have experimented with concatenating the two fields into one such there is only one field to search upon. Seems to work. The table would only be updated annually and would only be a few thousand records.

I have experimented with having two joins between tables, but that did not seem ideal.

Perhaps a query of a query?

Perhaps a macro?

(Assuming I can get the form to work I would also like to create a report that lists assets and their depreciation.)

View 13 Replies View Related

Forms :: Delete Records Based On Inputs

Nov 23, 2013

I have a form Delete which contains four fields i.e. Date, City, Depots and Vendor, which has combobox.

I am trying to delete a record from table "Sheet1" based on the combobox, for which i have written the following code, but getting an error at the lines highlighted in red :

Private Sub Command30_Click()
On Error GoTo Err_delete_Click
Dim stDocName As String
Dim intResponse As Integer
intResponse = MsgBox("Are you sure you want to delete this record?", vbYesNo + vbExclamation, "Cash Management Team")
Select Case intResponse
Case vbYes
CurrentDb.Execute _

[Code]...

View 9 Replies View Related

DB Runs Slow Over Network

Sep 18, 2006

I have a 16Mb DB. The intention is for users to access this over the network without copying onto their local machines.

At present, many of the forms are located on 1 master form and they are all subforms on tabs. There are a lot of calculated control boxes on the forms, and these seem to be very slow to bring back the data over the network whereas on my local machine, the data is displayed in a couple of seconds.

COuld this be sped up somehow? Maybe using queries or other methods?

View 12 Replies View Related







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