Query That Will Select All Tables In The Database And Create One Table

Sep 16, 2007

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

Thanks for your help
:)

View Replies


ADVERTISEMENT

Create Table AS Select Query

Oct 25, 2007

Hello all,

Is there anyway to create a table from a query like you can in oracle?
i.e.

Create Table [B_match] AS
SELECT *
FROM [B_All]

Take care

View 2 Replies View Related

Queries :: Multiple Tables In Database - Inverting Results Of Select Query

Feb 5, 2015

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?

View 5 Replies View Related

Tables :: Create A New Database Y And Link To Database X

Aug 28, 2013

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!

View 1 Replies View Related

Forms :: Select Multiple Records In Subform To Create New Table

May 21, 2013

I need to create some new records based on main form data and a selection of records from a sub form. The main form and sub form have different sources. I wanted to show the source fields in the sub form along with a check box to allow the users to select individual records. The record source for the sub form contains >1000 records, so the user will first enter data in the main form, use filters to find the records he wants to 'assign' to the main form data, click those he selects, then click a command button in the main form to create the record(s) based on the main form data and the selected records from the sub form. The new records will be appended to a new table.

View 6 Replies View Related

TRYING To Create A Simple Select Query

Nov 14, 2005

i've created a database for city permits to be issued (which works perfectly thanx to alll your help), but now i need to be able to print out 1 permit at a time when it is issued (immediately after the data has been entered into the forms). i have 3 tables & 3 forms: contractors/owners, permtOrders (which has tabs for the different types of permits), and permitOrderDetails - each form has a button to open the next. now i need to be able to print an actual permit (like the carbon copy, landscape kind that usually tears out of a book). once the user enters the information into the forms, i need a little bit of that info from each form to be carried onto the actual printable permit. i've tried to create a query w/data from the 3 tables, but it isn't returning any results. the relationships between the tables were crreated w/the wizard & i've tried created the query a couple of different ways. help :confused:

also (i know...i'm dumb), if someone could tell me how to take a snapshot of my query it would help w/my explanation of this problem. i only know how to do this w/a report.

thanx in advance :)

View 6 Replies View Related

Modules & VBA :: How To Create And See Results Of SELECT Query

Mar 22, 2015

I was beginning to think I had got the hang of creating queries on the fly in VBA. After several hours I have just learned that docmd.runSQL does not work for simple SELECT statements. I'm not going to worry about why that would be. I'm sure there's a jolly good reason.

So... I am able to dynamically create the SQL string for the SELECT statement that I need.

How do I run it? It just needs to return results as a datasheet so I can see them. I'm running Access 2013. I've been searching on this subject for quite a while and found many references to DAO, ADO, ADODAOD, YODELAEYYOUDELAYIO! and other things I don't yet understand ...

View 8 Replies View Related

Create Input Form For Database With 88 Tables

Apr 30, 2014

I have a database with 88 tables that have 5 fields each. Each field has the same name in every table. The ideal situation would be to have a single table to hold the data currently stored in the 88 tables but each table holds different information. Each of the 88 tables is related to 3 additional tables that hold header information. Needless to say the header info will be identical for each entry of the 88 tables. Access is limited to 256 fields per form so having all fields in a single form has not worked this far. Splitting the forms into groups of tables somewhat works but the header info needs to be inputed every time and it creates duplicate info. Creating a single form with multiple tabs does not work due to the 256 field limitation.

View 14 Replies View Related

Tables :: Data From A Database To Create Excel Spreadsheet

Aug 15, 2014

I'm exporting data from a database (using a query) to create an excel spreadsheet. I then import said spreadsheet into a new database. I was hitting lots of problems (subscript out of range, violating this that and the other etc etc) which I cleared up and actually imported the data. Well today I cleared down the new database and imported the spreadsheet again and got this.

the contents of fields in 0 records were deleted and 0 records were lost due to key violations.Thereby followed what to do when you get these things happening.Now to me 0 records deleted and 0 records lost means it's all worked. WRONG!! No records were imported at all.

View 5 Replies View Related

Tables :: Split Database - Table Field To Lookup Values From A Query?

Jul 6, 2015

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?

View 3 Replies View Related

Tables :: Create Database For Managing Nonconforming Product Internally

Jul 25, 2014

I am attempting to create a database for managing nonconforming product internally.

Different stages will need to be assigned to an individual within the organisation, is it possible to have a lookup value in my table which draws its information from active directory?

All the users are obviously in here and managed already, I dont want to have to create and manage a separate list of users just for my database.

View 2 Replies View Related

Queries :: Create Parameter Query To Select Which Column To Calculate Weekly License Info

Mar 2, 2014

Basically, I have a database table that is maintained every week. It is about e-mail account licenses for Office 365.

Each column represents a week of license data for every mailbox account which is about 10 000 plus users with the date as the field headers( something like "License information as of 06122013").

Basically, I have created 52 queries based on the license type I require to be calculated & I have a form created to control it. However, right now it is all output only as I have yet to create any user parameter to specify on any of the queries about the date I want

As I am currently unsure how to specify all the queries to use one field date header which I want the user to specify via drop down list in order to calculate all the licenses on this specific date.

For example, if I want to see the license information for 06/11/2013, I would choose "License as of 06112013" & it would then run all the queries based on the header specified earlier & output that information on the form.

Right now the queries are all configured as the "Select" type.

View 1 Replies View Related

Tables :: Pull Field From Main Table And Create A New Table With Date Stamp

Nov 4, 2014

I have a make-table query that pulls all the fields from 1 table (MainTable), and creates a new table with a date stamp based apon a form value entered (New Table = MainTableWithDate).

Currently, I setup the query to pull info from the form field like this:

DateField: [Forms]![frmmain]![DateField]

However, when the make-table query is done - all date fields are blank (all other fields are correctly created), and when I look at the new created table (mainTableWIthDate), the typeassigned to the date field is "Binary" (in the form, I've specified LongDate).

View 6 Replies View Related

Create Table Of Different Columns In Three Tables Join To Form A Master Table?

Jun 4, 2015

I have three tables that contain different columns but linked by a primary column call Name. I want to create a table where all these different columns in the three tables join to form a master table which can be updated regularly either through the master table or the smaller tables. The master table also has the primary column as Name.

If I update the master table with records, it should update the respective linked table and vice versa. I also want to link these tables to my SharePoint site.

Note: except the Name column, none of these tables have any other columns in common How do I go about this?

View 7 Replies View Related

Tables :: Unable To Create A Database By Compiling Records From Multiple Excel Sheets

May 22, 2013

I am trying to create a database by compiling records from multiple excel sheets. however, since most of the clients still uses the excel sheets to enter records, I am thinking if it's possible to create a linked table in access from these excel sheets that are hosted on an online server.

also is it possible to merge multiple linked tables together? if they have the same field range, so they will append when the linked tables gets updated.

View 3 Replies View Related

Create Tables From Another Table

Oct 17, 2005

Hi All

I am trying to create a database for estimating manhours on projects. There is a setup table with two columns: "Project" and "Time Period". The database is supposed to allocate manhours to tasks on projects, which is used in estimating man hour costs and also forecasting labour resource requirements.

For example, say the database is used for "Project X" and "Project Y" projects, with Project X running from Jan to March and Project Y running from Aug to Dec. The setup table would look like this:

PROJECT TIME PERIOD
Project X Jan
Project X Feb
Project X March
Project Y Aug
Project Y Sept
Project Y Oct
Project Y Nov
Project Y Dec

From this table, I need to automatically create a new table for each unique project that allows man power to be allocated to each time period. Following on with the example, there are two unique projects, so two tables
need to be automatically created with column headings as per below:

Project X table:
FUNCTION PERSON JAN FEB MARCH

Project Y table:
FUNCTION PERSON AUG SEPT OCT NOV DEC

"Function" is a description of a role, for example engineer. "Person" is the individual undertaking that role, for example Bob Jane. Then in each time period a number between 0 and 1 would be entered corresponding to how much time (0 is no time, 1 is full time) that person would be spending on that function for that time period. Then the table would be populated with many functions and people in this manner.

So the number of tables automatically created depends on how many unique projects there are in the "Project" field of the setup table. The number of columns in these tables will be atleast two: "Function" and "Person",
plus another column for each time period defined for that project.

Perhaps something can be done with a make-table query, but all I can get that to do is copy data from one table straight into another table.

Any help would be greatly appreciated.

Thankyou in advance.

Joey

View 14 Replies View Related

Create A Table From 3 Other Tables

Jul 17, 2007

Hi, i currently have three tables.

Policy table: Policy number(key),date and fund(4 possible strings of four letters)
Last Price: date, fund, last price
Next price: date, fund, next price

What would i have to do to make a table that has a policy number, date, fund, last price and next price?

Thanks

View 1 Replies View Related

Cannot Create New Table Linked To ODBC Database

Mar 7, 2006

I can edit/open existing linked tables (to Sage Line 50, v12) but not create new ones.

Following the procedure:
- New
- Link Table
- File Type: ODBC databases ()
Nothing now happens!

I can open the Link Table Manager to update previously created links.

Any ideas?

I'm using MSAccess 2003 on a brand new machine.

View 1 Replies View Related

Modules & VBA :: Create Backup For Table In Another Database?

Jan 7, 2014

How to create backup for my table in another DB with VBA ....

View 3 Replies View Related

Tables :: Create Table With Dates And Day Name

Apr 26, 2015

I am very new with access database. Want to create table with Dates & DayName. For example, tblYear2015 with all dates from 1-Jan-2015 to 31-Dec-2015 in FirstColumn and DayName in second column as per date of First Column. DayName Should be entered automatically as per date entered in first column.

View 6 Replies View Related

Tables :: Create One To Many Table Entries?

May 8, 2013

I'm new to MS Access, indeed database design as a whole.

I have been tasked with creating an issues logging database and am having some issues...

I have a table/form that has all the fields I want, including a unique ID, called "Issues". I want to be able to log multiple entries in another table called "emails". The trouble is I am not having much luck. I can create an entry in the issues Form which then shows in the emails but I can't then add another email under the same ID in the "Issues" table.

View 2 Replies View Related

CREATE TABLE In Access Database Syntax Error..

Aug 13, 2006

Hi folks.. I'm trying to dynamicly create a table in an existing ACCESS database..

Here's wat i'm using:

PHP Code:<%
strDB = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("../../../imgdbase/vrienden.mdb") & ";Uid=Admin;Pwd=mljiscool;"
sql = "CREATE TABLE " & request("login") & " (id INT(10) PRIMARY KEY AUTO_INCREMENT, email VARCHAR(155), gevalideerd VARCHAR(10) DEFAULT 'nee', inlogCount INT(10) DEFAULT 0, lastLogin DATETIME)"
Set DBConn = Server.CreateObject("ADODB.Connection") 
DBConn.Open strDB
DBConn.Execute sql
DBConn.close
Set DBConn=Nothing    
%> 


Code:I'm getting this error:Microsoft OLE DB Provider for ODBC Drivers error '80040e14'[Microsoft][ODBC Microsoft Access Driver] Syntax error in CREATE TABLE statement./mljnew/welcomeportal/registration/registration/test.asp, line 7

What am I doing wrong ??? The code here is probably the MySql correct code (Access uses different data types / field names ???) Hope somebody can help me out, because I can't find the correct data types anywhere ! (like use TEXT instead of VARCHAR, that's all I know...)

Here's the actual sql i'm using (displayed by response.write sql):
CREATE TABLE roel (id INT(10) PRIMARY KEY AUTO_INCREMENT, email VARCHAR(155), gevalideerd VARCHAR(10) DEFAULT 'nee', inlogCount INT(10) DEFAULT 0, lastLogin DATETIME)

Thnx !!!!
Roel.

View 3 Replies View Related

Create Database From Query

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

Create A Table Based On 2 Tables With Different Fields

Apr 17, 2007

Hello,

I have attached a zipped excel workbook to best describe what I'm trying to do. I have table1 and table2 and I'm trying to write a query in access to get the output as shown in the workbook. I'm having hard time getting this right. I would appreciate your help if possible. Tks

Richard

View 4 Replies View Related

How Do I Create A Table That Contains Figures Calculated From Other Tables?

Feb 18, 2008

Hi,

I have two tables:

Table A provides total sales volume of the UK shoe market from 2000-2007.

Table B provides the sales volume of different shoe manufacturers from 2000-07.

I want a third table created, called Table C. This should look exactly like Table B but instead of sales volume it shows percentage sales that are calculated by using the figures in Table A and B (i.e. [sales volume from Table B/total sales volume from Table A] * 100).

Could someone point me in the right direction please (assuming that such a table can be created, based on a calculation of figures in other existing tables).

Thank you.

View 3 Replies View Related

Tables :: Create Log For Table To Capture Changes By Users

Mar 2, 2015

Is there a way of creating a log to capture changes in a table by users?

In a current database that I'm taking over, there was a lot of records deleted from the main table and now I need to figure out what happened.

So, if I would be able to have a log that records all the actions taken by users, I could at least see who did what.

View 13 Replies View Related







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