Queries :: Storing SQL Queries To A Table

Apr 4, 2013

I can't seem to find it and this has to be the last bit of stuff I need to get my Database up and running for my boss using an EXE front end.

What I am trying to do is something similar to what I did for my reports

I have a table with the following fields

ID = Autonumber
Discrip = Text (Discription of the Report)

The combo box on the On Change event has this code

Private Sub ReportCombo_AfterUpdate()
DoCmd.OpenReport ReportCombo, acViewPreview
End Sub

Can I set something like this for SQL commands?

View Replies


ADVERTISEMENT

Sum Queries And Storing Calculated Fields

Aug 26, 2005

Hello All,

Probably trying to do the impossible.

Normally I would never (that’s a long time) have the results of a calculated field in a table, but I’m stumped.

Table1
TestID: Autonumber
Quantity: Number
Selected: Yes/No

Table2
SumID: Autonumber
TestID: Number, Foreign Key
Quantity Delivered: Number

Query1-Sum Query
Table2
TestID: Group
Quantity Delivered: Sum

Query2- Select Query
Record Sets

Query1 Table1
TestID-----------------------------------TestID
Quantity Deliverd Quantity
Select

Fields in Query2
TestID from Table1
Select from Table 1

Run Query
Can’t update “Select” field


So I’m thinking that I should create a 3rd query to update the summed Quantity Delivered into a new field in table 1. Which would in effect create a stored calculated field.

View 2 Replies View Related

Queries :: Update A Table / Run Queries Without MS Access?

Nov 18, 2014

I'm using Microsoft Access 2010. I want to create a database that people without Microsoft Access can use. If I create a database is it possible to use VBA in Excel to update a table in Access and then run/export a query? I know you can use Excel to communicate with MS Access but can you do it when you don't have MS Access installed on your computer?

I have daily sales data that I want someone without MS Access to be able to load into the database and then export a query from.

View 2 Replies View Related

Table For Storing Updates

Nov 21, 2004

How do I create a table relationship where a secondary reltated table stores past dates, status code and customer ID. For example: I want to keep track of the past dates that an order was revised and why it was revised. So, if I have a customer who was due for installation on 10/21/04 and status is PEND for pending. THen on 10/18/94 I change the date of PEND to 10/25/04. I want to keep track of the times it changed and the reason why. HOw can I make the program automatically create a record in the revision table storing the past code, reason and date using customer ID as key and teh revision table would allow multiple records for each customer ID. DId I make sense?

View 2 Replies View Related

Storing Time In A Table

Feb 11, 2008

Hi,

Firstly this is my first post so please forgive me if i do not conform to the usual way of asking a question!

I have an access database set up and it is working well, so far!

What I wish to do is the following, I am creating this for a cycling club to store race results...

I wish to store the time that they completed the stage in, how do i do this using the date/time field option when all it wants to do is store a complete time.

for example i wish to store 1 hour 12 minutes 15 seconds in the table as 1:12:15 for stage 1.

Plus they may have received a penalty, so in another field I would like to say store a 10 second penalty...

anyway you get the idea.... please could somebody inform me on the best way to do this, it has to be stored as a time so I can add all the stages up later and create a report!

Thanks in advance...

View 3 Replies View Related

Storing A Number In Another Table

Mar 13, 2006

Hi

I have a database which picks up information from my accounts package. It has the following fields

ID,Name,Date,Ref1&Ref2

I need to extract the last ID number and store it in a table or somewhere and then extract the above information after that number into a csv file in the order above. It is used for my invoices and therefore when I do an import from my accounts package it updates all the transactions.

An example would be I say put 5 invoices on with a ID nr's of 1, 2, 3, 4 & 5 and output to a csv file. When I run the query/macro? it looks at the table with the last ID nr and starts from 6 and so on

I just need the ones I have done today. It has to be the ID number as each invoice is put on it gives it a sequential number, the dates could be different for each invoice.

I hope I've explained myself:)

Thanks in advance

Cheesey

View 1 Replies View Related

Storing Calculated Fields In A Table

Apr 20, 2007

I know that it is bad form to store a calculated field in a table - normalization and all that - but I can see no other way around a need that I have.

I'm trying to make a database to store injury time for employees. If an employee is injured, he can do light work for a certain amount of time, but this light work need not be contiguous. But, the total time on light work must be known so as to see when it is used up.

For example:

Bob is injured on 4/1/06 and goes on light work on 4/2/06 until 4/5/06 (4 days). Bob is then taken off light work until 4/10/06 when he reinjures the same injury. This does not count as a new injury, because it is an aggravation of the old one. So, Bob goes back on light work on 4/10/06 until 4/15/06 (6 days).

So, I need to store Bob's total time on light work for this injury (10 days) so that I can keep a running total to check against the maximum for a single injury. All I can think of is to store the sum of days on light work as a field in Bob's injury record, but that means storing a calculated field in a table.

Am I missing an easy way to do this, or is there a method to do what I want that I am just not aware of?

Thanx for any help!

View 2 Replies View Related

Storing Data From Textbox To Table

Jan 9, 2007

i have a form with two combo boxes cboMth and cboYr

i also have three text boxes in the same form with computed results: txtA,txtB,txtC.

now how can i store txtA,txtB,txtC with reference to the (Mth and Yr) into a table?

e.g: the table will looks this way (below)

===== ==== ==== ====
Mth/Yr txtA txtB txtC
===== ==== ==== ====
01/2007 580 683 93
:
:
:
06/2012 312 510 80
:
:

how shall i write the codes?

View 1 Replies View Related

Storing A Calculated Field On A Table

Aug 10, 2007

Hi,

I know it breaks all the rules to store a calculated field on a table, but I've painted myself into a corner with the way I built this particular database, not good, I have repeating groups and basically the whole database is built this way. I need one form to store a calculated value on a table to solve a problem on a report that encapsulates the total of all the repeating groups. Before you start the lecture, I know I messed up, but this is way to far down the road to start over. Any help would be greatly appreciated.

Gary

View 13 Replies View Related

Returning A Row From SQL And Storing In Local Table

Oct 16, 2007

Hi all!

I have a bit of an issue. Im trying to retrieve a row from SQL and insert it into my local DB table.

However, when I try and execute the code, I get an error message. The data had not been inserted into my local DB table.

Here is the code:

strsql = "INSERT INTO EntryDetail_Local SELECT EntryDetails.* FROM EntryDetails where entrykeyid = " & plngentrykeyid & ";"
Set qdftemp = mydb.QueryDefs("qry_Append_EntryDetail_Local")
qdftemp.SQL = strsql
qdftemp.Close
qdftemp.Execute
DoEvents

Any ideas?

Cheers

View 2 Replies View Related

Limiting And Storing Data From Another Table?

Dec 4, 2013

I have two tables one that lists accounting codes by county and one that a user will have an entry by county which needs to store the applicable acct code from the other table. I want the user to be limited to the use the accounting codes assigned to their county only when they enter data on the second table.

Table examples:

County
Code
Funds Spent
Accounting Code
Date Entered
Aitkin
123
$20.00

Give the user the choice to select only the Aitkin Acct Codes from the Accounting Code table.

12/4/2013
Aitkin
112
$23.00

Give the user the choice to select only the Aitkin Acct Codes from the Accounting Code table.

12/4/2013
Becker
145
$12.00

Give the user the choice to select only the Aitkin Acct Codes from the Accounting Code table.

12/4/2013
County
Accounting Code
Aitkin
4872727001000000
Aitkin
4842727001000000
Becker
4872727005000000

View 14 Replies View Related

Storing Info From A Formula In A Form To A Table

Nov 17, 2006

Hi,

I am an Access newbie and I have a form running about 5 seperate formulas and I would like to store the info in the table that it is associated with so that I can use it later in a query to create another formula. Any help would be greatly appreciated.

View 6 Replies View Related

Storing The Date The Record Was Entered Into A Table

Jun 27, 2006

Hello everyone. I am wanting to store the date the record was entered into the table. But I don't want the users to have to type this in; I thought that Access could grab the date from the system date.

I am new to this and I looked at the Date function but I am thinking it will just always update to the current system date.

Thank you very much if anyone could point me in the right direction.

View 3 Replies View Related

Tables :: Storing Calculated Values In Table

Jan 8, 2014

I know that as a general rule one should not store calculated values in a table, but if I need to include such a value on a number of forms, and calculating that value involves manipulating an ADO Recordset, does there come a point where the performance hit outweighs the design "quality"?

View 1 Replies View Related

General :: Storing Calculated Fields In A Table

Jul 26, 2012

I know that it isn't usually best practise to store calculated fields in a table, but in my situation it is necessary, and I haven't really found a good answer for this after searching these and other forums.

Currently my database has one "Name" field (I didn't make it like this) which might look like this "Jonathan (John) Smith"
Now I also have reason to grab the name like this "Smith, John" for other purposes.

I've set up a form for inputting new people with separate fields for firstname, preferredname, and lastname. I have the calculated fields:

=[fname] & " (" & [pname] & ") " & [lname]
=[lname] & ", " & [pname]

(Later I will add in all the iif's for if there is no preferred name)

Upon finishing the form, the user hits Submit, and I would like to store the concatenated values into the table in their appropriate fields.

I can do this with a SQL INSERT INTO statement but I'd have to add all 25 or so fields from the form, and injecting user input directly into a SQL statement is not best practice either.

View 2 Replies View Related

Storing And Accessing Word Documents In A Table?

Jul 4, 2015

I have a set of maybe 5 template word docs which I populate with info form our database using bookmarks , the word docs are currently sat in a shared network drive.

I have started wonder if it would be better to store them in a table and call them from there to keep things together.

View 1 Replies View Related

Storing A Calculation As A String In A Table And Executing It In A Query.

Jun 11, 2007

N.B. This is not your usual 'Bad practice to Store calculated values in a table' post

I have a table of items.
Each item has a different formula attached which I need to store as a literal value in the table and execute it as a normal expression in a query or vb.

e.g

Columns:Item, QTY, Hrs, mins, ItemFormula
Data: Car, 2, 7, 3, [QTY]+ [Hrs]/[mins]

So ' [QTY]+ [Hrs]/[mins]' would literally be stored for that rercod and is how it would be displayed in a table, form, report etc as it is important for the user to see how the calculation is performed as well as seeing the end result which will be calculated as normal in vb or a query.
The formula will vary from record to record.

How do I go about this?

TIA

View 6 Replies View Related

Adding Feild Values And Storing In Another Field In Same Table

Mar 19, 2005

I have been struggling with this and would appreciate any help. It sounds so simple!!! I have 4 fields (Unit Price 1, Unit Price 2, Unit Price 3, Unit Price 4) in a Orders table that I want to add together and store in a 5th field (Sub Total) in the same table using a form. I am having problems with the sum and I am unsure how to get it to show in the Sub Total box in the form as well as the table. Thanks for your help!!

View 2 Replies View Related

Modules & VBA :: Storing Outlook Attachments And Patch In Table

Jun 13, 2015

Currently, I'm taking my unread emails, storing them in a table then sending any Excel files to a specified folder I've modified some code I found while surfing and it works great but I'd really like to send the attachment name and file path with the email recordset. It's been a long day so maybe it's super simple. Below is my code. Office 2010 Windows 8.1

PHP Code:

Function ReadInbox()
Dim TempRst As DAO.Recordset
Dim OlApp As Outlook.Application
Dim Inbox As Outlook.MAPIFolder
Dim Atmt As Outlook.Attachment
Dim InboxItems As Outlook.Items

[Code] ....

View 1 Replies View Related

General :: Storing 2 Identical Fields On Same Table In Database?

Nov 5, 2013

I want to be able to store 2 identical fields on the same table in the database. The copy will start off being the same as that input in the original field. 90% of the time the 2 fields will always be identical but the copy may be changed at a later stage. I looked into using the copy as a calculated field but found out these can not be updated. Is there another control I can use or do I have to code the population of the 2nd field specifically (in AFTER UPDATE EVENT?) myself. The added complication is that the original and copy must be selected from a combo box as all values are held in a separate table.

Also trying to make the second field = to the first field in the design stage of the table using default value

SO if I put default value = [table].[fieldname] I get an error stating could not find field 'table].[fieldname', and yes the opening and closing square brackets are missing from the error.

View 5 Replies View Related

Forms :: Storing Small Icons In A Table For Use In Continuous Form

Nov 29, 2013

I searched without success for a solution to show small pictures (icons) in a table field to show up in a continous form.

Purpose is:
a communication log table has comm codes such as
- mail out
- mail in
- tel call in
- tel call out

the comm log table has the fields
CustomerID, datetime, CommCode

The CommCode table hast the fields
CommID, CommCode, Icon

The form (subfrm) should show in continous form mode to each customer..Datetime, (commCode), and to visualize the Commcode the small image (icon) presented by an envelope with an arrow right, an envelope with an arrow left, etc.

I cannot find a solution for storing those little images and retrieving them from an OLE-Field.All my other pictures I do not have stored in the tables, but only the image path, but for that I would prefer to store them directly into a table field as they do not blow up the database.

View 1 Replies View Related

General :: Data Entry Form - DLookup And Storing To Table

May 8, 2015

I have a simple data entry form based on a table. However I have a few fields that I do a lookup in a field on the form from a query, and yes I know I should not have a lookup in the control source however, this is the way that I will be doing it on this occasion.

=DLookUp("[Salary]","[Salary Query]")

How I get the value from this unbound field to enter into the actual field in the table. Do I bring the actual field into the form and hide, and do some sort of after update, as I have tried and it does not work.

I have called the unbound field with lookup "Salary Level Base" and the actual field in the table is "Salary Base".

View 5 Replies View Related

Queries :: Update In Table From Same Table Based On Criteria Column In Same Table

Sep 29, 2013

How can I update (some columns) in a table from the same table based on a Criteria column in the same table.

View 2 Replies View Related

Queries :: DSum - Update A Field In Specific Table With Info Form Another Table

Aug 5, 2014

I have a table products with a field "id_product" and "total" (Total items in stock)

I have a query with the fields "id_product" and also the field "total in stock"

I want an update query to update the field 'total' in table 'products' with infos from that query

For each id-product in table products, replace the field total with the field 'total in stock' from the query

So I want to update a filed in a specific table with infos form another table.

View 2 Replies View Related

Queries :: Updating Child Table List View On Change Of Main Table Row

Apr 19, 2013

Here's a query that the bottom listview in the attached form i.e. a listview representing a table of calls(many) to fims (1 top listview)

Code:
SELECT calls.id, calls.firm_id, calls.called, calls.said, calls.spoke_to, calls.next
FROM calls
WHERE (((calls.firm_id)=[firms].[id]))
ORDER BY calls.called DESC , calls.next DESC;

When I run the thing...I get a dialog asking me for firm id.

I want to change this so when I move up and down the firms LV (top)... the bottom LV updates taking firm id from the top LV with focus.

Access 2003.

View 2 Replies View Related

Queries :: Access 2007 - Insert Into Table Where Source Is Another Table And Form

Feb 3, 2015

If you want to use a "DoCmd.RunSQL "INSERT INTO" command to insert data in a table and the data to insert comes from a table and a form, could this be done in one pass?

So...writing a record wit 4 values from table1 together with a additional value from a textbox in table2 as 5 values.

View 5 Replies View Related







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