Database Form Query
Jun 7, 2006
I've got a form that has drop down menus to select info from a particular table for search criteria in Access 2003. If I select a certain set of data I get results in from query. If I happen to leave one of the drop down menus blank than I get no results, when I know there are results availabe. What type of language do I need to put in the criteria part of the query to allow a blank search of one or more of my drop down menus? Say I have 10 drop down menu criteria, and I want to use only 3 of them as criteria for a particular search, so I select the appropriate items and leave the rest blank. But using the same form, be able to do a different search using any combination of the drop down menus with a specific response or blank to search every respoonse in that field.
That way I can have one form to do multiple types of queries depending on the information you select in the drop down menus. And if the menu is blank, the query assumes any entry in that column is open to results.
Any ideas? I am a newbie to Access, so I'm probably just missing something simple, or I'm trying to do something far more technical then I ever thought it would be.
View Replies
ADVERTISEMENT
May 24, 2006
ok, someone please clarify this for me..
i know about normalisation and data redundancy..
therefore i will lay out my database like so..
job id
job date
job time
job id
driver id
driver id
driver name
driver phone
job id
payment id
price
etc...
this is all fine..
in the most straightforward situation, you create a form based on a table...
now if i create a query with fields from all the tables.. eg..
job date, job time, driver name, price
if i create a form in the basic way, you cannot add fields from several tables (in the direct sense i mean, forgettin the use of subforms.. etc)
if i create a form based on the query, will this allow me to add as many fields, and most importantly from as many tables, as i wish.. choosing only the relevant ones for that particular form..
will everthing automatically link with the job id if my user fills in this form..
if this is the case then i can see my self making one large query.. with all the relevant job details, and then using a tabbed form to separate them.. is this a common practice.. is this the correct way to go about things?
View 5 Replies
View Related
May 5, 2015
I have included the form control [Forms]![AddMatterFrm]![MatterIdFld] in the criteria of the first of three queries.
1st Query - with the form control isolates transactions relative to the contents of the form variable (coming from an open Form)
The second query is a XTAB query further processing and summarising the transaction.
The 3rd one adds another expression field.
I execute the 3rd Query using the command below
Set rstDebtTrans3Qry = db.OpenRecordset("DebtTrans3Qry")
I then get the ....Database Engine does not recognise [Forms]![AddMatterFrm]![MatterIdFld] as a valid field name or expression.
If I take the control reference out and hard wire the value I want - there is no problem.
I'm sure it has something to do with the XTAB query but I need a solution.
View 9 Replies
View Related
Aug 7, 2015
I have a Inventory DB and i want to integrate scanning. So far i have a table called StockInfo, this table holds information about bar codes (device type, make, model). I have a form called Scan_Barcode, on this form I want to be able to scan a bar code into a text box (text0) and use a afterupdate and requery function to display the results onto a form called EnterInventory.
Not only just the results but I want certain columns from the query to display in certain text boxes on this form. I have a query called FindProduct. that filters the criteria by whats entered on the Scan_Barcode text box (text0). So when i scan a certain bar code i can get the query to display certain product information for the bar code. I just cant get the results onto the HarwareStock form.
View 8 Replies
View Related
Sep 7, 2011
I'm trying to set up a simple query that links four tables. However, the tables are extremely large, all in excess of 1.5GB each so I had to split the tables up into four separate DBs. I've tried the following with no success:
1) Link the 4 tables in the DB which contains my primary key. This quickly inflates increases the file size above 2GB and won't let me go any further.
2) Build a remote query to connect the four tables. This looked promising until I tried to run the query and it became evident that it only knows to point to the last database source that you specified.
I'm running everything locally on my C drive. The data source are simple text files (1.6 million rows) from the FDA website.
View 3 Replies
View Related
Aug 22, 2014
i have a database that runs updates from within itself.what i need is, this database to then open a another database run a update query, then close it.
View 4 Replies
View Related
Oct 28, 2005
Hello All,
I'm trying to run a UNION query that joins five queries through a MS WorkSpace into a DAO.recordset in VB. I'm pulling the data from a SQL Server Database through VB in Access. I'm attempting to open a recordset with a query passed to it as a string. The query is below. For some reason, I'm receiving a message: "MS Jet database engine cannot find the input table or query. Runtime Error 3078".
Here's what's puzzling. When I run a single query without any UNION statement, the code finds the table and runs fine without error, but anytime I join two or more queries with a UNION statement in the VB, it gives me the error.
I've executed the same UNION query in both Access Query Builder and SQL Server's Query Analyzer and they work fine in both environments. It's only when I call the query from a DAO.Recodset with VB that it causes this problem. The following is a sample of the UNION query joining two of the five queries. Does anyone have any idea what could be the problem? The following query executes in about 5 seconds so I don't think there's a "time-out" issue. I'm thinking that the UNION statement may be the culprit. Maybe there's another way to approach joining these separate queries? Any help would be most appreciated. Thanks.
SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((CategoryID) = 'HCPROD') AND ((BrandID)<>'CSS')) AND (((BrandID)<>'1356')) AND (((BrandID)<>'1400')) AND (((BrandID)<>'1551')) AND (((BrandID)<>'555')) AND (((BrandID)<>'66'))
AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID
UNION
SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((ProductID) = '0029800')) AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID
Set wrkJet = CreateWorkspace("", "pw", "", dbUseJet)
Set db = wrkJet.OpenDatabase("DW", _
dbDriverNoPrompt, True, _
"ODBC;DATABASE=DW;DSN=DW2")
'Set rs1 = db.OpenRecordset(strSQL)
View 9 Replies
View Related
Aug 31, 2012
Is there a way to open a form from a form in a different database? I'm pretty sure it needs to be done in VBA and have seen some code floating around that suggests it can be done, but I'm thinking there's more to it, like maybe one or both of the DB's need to be in a different format?
View 9 Replies
View Related
Jun 24, 2015
I have a database with a Main Menu Form, containing a Button that loads my main data entry form. When the Button is Clicked portions of the data entry form that is loading shows through the Main Form Background (e.g. portions of the navigation bars, and portions of the boarder on the form that is loading.)
View 5 Replies
View Related
Jan 24, 2006
Im trying to create a database for a small hotel but Im having trouble making each booking individual.
I have customers who I can make individual by making a autonumber - customer ID and I have a seperate table for rooms but in making a booking table i can't have two autonumbers, as in I cant have an autonumber for customer ID and one for Booking ref. any help?
View 2 Replies
View Related
Mar 13, 2006
Hi guys hopefully you can help me out.
I was presented with a challenge to create a form that users will fill out online. There are a handful of questions mostly with check box answers, and a couple of areas to type your name and the date.
NOTE: this form is currently only on paper...
Now how do I go about making this form into an "online" version?
Basically I need users to be able to fill out this form online and then have the results collected. And possibly have the admin be able to generate a report of the forms that have been submitted.
View 4 Replies
View Related
Aug 18, 2005
Hello,
I have 10 databases . ALL identical and used for different vendors.
All have same table structure ( invoice, price, etc , etc tables)
I want to run a query where I type in the invoice number and it should query all the database and display the appropriate information that I need.
Would appreciate any ideas or suggestions. Thanks
ALSO databases are all on one server.I would need to connect using ODBC
View 3 Replies
View Related
Jan 7, 2008
Is it possible to open a database from a form in a different database? Not necessary to do so; it would just be handy to do from the form I already have up and looking at rather than having to go open another instance of access and go to that database.
View 8 Replies
View Related
Aug 19, 2005
My form enters the data into my table just fine. when i view the data in the TABLE, I can see all records and use the record selector at the bottom of the window, just fine...it shows 1 of 9 or howver many records. here's my problem. I want to enter as well as view my records via the FORM and when I open the form, it let's me enter stuff but it only update the record selector according the current session.
Example: I open my form, enter 4 records. The record selector shows 4 of 4. I then close my form. When I open it again, the 4 records are in the TABLE, but the FORM (blank and ready for my new entries) shows 1 of 1 again. The entries (records) go in but the record selector never reads (counts) anything entered in the previous session, even though the records ARE THERE in the TABLE (as they should be). HELP!! What control have I mixed up?
View 1 Replies
View Related
Nov 7, 2005
I know this is pretty basic, but it is annoying me. Whenever I bring up a certain form in my database, it seems to call up the database form. How can I get rid of this.
View 9 Replies
View Related
Jul 11, 2006
Im creating a form to add data to a database and my issue is that when I hit the save record button it overwrites the first line in the database and I can't figure out why it would be doing this. Any ideas?
View 1 Replies
View Related
Nov 29, 2005
HI,
I'm trying to create my own database from queries. the queries retreive data from and ODBC data source. How do I create table from query but I don't want any old data from table to be deleted whenever there's a change of data in ODBC data source. I just want to keep pushing in new information without deleting the old ones, even though those old information have been deleted from the ODBC source.
thx guys!:D
View 1 Replies
View Related
Jun 1, 2006
Hi,
I am currently using a large Access 2002 database in order to generate various reports.
My two main tables are despatches and returns from which they hold around 1,200,000 records and 100,000 records respectively.
The problem I have is that the reports use various expressions within various queries to generate a single result (percentages per channel etc.)
This is obviously very time consuming and it may take up to around 10 to 15 minutes to get a result from a chain of around 5 queries.
Can anyone suggest alternative methods to generate similar results in quicker time?
(Please note that the tables can not be downsized and records can not be archived)
Many thanks
:)
View 6 Replies
View Related
Dec 1, 2006
I have a database with all of my contacts... I have a report that shows my contacts in alphabetical order and I have a form for adding new contacts. I was wondering if there is a way to have a report or query that will look up a contact by typing in the person or the company name.. Is this possible and if so.. how do I do this?
View 1 Replies
View Related
Aug 30, 2005
I am building a simple assets database to help me with my work. This is what I did:
I created a table with following columns:
1) Bill ID - unique number of the asset bill, created by the accounting software.
2) ID of the supplier
3) Month
4) Type of the bill (code such as RI for investments, RO for activated assets etc.)
5) Date
6) Basic non-taxed value.
There is a 20% tax on assets. 19% OF the 20% tax is added to the base non-taxed value - I get the starting value of the asset before amortisation begins.
So far so good, my query works perfect, does everything right, however:
Every now and then (less than 10 times per year) I get assets from suppliers that are free of tax. That means that the value on the bill = starting asset value, and there is no need for adding the 19% of the tax back to the asset value. I don't know how to exclude those bills from the process.
The way I figured it theoreticaly, it can be done like this:
1) I could store the tax-free supplier codes in a separate table and make the query perform a check against the column 2 (supplier ID), and if it findds the supplier there, the base value is taken as a final value.
2) I could make all non-taxed bills have an unique tag for the non-taxed bills(column 4 - type of the bill), and make the query check against those.
I have no clue how to make any of those, so I hope someone here could help me.
Thank you!
View 1 Replies
View Related
Mar 19, 2007
Hi all, please help.
I have created a append query, which runs fine.
But unfornately whenever I try to open this query in design view it crashes the whole Access Database.
This is extremly frustrating as I cannot amend this query, or even check it to remember how it works.
:eek:
I cant see a single question on these forums which, relates to this problem.
Please help if u can!
:confused:
View 3 Replies
View Related
Apr 22, 2007
Hi;
i designed a database
it is controlling the stock and the test results
but there is a problem when i was using the query
because i entered the table data like
"1111 Microsoft Access"
"2222 Microsoft Excel"
when i executed the query i must enter the exact data like "1111 Microsoft Access" but i wanna only use "1111" for executing the query.
Please help me how can i do that?
Thanks for your Help.
View 6 Replies
View Related
Oct 17, 2007
I have 12 db that I would like to ectract labor hours from, then put all data on a report. Is this possible? Please explain how too.
Thanks
View 4 Replies
View Related
Feb 3, 2005
Hello,
Ok this may seem simple, but I can't think of an "easy" way to do it.
How can I query all tables in a database (that are not related in any way, and shouldn't be... for a reason) to find a specific string.
The example is, the db contains tables of pc's at multiple schools, sort of a primitive inventory. Each school has its own table at this point, because we may eventually give each school its own db in the future (and once we clean this one up a little!).
If I am looking for a pc's service tag, but don't know what school's table it's in, I have to open and individually do a "find" in every table. Is there a way to simply query all tables in a db without the tables being related? Is this a union query? If so, is there a limit to how many tables can be part of that?
Thank you!
LMS
View 2 Replies
View Related
Jun 17, 2005
is there anyway to view records in an access database from an existing database? i have 3 databases that perform basically the same things, but are for different people...
i would like to create a database that can report all this information in one spot, instead of creating reports in every database. if this is not possible, i'll probably go the asp.net route, but this seems to be an easier way, if it is possible
what do u think?
*j
View 2 Replies
View Related
Jul 20, 2005
im trying to get a site working
www.peck-ed.com/search.asp
but im having some problems with the search on asp. the problem as u can see is the fact it has to be the exact item for the search to work, atm the query is
Select * From PLIST Where PLIST.DESC Like "::ID::"
but i need it to work on single words from teh database rather then the whole thing, anyone got any ideas if i can change that so it will pick up on words rather then the whole item?
Amiga'Ferrari Formula One'
use that if you need to find an example of data, its got all the stock records of the shop dating back for years :P
View 4 Replies
View Related
