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
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.
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 RelatedHello 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)
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
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
:)
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.
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 RelatedI 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!
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:
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.
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
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
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
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
I have an Access Database that queries TagNames from an SQL DB(dbo) and puts them in a new access table(Consolidate). The query adds a field(Time) which looks at the EventTime of the item in the SQL Database. As of now the query is a 12 hour query finding items that occurred in the SQL DB from the previous 12 hours, ie (Criteria: >Dateadd("h", -12, Now()).
I am trying to change from a 12 hour history to a dayshift and night shift query. This would be 6am-6pm and 6pm-6am. So, I am creating another field(Expr1) where, Expr1: IIf((DatePart("h",Now())>=0) And (DatePart("h",Now())<=6)DateAdd("d",-1,Now()),Day(Now())). The criteria is: >=TimeSerial(6,0,0) And <=TimeSerial(18,0,0) This query should cover the dayshifts history when the nightshift runs the query. If the system time is greater than midnight and less than 6am use the previous days criteria, else use todays criteria.
Is is possible to copy queries, reports, modules, etc. from one database to another, and if so, how? I have some queries and reports in an inventory database I'd like to copy and use in another database without having to set them up or re-code them again.
Thanks,
Rod
why is it that as soon as you fix one problem another one needs dealing with :mad:
OK, the problem i have is that i have a BE/FE configuration database in a multiuser environment. I have built all the tables and the relationships in the BE configuration. In the FE configurations i have built the main forms to input data. It has no come to the point where i have begun to create the queries to allow reporting and data searching. And this is where i have a problem. I am trying to create a simply query and i do mean simple (at the moment i simply want 2 fields from 1 table and 2 fields from another table) no calculations or anything i just want it to display the data. I have tried creating this query numerous times and i keep getting the same error.
"The wizard is unable to open your query in datasheet view, possibly because another user has a source table open in exclusive mode. Your query will be opened in design view"
the above is the error i get when i use a wizard to create the query, after getting this message and going into design view i try to view the results in datasheet view and get the following error "Type Mismatch in expression"
If i dont use a wizard and create it in design view, when i go to view the results i get the second error message everytime.
If i create a query from 1 table only the query works without errors, but i always get errors when i have more than one table in the query.
Also i am the only person with the database open so therefore it is total impossible for the database to be open by another user in exclusive mode.
Anyone know what the problem might be or how to fix. Your answers will be very much appreciated.
I used to know how to get the path for the current database by running a query. I forgot how to do it. I am using Access 2003. I have tried querying the msystem tables without success. I have searched forums and the web without success. Anyone? Thanks!:)
View 1 Replies View RelatedI will try to explain this as best as possible, please let me know if there's any more info i can provide in order to get a better response. I am making a database that links the following tables:
Product
Fund
Company
The value for each of these combinations will be a simple yes/no, but the problem with the relationship design comes in because of the companies. For each company there are multiple smaller companies, for instance B company is comprised of a1, a2 and a3 entities. The information I am puting into the database is specific to the a1, a2 and a3 entities, not B company as a whole. What i need to query is B company as a whole. For instance, i need to know that B company can sell widgets(product) through 1, 2, 3 and 4 (funds).
I can't think of a way to consolidate the data from the smaller companies into the data for the company as a whole, especially since the smaller companies may sell the same products in the same funds. My current Table relationships looks like this:
http://img338.imageshack.us/img338/5282/tableif8.jpg
but i don't know how to manage the company as a whole(eg. B Company above)
The second problem i have is once i have the data for the company as a whole i need to be able to query multiple funds. I thought this was going to be easy at first by switching "or"s to "and"s but, even after playing with parenthesis any "and"s that i add just make it so it is looking for "1 AND 2 AND 3 AND 4" in one field. what im trying to make it do is query (Product = z, Fund = 1, available? = yes) AND (product = z, fund = 2, available? = yes) etc.. and have it output a list of Companies (eg. B Company)
I realize that I can query like this by querying a crosstab with funds as columns, but the problem with that is funds will change because I have 5 groups of people using the database who want different sets of funds (and it will output entities, not companies).The database needs to be dynamic so when one group of people changes their preference of funds or we add a new fund I dont have to make a brand new query/form/report.
Any comments/quidance is appreciated, please let me know if you need any more information. Thanks! :)
What query do I use to add data from one db [same file names] to another db?
View 1 Replies View RelatedI need to produce a query that will show only one File and location even though there are multiple records contained within that File. To clarify,
In each individual record I have Id_No, Surname, File_Name, DOB, Location.
I need to shred the file once the person is over 25 years old, but some File_Name have different people with different dates of birth. I would like to display the File_Name only if all of other records in that File_Name are over 25 years. I have attached a picture of the query which I use to find the over 25's
I have table tblLog set up to track logging on and off. All I am tracking is the date and time the user logs on and logs off. That's pretty simple to do. When logging on it appends the userid, date, and time to tblLog. When logging off, it updates the logoff date and time for the matching fields.
What I need to know is this. If a user closes the database without using the appropriate buttons within the program, such as Ctrl Alt Del, or right clicking and using the Close command, or if the power shuts off, or whatever, is there a way to get that last update query to run...
I have a large database of names, I would like to produce a query that reveals anyone that appears in the database more than once.
For instance in the image below "Sayprasith, Nick & S Kaye S" are listed twice in the database. I would like an output showing anyone that has their name repeat two times or more within the "owner_name" column. Ideally the output would be in such a way that preserves the "account number" field as well.
Based on the above image the ideal output would be:
Account Number Owner Name
00015121 Sayprasith, Nick & S Kaye S
00015148 Sayprasith, Nick & S Kaye S
Only it would be for the whole database that consists of many thousand records.
I have a database X, I want to append data from tables in another database Y, into database X (tables in both databases have the same name: [Y].[Names] into [x].[Names]).
My question is:
How to make this happen by allowing me to search for Database Y (like if i want to save a Word file, a window will open searching for the folder i want to save the Word file in).
1. how to convert an access database (query) to a web page.
2. what knowleage we should learn ?(php,asp....)