Tables :: One Query From 2 Tables Within Same Database
Jul 29, 2013
I have one database that I am working on and within it there are several Tables. 2 of the tables have similar information on various organisations and I need to query these 2 tables to get a simple mailing list of ALL organisation addresses and the main incumbent there.
Within Access (2007) and in the SQL View window, I have made a simple query to give me a list of all the organisations and incumbents but cannot get it to work correctly. Is there an easy way to get this information out of the 2 tables? I realise that the SQL in SQL View has to be formatted differently. I have also tried the Microsoft Query Wizard but that doesn't work either as it gives in a side by side list which is useless to me.
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 am splitting a database and have created the Back end already. When I create the front end and link to the tables on the back end... The front end does not link to all the tables in the back end. The list that comes up when creating the linkings does not show all the tables in the back end. What would cause this?
I have broken up by master database, table, into several tables. They all share the same ID values since they came from the master table. So each table that I split off has a unique, matching, identifier, ID, in both the master table and the new table. Plus a previous matching identifier listed immediately below.
Example: Permit the master or main table and Builder a supporting table. There are five tables that I split off and all have an identifier similar to the two listed below.
Permit->ID Permit->BLD Builder->ID Builder->BLD
All my tables have the same ID. But each table also has a identification numbers such as builder BLD, Subdivision SB and inspector INS>
Permit has the BLD, SB and INS
Builder has only one occurrence for each builder in the Permit table. As in the past it was identified by BLD number and now also ID. the use of BLD is from when I used visual dBase. The big difference here is that I have two ways to identify one is ID any other is by BLD, etc. My first thought is to identified by the BLD. I don't know why, but I guess I fear using a single "ID" to identify my various split off tables unique value or BLD.
looking for a way to export the list of table names, table types & if they are linked (e.g. tbl Sales Linked .dbf or tbl Staff linked to excel) from a database - this has to be done for about 300 databases.
in an individual db, I have a make table query off of the table MSysObjects to get the data. The Database field tells me where the source of the linked table resides & the ForeignName field gives me an idea of the format of the data source (e.g. dbf or excel). I could manually import that query into each db, run it to get the table names, then copy & paste..
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?
I am playing around with a sample Access 2007 database that has a number of tables, forms, reports queries and macros.I would like to rename then so that the names make it easier for me to recognise what they are when listed in a drop down list.
For example, I have a form named "Workorder Parts" and a table named "Workorder Parts" and I would like to rename the table to "tWorkorder Parts" and the forms to "fWorkorder Parts".I am assuming that these are referenced throughout the database and would like to know if there is a simple way to do a rename on mass?
I have a rotating number of tables that are created from excel spreadsheets that are imported. The Tables will change, but when they are there I need to be able to create a query that will merge them all together so I can run one query against all the tables. In SQL I know you can use a * to say Select * From Table_1
Is there a syntax for the From portion so I can say:
Select * From * (AKA all the tables in the Database)?
I have searched for a wildcard for the FROM statement that works like in the select statement but have been unsuccessful at finding an answer. Can anybody help? I'd list the Tables in the from Statement but there are 266 of them. Unless someone knows how to say:
Select * From All tables in a folder with 266 excel spreadsheets
I am running access 2007 in Win7. I have a time sheet application that I wrote many years ago. Within same I have a button to press to open a new form to enter time sheet data. The data for the form comes from a query that links Employee and the Timesheet history data file.In an effort to upgrade and make the application more portable and available to multiple users, I decided to migrate the data tables to a Microsoft SQL 2012 database. Various Access reports based on the SQL database run ok. But if I try to open a form to add a new record I get the following error message:- "Run-Time Error '2105' You can't go to the specified record."
If I debug the error it shows:- Private Sub Form_Open(Cancel As Integer) DoCmd.GoToRecord , , acNewRec End Sub
In an effort to make sure the original time sheet form load worked ok on the original tables, I changed the query to feed off tables in the local Access program and have no issues. It works perfectly. Also if I open the Timesheet Query that feeds the form I can add new records.However if I change the Query to look at the same tables on the SQL database I cannot add records. I can search all records from first to last but not add a record into the query. The add record arrow is greyed out on the query results.
I have a split database and need a field (Combo type) in the table to lookup values from a query in the front end. How do I do this as it doesn't see the querys because the front and back end are split?
I have a question where I need to effectively invert the results of a select query.
I have multiple tables in the database, but the 2 I'm looking at are TBL.Trip and TBL.TripDiary
A trip is logged in the trip table, and then an operator logs a diary entry against the trip. One of the options that the operator selects is when they receive an email back from the traveller, this is logged as a diary entry.
I need the query to look at the DIARY_Action field and select all records in the TBL.Trip that DO NOT CONTAIN an entry for Diary Action "4".
I can produce a select query that gives me the results to see all trips that HAVE had a response, with a simple IS LIKE "4" query in the action field.
If I change this to IS NOT LIKE "4", I get the results that I need, but duplication due to the various other "diary" entries in the table".
I simply need the inverse of the IS LIKE query but cannot see how to remove the duplicates?
I have some experience doing 'Update Query' using two different tables but I'm having a hard time doing an 'Update Query' using 3 tables.
I have my source table TP05XY with the fields 'Mark' 'Date' 'UTM_Edig' and 'UTM_Ndig'. Mark and Date are my primary keys (they together uniquely ID each record). I have my Observations table with the fields 'Mark' 'Date' and 'Obs_ID'. The last table is Locations with 'Obs_ID' 'UTM_E' and 'UTM_N'.
I want to update my fields UTM_E and UTM_N from UTM_Edig and UTM_Ndig. However, to do so, I have to go from my TP05XY table, through Observations table to update Locations table. Table TP05XY is joined to Observations through 'Mark' and 'Date' and Observations table is linked to Locations through 'Obs_ID' field.
I have tried a few options without success ... anyone knows how to do it?
I am using Access 2010.I have a table that I am using to pull my data from other tables and a query.My table is called tblMyData.One of the field names is level1. This field points to another table, and gives the user the choices for states (examples California, Texas, Maine).
Another field name is level2. This field points to another table and give the user the choice for type of customer (examples Business, Consumer) The field name level3 points to a query. The query, qryFinalChoice matches up the choices based on level1 and level2.for example, the user can pick California for level1, business in level2 and California Widgets or Los Angeles Clothing store in level3 (plus about 20 other choices).
If the user picks Texas for level1, business in level2, business in level2 and Houston rugs, or Texas style restaurant in level3 (or about 15 other choices).
-I am recording 1 for California on level1, 2 for Texas and 3 for Maine in level1. -I am recording 1 for business and 2 for consumer on level2.
The query qryFinalChoice has all the combinations for state, business or consumer, and lastly business name or consumer name.
-qryFinalChoice has line1 to match up the choices for level1 in my table. -qryFinalChoice has line2 to match up the choices for level2 in my table. -qryFinalChoice has line3 to match up the choices for level3 in my table.
I do not want any of the Texas business names appearing when the user picks California, or vice versa.
My SQL in my tblMyData tab for level3 looks like this: Select line1 from qryFinalChoice where line1=1;
I am able to get all the line items where California is a selection.How do I change my SQL to pull all the line1 choices where I have selected from level1, and all the line2 choices where I have selected from level2 automatically based on my pulldowns?
I'm looking into storing query data in temp tables for my reports run better. From what I'm reading, it seems best to have the temp tables in a separate db, and to break the links to avoid bloating of the FE database. I'm unsure how to do this with VBA, especially since my temp database will be password protected. When do I break the link - when I close the FE database?
I am using collect data via email process to collect data by email, purpose is to update not to add new records but to update. The condition for update is to have data from one table only.
I have 4 tables data from which should be sent by email. These tables are related. I made a query based on tables and query is update-able. When I use this query, wizard does not give option of updating the data but only of adding new records.
I tried to first make a make table from a make table query but that too have the same result.
Any ways to make treat these tables in a single table? Almost all fields except one shall be just to read and one field shall be updated.
Currently trying to build a database for customer management and order placement/tracking. Want to set a couple of rules so that if I for instance click yes of billing and shipping address the same that the database will automatically fill the shipping address with the data I inputted for the billing address in the same table.
The other issue I can see I'll run into is, I want to be able to select one of the company ID's (made up of a three letter abbreviation of the full company name) in the product ordering table and it will automatically fill in the rest of the customer data (phone, email, address etc) data into that form.
I am testing the security of my DB X on Acccess 2007. I could create a new database Y and linked to the database X. Unfortunately i could change the records on the tables. I don't want other DB that make connections to my DB to change my tables!
I have several visual dBase tables converted over to Access 2013. The old tables use identification, SEQ. I have retained the old SEQ , but now have the Access ID. It seems I somewhere saw that if you delete an ID, you will be changing all the subsequent IDs that follow. In other words, if you have ten records that have IDs of 1-10 , and delete record five, ID 005, record ID 006 now becomes 005.
Is that true. Is it a good policy to count on the ID number when you at times delete records?
True, I could test this theory myself, but I'm concerned that there might be conditions where it could be troublesome.
I am trying to re-create a db system which used a backend database for some of the information. I have opened the backend db in access and there does not seem to be any information in this database when I know for a fact there is.
How do I go about viewing the tables etc in the database.
Thanks for considering my question. I have developed a DB with about 60 tables, all related by one-to-many relationships. During development, I placed values in the tables, generated keys, etc. so things have gotten quite cluttered. Is there a way (vb program, access method, etc.) that I can use to remove all the entries from the table, and essentially start with a set of clean tables and no keys? I'd like to be able to run this from a control on a form.
I wonder if there is a possibility to select a table from a database by using a sort of explorer window. My problem is this: I want to import a *.xls file in a table. I want to be able to select a certain *.xls file from a list (so far no probs) en add it contents to a table selected in a browser window. A table ofcourse who the exact field structure.
