Create Table AS Select Query
Oct 25, 2007Hello 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
ADVERTISEMENT
Query That Will Select All Tables In The Database And Create One Table
Sep 16, 2007I 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
:)
Forms :: Select Multiple Records In Subform To Create New Table
May 21, 2013I 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 RelatedTRYING To Create A Simple Select Query
Nov 14, 2005i'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 :)
Modules & VBA :: How To Create And See Results Of SELECT Query
Mar 22, 2015I 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 ...
Queries :: Create Parameter Query To Select Which Column To Calculate Weekly License Info
Mar 2, 2014Basically, 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.
Create Table Query
May 31, 2005Hi
Is there a quick way to split up a table into many other tables? I want to split the table by Client Number, so all the Clients with 1234, for example, are in a table called Client1234
I know the very long winded way, creating a ‘Create New Table’ query, inputting the Client Code criteria each time.
Is there a better more efficient way?
Thanks
coley
Create Table Query
Mar 9, 2007Hi,
I wrote a script which creates a new table and append records into it. Here are my codes:
For Each rst In dbs.tabledefs
If rst.NAME = "Biweekly_Temp_Table" Then
dbs.tabledefs.Delete rst.NAME
dbs.tabledefs.Refresh
End If
Next rst
qryBtt = "SELECT DEDPARMS" & payp & ".EMP_ID, DEDPARMS" & payp & ".FORMAT_NM, " & _
"Sum(IIf(Right([DEDPARMS" & payp & ".Dedtype_CD],1)='R',[overded_am],0)) AS [Employer Amt], Sum(IIf(Right([DEDETAIL" & payp & ".Dedtype_CD],1)='R',[DEDETAIL" & payp & ".ded_am],0)) AS [Employer Actl], " & _
"Sum(IIf(Right([DEDPARMS" & payp & ".Dedtype_CD],1)='A',[overded_am],0)) AS [Admin Amt], Sum(IIf(Right([DEDETAIL" & payp & ".Dedtype_CD],1)='A',[DEDETAIL" & payp & ".ded_am],0)) AS [Admin Actl], " & _
"Sum(IIf(Right([DEDPARMS" & payp & ".Dedtype_CD],1)='E',[overded_am],0)) AS [Employee Amt], Sum(IIf(Right([DEDETAIL" & payp & ".Dedtype_CD],1)='E',[DEDETAIL" & payp & ".ded_am],0)) AS [Employee Actl], " & _
"First(DEDPARMS" & payp & ".STATUS) AS FirstOfSTATUS, First(DEDPARMS" & payp & ".AGENCY) AS FirstOfAGENCY, " & _
"First(DEDPARMS" & payp & ".TITLE) AS FirstOfTITLE, First(Right(DEDPARMS" & payp & ".title,2)) AS RepUnit, Left([DEDPARMS" & payp & ".DEDTYPE_CD],2) AS Type, " & _
"Left([DEDPARMS" & payp & ".DEDTYPE_CD],2) AS LeftType, Sum(DEDPARMS" & payp & ".NBR) AS SumOfNBR, First(DEDPARMS" & payp & ".DEDTYPE_CD) AS FirstOfDEDTYPE_CD1, " & _
"First(Right([DEDPARMS" & payp & ".DEDPlan_CD],2)) AS Tier, First(Left([DEDPARMS" & payp & ".DEDPlan_CD],2)) AS Carrier, DEDPARMS" & payp & ".DEDPLAN_CD Into Biweekly_Temp_Table " & _
"FROM DEDPARMS" & payp & " LEFT JOIN DEDETAIL" & payp & " ON (DEDPARMS" & payp & ".EMP_ID = DEDETAIL" & payp & ".EMP_ID) AND (DEDPARMS" & payp & ".DEDTYPE_CD = DEDETAIL" & payp & ".DEDTYPE_CD) " & _
"GROUP BY DEDPARMS" & payp & ".EMP_ID, DEDPARMS" & payp & ".FORMAT_NM, Left([DEDPARMS" & payp & ".DEDTYPE_CD],2), Left([DEDPARMS" & payp & ".DEDTYPE_CD],2), DEDPARMS" & payp & ".DEDPLAN_CD " & _
"HAVING (((First(DEDPARMS" & payp & ".STATUS)) Not In ('P'))) " & _
"ORDER BY DEDPARMS" & payp & ".EMP_ID, First(DEDPARMS" & payp & ".STATUS);"
DoCmd.RunSQL qryBtt
NOW THE ISSUE IS, AN ALERT MESSAGE ALWAYS POP UP SAYING SOMETHING LIKE, "You are about to paste 34590 row(s) into a new table." What would I have to do to get rid of this pop up? Eventually my users are going to use this and I don't want them to be alarmed when the message pop up.
Thank.
Joe
Create Table Query
Feb 13, 2007can I use create table query and change a fields data type?
I have a field with numbers, but when I get the table, it is set as a text field. I want it to be a number field, but I can't change the orignal table. That's why I was going to use a create table query, but I can't figure out how to change the data type from text to number.
Thanks
Select Query Using A Lookup Table As Criteria
Aug 29, 2007Using MS Access 2002
Need some help trying to filter out some data so it can be deleted from the main table so updated data can be pasted into it that table.
Table name is "dbo_VG_PropertyValues"
AppID - Number
DNIS - Text (This is the column i need filtered)
PropertyName - Text
ProperetyValue - Text
I had a query where i would just type in the 3-4 DNIS numbers that i needed to search for by using "11111" or "22222" or... etc... in the simple query view under criteria..
I'm now getting lists of 50 + numbers i need to do searches for, delete and replace with updated data. I can't run a string of "or" statements on 50+ numbers, it won't allow it. I created a table called 'DNISList" with the 50+ numbers i need to search for out of the "dbo_VG_PropertyValues" table. In the simple query view i linked on the DNIS number from both tables and set the join property to show all the records that matched. Hey it looks good, so far, i got the results i wanted but can't delete anything.
Is there anyway in the criteria field under the column DNIS from "dbo_VG_PropertyValues" where i can so a search on that table without linking the tables? I hope I'm making sense. You know something like:
IN ([DNISList]![TFN])
Queries :: Query To Select Part Or All Of A Table?
Nov 27, 2013I need to give the user the ability to select either all of a table say
[tblStores].StoreNo
or only those stores where [tblStores].StoreNo Is Null
I cant for the life of me get my query to accept Null or Is Not Null in the parameter box.
What do I need to do.The end game is to produce a subform to allow users to maintain tblStores
eg
tblStore
StoreNo | StoreNm
000001 | StoreA
<Empty>| StoreB
000003 | StoreC
Null returns
Store B
Is Not Null returns
StoreA
StoreB
StoreC
How Do I Create A Query Like A Pivot Table?
Nov 15, 2005Hi Forumers...
I have data in a table that looks like this...
SITE-ID | VALUE
Site 1 | 20
Site 1 | 21
Site 1 | 16
Site 2 | 8
Site 2 | 9
Site 2 | 12
etc...
I would like to create a query that allows me to show summary statistics for each site. eg...
SITE-ID | VALUE_MIN | VALUE_MAX | VALUE_AVG
Site 1 | 16 | 21 | 19
Site 2 | 8 | 12 | 9.667
etc...
Am relatively new to MS Access and can't work out how to create a query that does this. Any help will be appreciated.
Using MS Access 2000 (9.0.7616 SP-3) on Windows 2000
Thanks,
Chris Medlin
Create A New Row For Each Table Column In Query
Oct 26, 2006I have a table which stores multiple materials in different columns, and the analysis results for each material in the associated cell. I need to create a query which will display a new row for each material and result with all the appropriate associations (location, date, time, etc.) Is there a way to do this in a single query? Do I have to create a separate query for each material?
Ultimately, I need to create an excel file which displays each material, the analysis result for that material, and the associated site, date/time values in a new row for each material.
Thanks for any help.
Create VIEW From SQL Query On A TABLE Using VBA
Nov 8, 2006How can we do this? Can someone pls help? The query should be part of the code. Like create a variable strSQL = "SELECT * FROM ...." and so on
Newbie here :)
Little Help To Finish A Create Table SQl Query
Dec 26, 2006Hi,
I have the below in an SQL query that is called by a button (At present) that creates a table with an autonumber Primary Key (Can do a single Autonumber PK or a multiple field PK at the moment with the SQL code), and i want to create a table but the table name (An maybe some fields) to be from a field in the table. How can i do this, and is the below correctly written?? it works OK, but may be untidy.
CREATE TABLE Discharges ( MainID COUNTER CONSTRAINT MainID PRIMARY KEY, DischargeDate1 date,
Program1 varchar, Eligibility1 yesno, Cap1 currency, Phase1 varchar(111),
SRFA1 yesno, DischargeDate2 date, Program2 varchar,
Eligibility2 yesno, Cap2 currency, Phase2 varchar, SRFA2 yesno,
DischargeDate3 date, Program3 varchar, Eligibility3 yesno,
Cap3 currency, Phase3 varchar, SRFA3 yesno, DischargeDate4 date,
Program4 varchar, Eligibility4 yesno, Cap4 currency, Phase4 varchar, SRFA4 yesno, testlabel memo)
Have tried:
CREATE TABLE [Form1].[User] (
CREATE TABLE Form1.User (
and then made the record save (To store the field value on the form) before running the SQL code, but nothing happens (No errors)
Thanks
Dan
Run A Create-table Query Every 2nd Hour
Sep 18, 2006Hi
I have made a create-tabel query that I want to run every 2. hour. How can I set Access up to do this automatically?
eroness
General :: Need To Create Query From A Table
Apr 14, 2014I have an old ERP system. The upload function is one line per value. So if I want to upload 7 models I need 7 lines, not one line with Qty of 7
however my source system sends the data as one line with Qty of 7
DLM2245/17DLM2245/17X1
DLP2204/17DLP2204/17X7
DLP2249/17DLP2249/17X7
DLV1004/17DLV1004/17X147
DLV1005/17DLV1005/17X158
DLV1009/17DLV1009/17X187
so in this example line 1 is fine. But line two needs 7 lines, and line 3 needs 7 lines and line 4 147 lines etc etc etc.so this is how the first three lines would look in the query.
DLM2245/17DLM2245/17X
DLP2204/17DLP2204/17X
DLP2204/17DLP2204/17X
DLP2204/17DLP2204/17X
DLP2204/17DLP2204/17X
DLP2204/17DLP2204/17X
DLP2204/17DLP2204/17X
DLP2204/17DLP2204/17X
[code]....
Queries :: Use A Query To Select Specific Records From Table
Dec 14, 2013While I am not new to Access, I am not well versed in its abilities as far as combo boxes go. What I have is a Form where a combo box allows you to pick from a table records 'record ID #' in order to fill in the data of that record to the rest of the form.What I want to do is use a query to select specific records from this table and allow the combo box to show only these 'selected Record IDs' for user selection.
View 1 Replies View RelatedQueries :: Select Active Record In A Table Using A Query
Nov 30, 2013Based upon a specific date (varDate), I want to select the record that is active (who drives the lease car).
row 1 user X from 13/11/2013
row 2 user Y from 15/11/2013
row 3 user Z from 17/11/2013
I want to find the active user on a date. So on date 13/11/2013 (=varDate), user X is active, on 14/11/2013 (=varDate), user X is still active, on 15/11/2013 (=varDate), user Y is active, on 16/11/2013 (=varDate), user Y is still active and on 17/11/2013 and later, user Z is active.
SELECT Query To Not Include Record If Already Exists In Another Table
Aug 25, 2015I have 2 tables:
Query_Rates (Actually this is the result of a query):
Unit
R_Date
Sold_Rate
A
24-AUG-15
145
[code]...
So what I want is a list of all the records from the Query_Rates table where the absolute differences between the sold rates between Query_Rates & [Sent till date] (matching the unit # and the dates) is greater than 1 and the record shouldn't be displayed if it is already present in the [Sent till date] table. But if you notice the first record which has unit A is already mentioned in the "Sent till date" table and shouldn't be repeated again in the query result.
The desired out put should be:
Unit
R_Date
Sold_Rate
X
25-AUG-15
200
Urgent Help Needed With Table Create Query.
Aug 6, 2007Hi all, i need some help constructing a query that will create a table for me that does not include the Type value of 'promotion' if the same ID value exists elsewhere within the query.For instance, in the example below, i would want to delete the first entry containing 'Promotion' because ID2001 exists in two other places. However, if only one instance of ID2001 'Promotion' existed in my query, i would want to keep that entry. I would prefer to write all my data to a new table called "Test", This query has to sort out about 1000 entries when executed, otherwise i'd do it manually.I'm pulling my hair out with this one, so could someone help me?ID Company Name TypeID2001 Disc plc John PromotionID2001 Disc plc Paul AgentID2001 Disc plc George Agent
View 3 Replies View RelatedQueries :: Create Summary Query For Each Table?
Dec 5, 2013I have three tables with data.
Table1 is data for meals.
Table2 is data for room costs.
Table3 is data for payments made.
Each of these tables has a foreign key for EventID.I'm trying to produce a report that will show, for each EventID:
The total billed (which is meals + rooms)
The total paid (from Table3)
The balance due (the difference from the two above).
Do I have to create summary queries for each table?
Queries :: Prepare Csv For Access Table Import Using Select Query
Oct 26, 2014I have generated two queries called "qry_GetWellName_GetAPI" and "qry_GetWellProduction" that extracts all the data I need from a csv to make two tables.
My issue is that I can not gather all the production data into a query formatted properly for a make table query that contains production history by month / api number.
Ideally the data would be placed in a table called tblProductionHistory with the following unique table index --> [api_fk] [data date]. The api_fk would link to the table that was created by extracting well names using qry_GetWellName_GetAPI.
tblProductionHistory field structure I am trying to achieve:
[api_fk]--[data date]--[oil rate]--[gas rate]--[water rate]--[days produced]--[cumulative oil]--[cumulative gas]
Currently the qry_GetWellProduction shows each well name and the year it had production as a separate record from the month names and the month's production values. I need to consolidate the data so each record can be matched to the unique table index.
Is there a sub query I need to use to manufacture a data date for each month that includes the year and last day of that month for that year? Also would I need a separate sub query to match each month's production to the api number?
General :: Select Non-contiguous Records In Access Table Or Query
May 14, 2013Is there a way to select non-contiguous records in an Access table or query?
View 3 Replies View RelatedGeneral :: Records Using A Select Query That Is Sending To A Make Table
Feb 25, 2015I have records using a select query that I am sending to a make table. I would like to have those records excluded from being used again for 180 days, at which point they can be used again. Essentially, I have an ID and an email address which gets stored in the Make Table. I would need to ensure that if we send an email out in Week 1, we do not send an email again for another 180 days if there is activity from that same ID. On day 181, the ID/email address can then be resent.
View 6 Replies View RelatedQueries :: Making A Table From A Select Query In Access 2013
Jan 21, 2014I have made a new access 2013 database. I have created a linked table that has imported a substantial amount of data from an external data source, (an Excel spreadsheet). So far no problem. I created a select query that plucked data from the original table mentioned. Again, no problem. Then I decided to create another table, using certain fields only from the select query. Microsoft's guide tells me to start with CREATE, then Table design. I am happy to use just 4 fields from my query, but what I keep ending up with is a table, that, when I double click on it gives me the following:
ID Field1 Field2 Click to add
(New)
It is presumably expecting me to enter an ID number and it will come up with some record, but I want a complete table that should show several hundred records.