Ok, this is what I have, I have a database that has 3 tables, a year table, that all my forms get inputted into, a table that has manufacturer addresses on it and a table that has sampling locations.
On my form, I have it where there is a drop down box, so you can just select the name of the manufacturer and select the name of the sampling location. But what I want it to do is, that you select the name from the drop down box and then in another box or two it auto populates the corresponding street address, and city for both the manufacturer and the sampling location.
If you have a one - many relationship between data, normally in the table it is captured, you would find multiple rows for every 'thing', each row detailing different 'functions' performed on it.
Is there a way that one can display these functions horizontally, instead of vertically, i.e, have one row per 'thing', instead of multiple rows.
I am trying to create a query which will return records which are from a specific month of the previous year...
To better explain:
If I have sales from 2013 and 2014 in Jan, I want a query which will show me the results from Jan in 2013 only... I'm just unsure on how to make a query do this and it's probably really simple.
But then in 2015 I will want it to show results from 2014 without having to change the expression or criteria.
I have a text file linked to this database, and I'm trying to create a Query that will pull all of the data out of this text file, and add a rank if two of the values match in multipe records.So in other words, when multiple records have the same [JOBNBR] , I want to evaluate the [TIMESTAMP] value to see if it is the smallest one, and so on.I don't want to create multiple queries to do this, is there any way to do this? Here's what I have:
Code: DDR: (Select count(*) from tblMyDataImport Where [tblMyDataImport].[TIMESTAMP] < [TIMESTAMP] AND [tblMyDataImport].[JOBNBR]=[JOBNBR] )
This may not even be possible, but I am looking to create a Query that can locate records in an Access Table based on 2 columns of data that I have stored in a CSV file.
My table contains several fields, 2 of which are "Dept" and "SKU" and has over a million records.
My CSV file contains 3 fields: "Dept", "SKU" and "Total" - total being the number of times that particular Dept/SKU combination is used.
I need to be able to parse the dept/sku values from each row in the CSV to the query and locate only the records that contain the same values in the Access table.
The plan being to delete out those that are identified by the query.
I have only one bound text field in my report. By default, it shows the results (150 records ) vertically. I wish to show them horizontally, 5 in a row. How to do this.
Is it possible to run a basic select query to pull records based on multiple strings entered by a user?
I have a query with field criteria - Like '*' & [Type In MMDBID:-] & '*'
This allows the user to input one MMDBID and the records are retrieved from the db.
I can also use the OR statement in the same field criteria "AB123" OR "BC123", and all records based on those values are pulled back.
But I cannot get the user to input multiple values and I tried amending the SQL string based on the OR criteria above
SQL statement (Like) is below :
SELECT tblFund.MMDBID, tblFund.[Investment Name], tblCodesLive.[IOE Code], tblCodesLive.[Uptix Code], tblFund.[Red Payment Deadline] FROM (tblFund INNER JOIN tblCodesLive ON tblFund.MMDBID = tblCodesLive.MMDBID) INNER JOIN tblContact ON (tblFund.MMDBID = tblContact.MMDBID) AND (tblCodesLive.MMDBID = tblContact.MMDBID) WHERE (((tblFund.MMDBID) Like '*' & [Type In MMDBID:-] & '*') AND ((tblFund.Editing)=False) AND ((tblFund.Closed_Fund)=False));
I have an access table which has a key using letters (A,B,C,D...etc). Against each letter there is multiple rows against each letter. However for testing purposes I only need to get a single row against each key and I was wondering if there is a function I could use which would return only the first record of data for each key?
Does anyone know how to create a query object using a string that contains SQL code?
The reason I ask is that I want to be able to run an ad hoc query, but I can't use a RecordSet because there's no way (that I know of) to take the results of a recordset and create a query display out of it (meaning, make it look like a query was executed)
So what I really want to do is to create a query, and then execute it with "DoCmd.OpenQuery()" If that's possible. Any ideas?
How to find the max value of a field in a query... How does a query display the max record based on the primary key... want to isolate the last record created, the one with the max primary key.
I have a make table query that gets its info from another table that is updated weekly. I would like that when we run the Query Type (Make Table), we would like to add a primary key to a field that exists already starting at 1 every time the query is run.
I have a database where I need to import data from an excel file.
What my employer needs me to do is upon appending the data to a table which I named properties, under the field which I called owner, all the names should follow the correct format. Most of the inputted data does not follow the correct format.
The correct format of the names should be: K. CURTIZ -> Where K is the initial of the First Name, it should have the '.' and a space before the Family Name
Here's an example of a data that is inputted incorrectly: R OTTESON -> it should be R. OTTESON
another example where there are 2 names of 2 different persons, it should be inputted this way: A. & J. PANAG
Mostly inputted incorrectly this way: J&T BOTIFAN -> it should be J. & T. BOTIFAN
I have a rather interesting situation involving creating a primary key. Basically I have two tables.
Table 1
Date, Customer, Part Number, Work Order, Qty tested, qty failed, tech name
Table 2 Defect, reference, quantity(defect), defect cause, part number, work order
The ideal relationship I want to create is that for every Table 1 there are multiple table 2. Regular one to many relationship. The problem is that in the first table it is possible and probable that there will be duplicate records because of the process. The test parts by individual carts, but often they're exactly the same parts with same work orders and same everything else. How should I go about doing this. I don't want to just create an autonumber for each record if possible. Any suggestions?
I have four columns: date1, time1, date2, time2. All are strings.I want to concatenate date1 & time1. Then date2 & time2.Then I want to do the following calculation and the answer to expressed as number of days:
datetime2 - datetime1.
I am using MS Access 2007 and the results are being grouped by a different variable. Ideally I want to complete this action as one complete statement in the SELECT statement of my query. This is very easy to do in Excel but I'm baffled by MS Access!
I have a Db with a table imported from sql server. One of the fields contains data wrapped between xml tags. The xml tag identifiers when looked at as columns are not consistently the same tag identifiers but effectively I am parsing the string to provide columns of data from between the tag identifiers.
example:
<T6><A29>Compton Group</A29><A31>CO009</A31><A32>CO161/71</A32><A30>Invu Proposal</A30><A28>05 September 2007</A28><A41>Sales</A41></T6>
I am wondering if the method I am using to deconcatenate the string is the bext way. In my query I reference the left tag value .
TagVal1: ParseTagOpenVal([Attributes],3)
And then I parse the result based on the right hand tag value
Val1: ParseTagCloseVal([TagVal1],1)
I have a couple of modules for parsing the string and I am able to deconcatenate using this method but I am wondering if its the only way or the most efficient?
I have two tables tblPatients (pkUMRN) and tblAdmissions (pkAdmissionID, fkUMRNAdmission). pkUMRN is not auto-number, but is an 8-digit alphanumeric code unique to each tblPatients record. I have set up a one-to-many relationship between tblPatients_pkUMRN and tblAdmissions_fkUMRNAdmissions (each patient can have multiple admissions).
Is it possible to allow the entry of a new tblAdmissions_fkUMRNAdmissions to create a new tblPatients_pkUMRN? IE can you create a new primary key from a new foreign key?
Along a similar line, if the primary key already exists, how would you autofill a form with data from tblPatients when the tblAdmissions_fkUMRNAdmissions is entered in to the table?
I have two Tables, Table A and Table B...Table A and B have a one to many relationship with A (one) and B (many).I have a Master form that displays information for Table A. Also, I have a subform within this Master that displays information for Table B.
As I scroll through the records of A, you can see the 1-many relationship elucidated in the subform with many being displayed for Table B.I run into a dilemma, though, when I try to ADD a new record to Table A (and in turn Table B).When I add a new record, there is no Primary Key left to be displayed since this primary key is generated from a query.Hence, when I move to a new record, I can not save the record because there is no primary key. Since there is no primary key, there is nothing that i can input into the subform either. I want to create the primary key from values entered in the field. However, i CAN NOT create a relationship with an expression for field values.
This is a real mind bender. I am running a2k. I am merging two databases. table1 is in the backend database. table2 is linked to the second database. I run an append query to add table2 entries to table1. The append fails for 96 recs saying key violation. Turns out I can rerun the append if I drop the zip code field. I then try to manually change the zip code and receive the duplicate values in index, primary key, or relationship.
I ran a compact & repair the databases, still no go. I can't edit the value of the zip code. I import the table to a new database. Still can't change the zip code. I drop MANY of the indexes. Even the index on the zip code field. Run compact&repair. still getting the index message. I even re-imported to a new db again. Still same error message.
I may start again by importing JUST these two tables to a new blank db and see if I get the same issues. I'll keep you posted...but maybe there is someone out there who has seen this error before.
fyi-if I enter a new record, the autonumber field correctly increments to the next available autonumber field. (I say this because of another thread on this matter w/ autonumber fields and bug in A2003)
Is there a way to create a relationship so when a new record is entered into the main table it automatically creates a new row/record in the related table with just the foreign key entered?
I am trying to create a form to enter data into a table that I ultimately will create a report from. I have created a blank table with the columns I need. I created an append query to add the new records and an update query and a macro to run them on click of a button. It all runs but it doesn't append anything to the table. What am I doing wrong?
I am trying to create an expression to pull data from 2011 to present date. I need the data for 2011 to only reflect 1/1/11 through 10/7/11 (today's date in 2011). I need the same for 2012 and 2013.
I don't want to have to enter dates each time I run this, therefore, a formula would be preferred rather than hard numbers.
