I have at least 200 excel spreadsheets that get updated every day with closing prices of commodities. I want to run a breakout query against every spreadsheet to see if today's close is a new 20 day breakout. What is the best way to organize the access table or tables? Put all the data in one table or have separate tables for each contract and run one query against the group? I need to be able to update the tables everyday so that today's close info is included. I have a working query that works with a single dynamically linked spreadsheet to table, but I don't want to set this up for all 200 spreadsheets. Can this process be duplicated? I have tried to modify the query to run against two separate tables, but was unsuccessful. Since there are so many potential tables, if that is the best way to do it, how can you say select all tables and then run the query?
Here is the query I have written:
SELECT WZ07.Symbol, WZ07.AlphaDeliveryMonth, DateSerial(Val(Left([ddate],4)),Val(Mid([ddate],5,2)),Val(Right([ddate],2))) AS transdate, WZ07.DClose
FROM WZ07
WHERE (((DateSerial(Val(Left([ddate],4)),Val(Mid([ddate],5,2)),Val(Right([ddate],2)))) Between Date()-28 And Date()-1) AND ((WZ07.DClose)=(select max([WZ07].DClose) from [WZ07])));
The WZ07 is the December Wheat contract for 2007. This is what the excel spreadsheets are named. I had to do a dateserial command to turn a number into a date(couldn't make access recognize that 20070831 was August 31,2007) All the column headings on all the spreadsheets are the same: Symbol, AlphaDeliveryMonth, DDate(transdate),Close
I know that this is both about tables and queries so I wasn't sure where to post this. Thanks for your help.
I'm wrestling with a problem with a hardware and software database, on the software side. I'm trying to figure out where to store the license and media cost for a piece of software. Right now I have the following tables:
1. Software name (MS Word, Office, for example) 2. Software version, which also stores whether a license is required 3. Software license, which includes the license code and whether that license expires 4. Software inventory table, which stores the # of licenses we have available for installation. The purchase order (PO) # and date are currently here. 5. Software PO generating table. This is used when we need to reorder more licenses, or a new piece of software.
I need to figure out where to store the cost of the software, the cost of the license, how many people are covered by the license. I've been trying to decide which of the last three tables should store this information.
There are two issues: we need to keep a history of how much older versions of the software and its licenses cost, as well as be able to enter information to order new versions of the software and/or licenses.
My primary key for my database a WorkOrder Number. The issue is that sometimes problems span a selection of WorkOrders. For example, if we get a lot of bad parts, 3 different WorkOrders might be affected, and each may have different resolution to the effect. To speed up data entry, I've had a request to make a button that copies the all the information from a current record into a new one.
If much of this information repeats, it this a poor way to handle this data? Currently it is pretty straight forward with ostly one->many relationships, and this would make things pretty complicated with a bunch of many<->many relationships.
Im trying to show multi level organization with a query and im not sure how to do it. I have a table that is named Originators, that includes all of the persons personal info and this table gererates a OriginatorID. Next I have a table called OrgTree that has the fields as follows:
OriginatorID---Auto lookup to Originators---OriginatorID LastName--Text FirstName--Text StartDate--Date/Time UpLineOrigID--Autolookup to Originators-OriginatorID UpLineLast---Text UpLineFirst--- Text
This works so far, but I would like to see all the people brought in by originator (a) and all the people brougth in by those people and so on. I guess this would be a tree, but im not sure if thats my problem.... Can this be worked out with a query or do I need to do something else. God I hope it can be done with a query.
I have one table named Entry. This table have account number,work organization, price, date and etc.
I use a combo boxes in form which is connected to query and I can easily choose one account number,one work organization and date to see an report. There is also a possibility to left some of the fields blank, it will bring report if is null.
Now I need to make a form that have a possibility to bring up a report for multiple selections of account number and work organization?
I have an Append Query that I developed and am wanting to use the SQL View statement in a piece of VBA code that I am writing. The problem is that it is not working.
Here is the SQL View when I am in Query Design View. Code:INSERT INTO tblMasterAccountList ( AccountNum, AcctName, [Account Type], [Billing Spec] )SELECT TEMPAcct.Acct, TEMPAcct.Name, TEMPAcct.Type, TEMPAcct.BillingSpecFROM TEMPAcctWHERE (((TEMPAcct.Existing)=False));
Here is the code I have in VBA... Code:Dim strSQL As StringstrSQL = "INSERT INTO tblMasterAccountList (AccountNum, AcctName, [Account Type], [Billing Spec]) "strSQL = strSQL & "SELECT TEMPAcct.Acct, TEMPAcct.Name, TEMPAcct.Type, TEMPAcct.BillingSpec "strSQL = strSQL & "FROM TEMPAcct "strSQL = strSQL & "WHERE (((TEMPAcct.Existing)=False));"DoCmd.RunSQL strSQL
The code runs fine in that no errors are generated, but it does not append the records. When I run the query outside of VBA, it does append the records. Is there something special that needs to be done with an action query for the SQL statement to work in VBA?
i am relatively new to databases so this is a fairly basic question.....
I have a form. Within this form all records are locked as i do not wish users to change details. However i am told there is an option allowing the editing of existing records upon request. is this true?, as it would improve my form greatly
Hi all. I am looking for a formula to calculate a commission, based on the total sales price. The commission is a sliding scale. Ex. if sale price is 200 or less, commission = 30%; if sale price is 201-500, commission = 25% on 201-500 & 30% of 1st 200; if sale price is 501-1000, commission = 20% on 501-1000, 25% of 201-500 & 30% of 1st 200; if sale price is 1001+, commission = 15% on 1001+, 20% on 501-1000, 25% on 201-500, & 30% on 1st 200. Does that make sense? In other words, if sale price is 300, I want commission to be 30% of 200 + 25% of 100, which = 85. Is there a way to write this in one formula?
This report is based on a query. The query is called "Diplomas_requested_per_month". The fields in the query are: "Transcript_type" and "Request_date". In this query there is Between [Start Date] And [End Date].
I am attempted to count the number of "Official Copy" for a specified month.
I am propted to input the start date and ending date, it works great when I run the query. But in the report, I get a "#error".
I am thankful for who have responded to the previous posting of this issue, but I am completely lost.
Using Access 2k and Word 2k on a WinXp Workstation.
The code that I'm using is MS standard and has never previously given me any problems (see below). I've searched the MS knowledge base and Googled the error but nothing I find seems to relevant to this situation.
Can someone help?
TIA,
Mo
-----code----
DoCmd.Hourglass True If CreateWordObj() Then With gobjWord .Visible = True .Documents.Open "c:hp_lettersdrugsranout.doc" DoEvents With gobjWord.ActiveDocument.MailMerge .Destination = wdSendToNewDocument .SuppressBlankLines = True .Execute End With '.ActiveDocument.PrintPreview .Visible = True End With End If
hi i just signed up to this site and need some assisstance from someone
i need to create a form in access 97 that enables me to search for a keyword on a given search area, for example the cd/dvd rom drive for files and folders
I need to get x and y coordinates for each device, but the data has to get looked up from 2 other tables.
I have a table (called InstReclosers) that has device names. Each device is on a Section. I can go to another table (called InstSections) and look up what Node that particular section is tied to. Then I need to go to another table (called Nodes) to get the X and Y location for that particular node.
How I can go about getting this X and Y data into the InstReclosers table?
...in summary, InstReclosers has device name and section name. InstSections has section name and node name. Nodes has node name and XY coords. Need XY coords for each device in InstReclosers.
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).
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.
I have an MS Access accdb with linked SQL Server 2012 ODBC tables. I am working on a procedure to copy data from local tables to these linked tables (identical schema). I did a simple
Code:
DoCmd.RunSQL "INSERT INTO linkedTable SELECT * FROM localTable"
This works, but is very slow. Way too slow. (INSERT copies the data one record at a time).
I would like to copy the data in a bulk operation, or operations that I can execute programmatically.
I have a table for a multiple parents linked to a child table. I need to figure out a way to only allow 1 parent to be coded as primary, 1 as secondary, and then the rest as other... I thought about making Primary/Secondary/Other a primary key. But then I can only have 1 other. I would have to make a finite number of parents that could be entered and I want an infinite number.... My end goal is to have a report that only has a primary and second parent on it, but the rest of the parents still exist in the table...
Hi, I am extracting data from linked db2 table using access make table query. First I create a select query and can view the linked db2 data, but when I change to a make table query I get an error message, "invalid argument", when I run the make table query. There is no selection critera specified. Has anyone had this happen? and Do you know a solution?
When the value "X - DISCONNECTED" is selected in the form I want the record to be removed from its existing table and sent to a new table which keeps all the "X - DISCONNECTED" records together.
Any ideas would be greatly appreciated. How would this be coded?
I'm building a make-table query for which if the result is null (no record correspond to the set of criterias), a default message like "there was no activity during the period" would appear in the table (not a message box...I need the message in the output table). The best I could think of is an IIF function but it doesn't seem to work... Is there any way to do this without using VBA?
