Queries :: Generate A Query From A Table That Has No Data
May 19, 2015
I currently have tables that are in the database but they require the user to import the data from an excel file. This works fine however due to the data being imported I only require certain columns from the import, this is where I would use a query.
The data of the import will always have the same column headings. Firstname, lastname, usernumber etc..Unfortunately i'm not quite sure how I could get a script to generate a query of the selected columns after an import is completed.
I create a query from different tables. With an website based insert statement I put the data from this query to another table.
Is it possible to fill the table without the insert statement. But automatic done by the database itself. So insert/update all the query data automatic into the table
I have a reasonably complex query (3 subqueries into 1 main query) which gathers data from various tables into a single dataset based on a specified date.
I now need to generate a similar dataset but across a range of dates (a month) for reporting purposes. However, I can't just adapt the query and change the parameter from a "=#<Date>#" format to a "Between #<Date1># And #<Date2>#" format
The reason being, each date has to be treated individually and has to be queried as a standalone. It's to do with the type of data I have (one-to-many relationships between tables)
So what I really need to do is run the same query multiple times, for each date in scope, then stitch all of those datasets together into one 'giant' one.
How to do that in SQL (effectively, have one query produce the dates in scope, then join that onto the other query, passing each date as the parameter - I don't even think that's possible to be honest)
The other option I can think of is to use VBA to loop through the dates in scope, then use a QueryDef object to set the parameter and read the records for each date into a Recordset object. But then I have the problem of stitching all the Recordsets together, without looping through all the fields and rows each time.
I am create a database to track leave of my team.I have create the tracker and few reports, however now I want to create a report by applying filters in 2 places, for associate and for leave type or for associate and for joint leave or for associate and for Informed.I have SQL code which does not work. how can I select the other paramater with associate name using combox box in form.
Code:
SELECT Leave_Records.[Absent Date], Leave_Records.[Associate Name], Leave_Records.[Absent Day], Leave_Records.[Leave Availed], Leave_Records.[Joint Leave], Leave_Records.[Informed TL], Leave_Records.[Leave Type], Leave_Records.[Leave Applied], Leave_Records.Comments FROM Leave_Records WHERE (((Leave_Records.[Associate Name])=Forms!Report_Form!AssociateName) And ((Leave_Records.[Leave Type])=Forms!Report_Form!Leave_Type)) Or (((Leave_Records.[Associate Name])=Forms!Report_Form!AssociateName) And ((Leave_Records.[Joint Leave])=Forms!Report_Form!Joint_Leave)) Or (((Leave_Records.[Associate Name])=Forms!Report_Form!AssociateName) And ((Leave_Records.[Informed TL])=Forms!Report_Form!Informed));
I then created a 2nd select query with inner joins to join both crosstab queries on similar fields (activity & projectname).
SELECT QUERY 2: projectname, activityname, employeename (totalhoursworked as value), employeename (calculatedcost as value)
It gives me this:
However, I want it like this:
Those employeename... refers to more employees being added after a period of time. Hence I want to know if I could use vba to generate a report every time a button is pressed on a form? I know how to link the form to the query.
In an event, groups of 2-5 persons may be tagged to this event by a randomly generated number (using autonumber).
Let's say Tom (social security number: 12345X) is tagged to events 2, 5 & 6. There are of course other persons together with Tom in the above 3 events.
If I would like to find out who are the persons who are in events which Tom had participated in, how do I find them using a query?
Currently, I'm thinking of using a searchform where it would return his "associates" if I just query using his social security number, i.e. 12345X.
I would like to filter data from a table using a query (from an data input form). The objective is to output all results if input form field is empty and to output results higher or equal to the type in the field if field is not Null. The query code is as follows:
I am wondering if there is a quicker way to export a query to excel then have the data in that query removed from the original table. (effectively cutting the data from the table and exporting to excel)
I understand that this can be done by exporting the query to excel then running the same query as a delete query to remove the data but I just wondered if this is the most efficient way.
I have experience of VB in excel but currently only use the basic macro builder in Access though if Access VB is more efficient I can easily learn.
I have a query (MonthlyInputQry) which calculates the total input into a production process by month (where month is a number 1-12) and there is a year (2013, 2014, etc). This query is based off a table (InputTbl) which records each individual order which goes through the process and then adds up the combined weight for all orders in the month.I also have a table (MonthlyCostsTbl) where the costs per month of the process are displayed, and on this table there is a space for to input the monthly input as calcuated by the previous query (with the aim to find the cost p/kg each month)
At the moment, at the end of each month I have been looking at the value contained within the query for the applicable month (so March 2014 would be Month = 3; Year = 2014) and copying and pasting this value into the table.
Is there a way in which I could get this value in the table to automatically update, either in the form of a running total or at the end of the month? Either from the query itself or from the original table of inputs.
I am trying to run a query and for some reason its not showing the data from the table (its blank), this is a monthly reporting I do - last months query works perfectly.
Table: log and list
Here is the SQL
SELECT log.*, UL.langue, UL.version FROM log, (SELECT list.id, list.[langue], list.version, list.no_joueur FROM list GROUP BY list.id, list.[langue], list.version, list.no_joueur) AS UL WHERE (((log.id)=[UL].[id]) AND ((UL.no_joueur)<90000000));
I am working with a database and existing query from my predecessor. The field in the query appears in both the database table and the query.when I run the query it doesn't appear. Is there a limit to the number of columns in a query I easily added a new column and moved a column,
How to retrieve data from a table (via query) ? I created the below query, but I'm not sure what else is needed to retrieve the value from my SQL query. My query code is below. I'm not getting any errors.
Dim strClient As String strClient = "Jerry Davis" strSQL = " SELECT [Progress Tracking].[Client Name], [Progress Tracking].[Client Start Date], [Progress Tracking].[Start Body Weight], [Progress Tracking].[Tracking Date] FROM [Progress Tracking] WHERE [Progress Tracking].[Client Name])= ' " & strClient & " ' " MsgBox "Weight Box " & " " & strSQL
I want to set a table field's default value to whatever is displayed in a certain field on a certain form at the time.In other words, say I have a database with a table called TABLE1, and two fields called NAME and SCHEDULENUMBER. I have a form called CreateSchedule with a SCHEDULE NUMBERCONTROL form and a NAME form, and I can enter names onto it, and it records to the proper SCHEDULENUMBER. So if I pull up SCHEDULENUMBER 4, and add three names, when I go back into TABLE1, I can see those three new names, and each one has the SCHEDULENUMBER set to 4.
What I'm trying to do is write an APPEND QUERY to copy a list of names from a different table, and paste them into TABLE1. The problem is that the other table doesn't have a SCHEDULENUMBER field. What I want to do is put a button on the CreateSchedule form that runs an APPEND QUERY, and sets the SCHEDULENUMBER to whatever value is displayed on CreateSchedule's SCHEDULENUMBERCONTROL field.
I tried setting a default value in TABLE1's field properties for that SCHEDULENUMBERCONTROL field, but I keep getting error messages. I just want TABLE1, whenever I add a new record (regardless of how I add the record: manually typing it or clicking the append query button) to look at the form CreateSchedule, and set it's own SCHEDULENUMBER field to whatever is displayed in CreateSchedule's SCHEDULENUMBERCONTROL form.
I have a table that is updated from an update query which gets its data from a table, which gets its data from a form. I have set the query to only update the current record, this is done by a macro when the user exits the input box. The problem arises when a user goes to visit past record, I would like it to not overwrite the old data with the new data. If the query was only allowed to ADD data and not overwrite then this would fix the problem!
Running Access 2010 and developed this query to filter out data from a table between times on 2 dates (day before report run and day of report). Covers data from a shift that carries over to the next day.Trouble is, the PC i developed on still operates the query as expected. However, on the PC the database resides (not networked just stored) and operates, the query brings up no data at all unless I remove the Time filtering.
This PC used to operate correctly up until early this year (about 18 months operated correctly) when the PC was replaced due to failure. Access version is the same and I am at wits end to what the cause is. Here is what my query looks like:
Quote:
SELECT Breakdowns.BreakdownDate, Breakdowns.Time, Breakdowns.Shift, Breakdowns.Downtime, Breakdowns.Equipment, Breakdowns.Conveyor, Breakdowns.Fault, Breakdowns.Stopper, Breakdowns.Gate, Breakdowns.Dolly, Breakdowns.Carrier, Breakdowns.FaultType, Breakdowns.Comments, Breakdowns.Tradesman FROM Breakdowns WHERE (((Breakdowns.BreakdownDate)=Date()) AND ((Breakdowns.Time) Between #00:00:00# And #6:29:00#) AND ((Breakdowns.Shift)="Night")) OR (((Breakdowns.BreakdownDate)=Date()-1) AND ((Breakdowns.Time) Between #22:30:00# And #23:59:00#) AND ((Breakdowns.Shift)="Night"));
I have a database that needs data to be reentered every school term, at the moment i am having to delete selected data fields manually. im looking to create a query so that the data is deleted by running it. The data would be returned to a blank field. I have tried using a delete query but it is asking for the selected table, even though a selected table exists. Using the update query i am faced with updating the query to a typed word however i just want it blank.
I am building a simplified re-order point system - if inventory position drops below a certain level (the yellow level is this case) one or more purchase order lines has to be created in another table.
I have one table with the following field and data:
ItemId Red Yellow Green Multiple Inventory position 0001 10 30 50 5 45 0002 5 40 47 5 23 0003 11 20 30 10 5
I would like to generate new records (in another table) based on the above fields and three records.Basically the end result should look as the following:
The logic is quite simple - if inventory position is less than the yellow value new order lines should be created in multiple qty (based on the multiple field) until the aggregated value (in table 2) is above the green value.The priotization value should be based on the start inv (in tbl 2) compared to the values in red, yellow and green in tbl 1.
- Staff Position(Unique Position Name, Description, Hierarchy) - Training (ID, Name, Description etc.)
I essentially want a table with Staff Position as the Field, and Training as the Rows. The intersecting entries/matrix will be Yes/No to say whether that staff positions requires that training.
Simple Example
..................... Worker ........ Senior ........ Principal IT Training ......YES ............. YES .............. YES Accounts ......... NO ............. YES .............. YES Management ... NO ............. NO ............... YES
I need the user to be able to add as many training entries and as many staff positions as they want. It doesn't HAVE to be that sort of format...
I have to compile a report from more than two queries. All queries have a field common between them.
E.g. query1 gives name, address and contact number of all the 30 students in a class query2 gives semester wise marks obtained by each student query3 gives performance in sports of each student.
All the three queries have name of the student common in them.
Now I wish to generate a report card which should be as under:
1. The record of query1, query2 and query3 corresponding to a particular student should come together. 2. The record of query1, query2 and query3 corresponding to the next students should come after that and so on.....
I tried using a sub-report but it ends up displaying all the records of query1 first and then all the records of query2 and so on...
I'm working on a database where I enter a series of assets and their costs, and I'm trying to build a query that will generate an amortization schedule for each asset.I'm using an amort methodology that amortizes a certain amount each period based on a calculation. I've got a query that generates the amount that would be amortized based on that calculation.
My problem is in the last period of amortization before the asset is fully amortized. In that last period, the amort methodology may generate an amount to be amortized that would be greater than the total value of the asset. In such cases, I want the amort for that period to be the total value of the asset less the cumulative amort up to this period.
So, for example, I may have an asset that is worth $225. My amort calculation generates an amort amount of $50 per period. For the first four periods, the amort would be $50. But in the final period, only $25 would remain unamortized, and in that instance I want the final amort amount to be $25 instead of $50.
[AssetName], [MonthStartDate] (i.e. the first day of the month for the period of amortization), [BusUnit], [RawMonthAmort]. [RawMonthAmort] has an expression that performs the calculation that determines how much to amortize. I've tested this part and it all works (except for during the final month of amortization, as noted above).
I tried including another layer of conditionals in the [RawMonthAmort] expression that would calculate a cumulative total amort and check that against the total asset value (by bus. unit) and give the difference if the cumulative total amort exceeded the asset value, but I couldn't figure out how to make that work.
I'm a novice in fact this is my first serious try at creating a database. I'm setting up a database to track contracts both potential and active for marketing. This will be converted to a SQL when I have it finished. I have a customer table setup in the fashion of a contact list. Most of my forms need to access the information in it via a list box. I want to set these list boxes to generate a new record in my customer table if the name that is entered is not currently in that list. By using the event option "On not in list" I'm able to get the form for the customer table to open but when the new data is saved and the Customer form is closed the information is not available to the list box. How do I get it to update the list box without having the user start from scratch on that particular form?
I have a database that will take lots of data entered by an employee and calculate some additional data and generate quotations. Within each quote there is a possibility for 15 different metals (5 Precious Metals, and 10 Base Metals). There is also 5 fields that need to be filled out about each Metal (What the metal is, the market being used, weight, whether it is included in a different price, and the price). I currently have 75 fields to address each Metal and their 5 fields respectively. Is there a better way to Normalize this data, and accomplish what I need accomplished? I want at the form level the employee to tell the database whether they want to add a Precious Metal, or Base Metal, or Move on to other data entry.
Here is a Screenshot of the design view of one of my tables with too many fields : table screenshot1.PNG
Table called "Products" Field 1= "Product ID" which is a text field (PK) but numbers are used (ie 1 -20) Field 2= Products -showing our list of 20 products
When I enter a new product, currently I have to look in the table to find the last ID used then use the next one available. I have created a form to be used for data entry to enter new products
What I am trying to do is :- 1, have the form open at data entry level but still able to scroll and see all records and 2, Have the form auto generate the next number available. For example, I have 20 products entered so when the form opens to enter a new product, the ID is automatically at number 21.
I wondered if its because the field is a text field or I am trying to insert the code in the wrong place.
