Queries :: Preventing Repeat Values In Only One Field
Apr 7, 2013
I have a query which generates a full list of times and appointments for a given day from two separate dates using a right join and enters a "-" in the field for unallocated time slots using an ISNULL function.
I want to refine the query further so that if a single task/appointment is block-booked in multiple adjacent time slots, I only want to return the start time of the first slot, the end time of the last slot and display the task name once.
The intention is that this will be used to create a daily timetable list in an Access (2000) report with the date being specified in a Combo box in a form in which it will be embedded - though I'm not concerned about the form/report design at the moment, only how to do the query.
I've tried several variations of SELECT DISTINCT and using nested queries to no avail.
View Replies
ADVERTISEMENT
Aug 7, 2013
Beginner with Access 2010 - making additions to one field in a JunctionTable. Need to extend a field by adding some new numbers but have found it easier to Export to Excel - make changes and replace in accdb.
I have found an Excel formula that does what I want but starts from the number "1".
=INT((ROW()-1)/9)+1
Which starts with number "1" and repeats this 8 times then increments by one and repeats 8 times.
But I want to start with a number of choice (7270 and go to 9028). Have seen some VBA versions but none give me control of the start number. I am then able to update the second empty field on the JunctionTable from a query. (this is a fixed number).
View 9 Replies
View Related
Oct 12, 2012
I am trying to modify an inventory management database. I want to prevent duplicate entries to specific fields in centralized table. The table is called Work Stations. Its function will be to track various computer equipment by a specific work station name. Each of the items will have a unique asset tag.
The primary Key for Work Stations is WS-ID. The fields I am trying to prevent duplicate entries in are WS-Computer, WS-Docking Station, WS-Monitor_1, WS-Monitor_2, and WS-Switch. Each of those fields are primary keys in 4 different tables that conation more detailed information about item. The exception is WS-monitor_1 and WS-Monitor_2 have a one-to-many relationship with Mon-Asset Tag in the monitors table.
The goal I am trying to accomplish is when a work station ID is created or modified duplicate entries are prevented to those fields listed above. The computer, monitors, docking station, and switch fields in the work station table may contain data or may be null.
I have tried to set the Indexed Option to Indexed (No duplicates) for each of the fields and I have also tried setting Yes to Ignore Nulls in the index option on the table design tab for the individual fields. Both options have returned the same error stating the changes would create duplicate values, in the index, primary key, or relationship.I have not created form for this table yet as I was trying to get no duplicates option to work first. I have verified the data and the only duplicating fields are the Null fields
View 6 Replies
View Related
May 5, 2014
I am working on a database to track IT assets with third parties. I have a table called "Equipment" that includes info like model, serial numbers, purchase price, date, location, and "Asset ID". I have a second, single field table called "asset tag" that is just a list of asset ID tags, XYZ1000, XYZ1001, XYZ1002..
I created a one to one relationship between the two tables on the following fields: "equipment.assetID" and "asset tag.asset ID"
Once an "asset ID" is used, I would like it to either be grayed out or disappear from the list of available ID tags. Basically, I want it so that each "asset tag. Asset ID" can only be used once.
View 3 Replies
View Related
Jul 22, 2005
I have a table with a multi-field unique index:
PatientID
TestId
TestDate
Examiner
The problem is, date and/or examiner can be blank until that information is updated. I want those null values to be understood as actual values so that multiple instances of that "waiting to be updated" record do not occur. Is it possible for Access to understand my indexes in the following way?
PatientID----TestID----TestDate----Examiner----(Index)
1------------1---------07/18/05----AD----------1107/18/05AD
1------------1---------[blank]------[blank]------11
and therefore not allow another record like the second one to be added? The ignore nulls property of my index doesn't seem to affect this issue?
Thanks!
Carly
View 1 Replies
View Related
Jul 6, 2005
I read a lot for work and the material is from many different disciplines. To keep track of all this information, I set up a simple db to keep track of all my references and have fields containing various bits of information for each record. In the beginning, I assigned one keyword to each reference as a means of retrieving articles on related subjects. Over time it has grown to three keywords per record although some of the keyword fields are null. All keywords are in a single, separate table and I add a keyword to a record from a pick-list. When I increased the number of keyword fields to three in tblCatalog, I just added more links to tblKeyword. Now, I am having a tough time trying to query tblCatalog. Obviously, my basic db organization is not right so I am looking for suggestions on how to straighten out this mess. Here is what I would like to do:
* During data entry, select form one to three keywords for each record
* During data retrieval, concatenate all the keywords (one, two or three) and
list the resulting text in a single control on a form.
This seems like it ought to be easy but not today. There must be something fundamental that I do not understand.
View 2 Replies
View Related
Mar 2, 2015
I have two tables.descriptions I'd like to relate and use to find/replace in bulk.
[Checking].[Description] (with the source data)
[Rename].[NewDescription] (with the correct data)
I'd like the values in [Checking].[Description] to be replaced with the values in [Rename].[NewDescription], including those that are "Like".
Examples:
[Checking].[Description] = Geico 12345
[Rename].[NewDescription] = Geico
[Checking].[Description] = Geic
[Rename].[NewDescription] = Geico
View 4 Replies
View Related
Oct 21, 2005
Hi there
I am new to Microsoft access and I am currently exported an advanced revelation database in CSV format to access now because advanced revelation is a multivalue pick database I needed to create a the key for the multivalue fields which I have separated the data which is NI.
Now all the data is separated in order but the NI field only shows once then its blank going down till the next NI which is the next record this is due to to the multivalue fields data that has been separated.
How can I get access to copy the NI number down into the blanks slots till it reaches the next slot and then copy that one and so on in a loop once this is done I can use this as a key field and separate the data further..
Regards
Norman
View 2 Replies
View Related
Feb 28, 2014
I have some queries that pull data from one table and other queries that pull data from two related tables. In both cases, how do I prevent the data from being changed after the queries results are displayed? I know I could make a form and lock each field so it couldn't be changed, but I was hoping to avoid having to do that.
View 6 Replies
View Related
Jul 18, 2014
Trying to create a query from a table. This table has some fields with zero all the way down for all the records and i wish for the query to omit the whole field if that is the case.
View 9 Replies
View Related
Oct 31, 2013
I have a material usage table that tracks material going into products...Oftentimes the same material will be used and multiple records will contain that material.
I want my query to return only records with unique material used. I'm clicking the "Unique Values" and "Unique Records" property setting but its not working.
View 1 Replies
View Related
Apr 14, 2014
I have a query that takes a value, Proposalvalue, and depending on the currency, loc curr, it calculates the currency. It gets the currency value from the currencies table and appends to TableB
eg.
proposalvalue currency
50000 1
Currencies
id Value
1 0.6587
This creates the conversionvalue = 32935 in the TableB.
I have a form that can viewedit the data in TableB.
Using this form, I want to be able to change the proposalvalue and for it to automatically update the conversionvalue.
View 2 Replies
View Related
Jul 10, 2013
I have a table [VL] with four fields, [vl_id], [product], [vl_date], [valeur_liquidative].
The idea of the table is that you can input and update the value of each product on a given day.
I need to find the difference in days between successive dates (vl_date) each time that a product value (valeur_liquidative) is updated. Values aren't updated everyday as it is not updated during the weekends. I have had a go at this but have struggled..
A picture of what the table looks like is attached for reference.
View 9 Replies
View Related
Jun 21, 2014
modify the code below to Show the LocationName in the Schema Column instead of the MPID? I attached a pic showing the relationship between the two tables which contain the data I'm trying to query.
SELECT [Locations Query].LocID, Qry_MPLoc.MPID AS Qry_MPLoc_MPID, [Locations Query].Location, [Locations Query].Schema
FROM [Locations Query] INNER JOIN Qry_MPLoc ON [Locations Query].[LocID] = Qry_MPLoc.[LocID];
View 11 Replies
View Related
Feb 22, 2014
I have a table where there are multiple vehicles, each identified by their vehiclenumber. Each record holds the vehiclenumber, date and odometer reading. I need to figure out how to calculate records in this table per each vehiclenumber.
Below is a code that works, but only when i have each vehicle with the same vehiclenumber.
SELECT tblOdometer.VehicleNum, tblOdometer.ODate, tblOdometer.Odometer, tblOdometer.Odometer AS OdomAlias,
Nz(DLast("Odometer","tblOdometer","[Odometer] < " & [OdomAlias]),0) AS Previous, [Odometer]-[Previous] AS Difference
FROM tblOdometer;
View 4 Replies
View Related
Feb 4, 2014
I have a table with a string field included for an address.
Some rows have postcodes and some dont
How can I extract the PostCode values from the field?
View 14 Replies
View Related
Jun 18, 2015
I am trying to get sum "+" and "-" Values in same Field. I created new Expression Builder in Query and i wrote there :
A: Sum([tablename]<0)
of course it did not work.
How shall i update my expression to work this thing?
View 8 Replies
View Related
Jun 23, 2015
I have a calculated field in a query. The calculation only works if the fields have values. This is what I used a the calculation:
PrimaryShareTotal: [Primary1Share]+[Primary2Share]+[Primary3Share]+[Primary4Share]
I tried using the Nz command
PrimaryShareTotal: Nz([Primary1Share]+[Primary2Share]+[Primary3Share]+[Primary4Share])
but this doesn't work.
View 1 Replies
View Related
Sep 22, 2014
For my study on academic research I need to match patents that refer to academic research as prior work with the actual prior work.
I have two tables (see attached images below).
One regarding AcademicPublications (AP), which is neatly organized with title, year, journal, volume, pages, first author, etc... 480,000 rows
One regarding Patentswhere all this information is hidden within one field, in the most messy way possible... for instance, a field could have:
Quote:
Sugita et al, "Nonsurgical Implantation of a Vascular Ring Prosthesis Using Thermal Shape Memory Ti/Ni Alloy (Nitionl Wire)," Trans. Amer. Soc. Artif. Intern. Organs, vol. 23, pp. 30-34.
or
Quote:
Willingham et al., Cell 13, 501-507 (1978).
Or many other ways.
I want to create a new table that is set up like this:
Patents.PatentNumber | AP.ID | Patents.Reference | AP.Title | AP.Year | AP.Volume | AP.PageStart
The question is: How do I match different fields from one table on one field of another and make it return another field (the ID)? Some references are too horrible to match, but I need as many as I can get.
I can imagine two queries would give me the bulk:
A match in [Title] AND [Year]
A match on ([SourceTitle] OR [AbbreviatedSourceTitle] ) AND [Volume] AND [Year] AND [PageStart]
I understand that I have to make use of the Like "*"&[value]&"*", but how do I make it return the matching ID?
View 10 Replies
View Related
Nov 26, 2013
I have a query in design view that consists of two calculated fields at the moment. One of the calculated fields is supposed to add one year onto a value from another date/time field called "Last_Date."
The expression I've written in the "Field:" box in Query design view is
Expr 2: DateAdd('yyyy',1,[Last_Date])
When I try to run the Query it doesn't work! Access just prompts me to enter a parameter value for Last_Date.
View 1 Replies
View Related
Oct 7, 2013
I have several projects with different tasks for each. I have 3 fields [fkProjectsID], [TaskOrder] and [Duration] in a table for project tracking with that structure:
[fkProjectsID] [TaskOrder] [Duration]
1 /1 /5
1 /2 /8
1 /3 /15
1 /4 /6
2 /1 /8
2 /2 /30
2 /3 /25
I want to calculate cumulative values stored in [Duration] field (represent a number of days). I'm using the field [TaskOrder] to order different tasks within each project. With some testing, I was able to calculate cumulative [Duration] with 1 project using the DSum fucntion as following:
CumulDuration: DSum("[Duration]", "[tblProjectTracking]", "[TaskOrder]<=" & [TaskOrder])
I was having the sequence: 5, 13, 28, 34 for respectively Task 1,2,3,4. However, when I add a second project (and then a third...), I need to be able to filter based on [fkProjectsID] as well (i.e. a specific DSum by ProjectsID). I add this criteria but I get the sum of [Duration] on each row instead of the cumulative:
CumulDuration: DSum("[Duration]","[tblProjectTracking]","[TaskOrder]<=" & [TaskOrder] And "[fkProjectsID]=" & [fkProjectsID])
View 3 Replies
View Related
Jul 2, 2015
What I have a a form that my lab supervisor would use. That person selects the records that are to be modified and assigns work, completes work or otherwise updates the status of the records. One of the options is to mark a record as not having a sample here if it has already been marked as having been here. So essentially, I want to modify the record to change the sample arrival date and sample number field (which is a foreign key field) to null or empty. I have created a delete query that deletes the corresponding record just fine.I just am having difficulty updating the two fields mentioned above. When the supervisor selects the record the primary key for that record is also picked up so it is easy to know exactly what record to adjust. The query returns the information to be updated just fine, it just doesn't do that. Here is the sql of the query. As I said, I don't care if the fields are empty or null. Also the fields aren't required.
Code:
UPDATE TestRequestTable SET TestRequestTable.SampleLocation = "", TestRequestTable.SampleArrivalDate = ""
WHERE (((TestRequestTable.TestRequestNumber)=[Forms]![LabScheduleForm]![TRNumberCombo]));
View 3 Replies
View Related
Aug 23, 2013
I'm quite new to Access and have inherited a database from a colleague. It is a database of references from which we have extracted pesticide active ingredient, pest species and crop species using forms. I am trying to create a query that will allow us to search and return unique active ingredient x pest x crop species combinations (but which will return all instances of this combination).
The complication is that the pest and crop species names are within the same field ('taxa'). They are distinguished by a label ('pest' or 'crop') in an associated field ('PestorPredator'), but a query using 'OR' will only return a crop OR pest, whilst a query using 'AND' will return no records (because something can't be a pest AND and crop).
I want to be able to create a table that shows the active ingredient, associated pest species AND the associated crop species each in separate columns. We have tried a couple of methods, including UNION and UNION ALL....
View 2 Replies
View Related
May 7, 2014
I have a spreadsheet which contains data exported from another system (which I have no control over). I'm using this spreadsheet as a linked table in my database.
The problem I am having is that I can't guarantee that when the spreadsheet is updated, it will contain the same column headings as it did the last time...
The spreadsheet contains a list of temps, with a summary of info off their submitted timesheets. So the column headings (as well as WorkerName, TimesheetDate, etc) may be "Standard Hour", "Overtime Hour", "Over 12 hrs Hour", "Standard Day", "Overtime Day", etc - for each type there will be a Pay Rate and a number of units (hours or days) claimed. The columns only appear if 'someone' in the spreadsheet has claimed something under that heading this week.
What I need to do is to produce a report which gives a summary by person and week of the number of hours claimed and the total charge. I've done that - that part was easy The part I'm struggling with, is how to take the column headings and turn those into descriptors for each charge type... in otherwords, to go from the sample 'timesheet' below to the sample 'ByType' ?
... when I don't know which columns will be present each week?
At the moment I'm using a union query to pull out the info I need, but if the column headings change then I know it will stop working...
sample of my union query... I currently have 8 different sets of bill rate and charge rate, this just does the first couple...
Code:
SELECT qryTimesheetBaseData.[Time Sheet Start Date], qryTimesheetBaseData.[Time Sheet End Date], qryTimesheetBaseData.[Cost Centre], qryTimesheetBaseData.Worker, "Standard" AS RateType, "Hourly" AS RateCategory, qryTimesheetBaseData.[Bill Rate (ST/Hr)] AS Rate, qryTimesheetBaseData.[Time Sheet Billable Hours (ST/Hr)] AS Billable, qryTimesheetBaseData.[Time sheet Status] AS Status
[Code] ....
View 3 Replies
View Related
Feb 26, 2014
I have a table listing about 20 elements as field names eg FE, CR, NI, TI and so on.
I have built a form which has a combo box listing these elements by selecting "fields" in the property settings of the combo box & next to this combo box i have 2 text box's where the user can input Min & Max values to pass on to the query.
E.g., FE (chosen from the combo box) value between (Text box1) and Text box 2.
I can run the query to give me values between the 2 text box's by using the following formula in the criteria (Between textbox1 and textbox 2).
The issue i have is to be able to select the element from the listbox, input the min & max values identified and be able to pass this to a query so the query can filter based on the field and values passed?
View 3 Replies
View Related
Jul 1, 2013
I am currently working on an instrument datebase, I have a mainquery that takes care of user inputs from a form. The main fields that have been queried on are Type, System, and Manufacturer and they are all look-up fields that contain some null values.
On the same criteria row for these fields, I have
Like IIf([forms]![User Interface].[qtype2]="","*",[forms]![User Interface].[qtype2])
Like IIf([forms]![User Interface].[qsys2]="","*",[forms]![User Interface].[qsys2])
Like IIf([forms]![User Interface].[qman1]="","*",[forms]![User Interface].[qman1])
qtype, qsys and qman are the user inputs from the user interface that returns look-up table values.
This works fine when all 3 of these fields are all filled out for a certain instrument. The problem arise when some fields of the instrument are left blank or is null. The instrument won't show up in a query at all. What I wanted it to do is to show everything including the ones with null fields when the user input are null or "". When the user specifies certain requirement I only want to show the ones that are not null. I understand that putting them on the same row means AND, I have tried to OR them and did not have the result i wanted.
View 3 Replies
View Related
