Queries :: Identify Subsequent Records Where Original Record Meets A Criteria

Aug 8, 2014

I have a table [PickData] in a WMS (Warehouse Management System) database, that records details of each item picked. The key fields are;

[Movement] - a unique ID for a collection of items to be picked.
[ToAssignRef] - the order ID
[Product] - the product!
[Picked] - the date/time the item was scanned
[Pick Actioned] - the date/time the [ToAssignRef] was completed
[Reason] - A code to indicate why an item could not be picked - AKA F3'd

An operative would be allocated a movement, connected to the [ToAssign Ref], containing a number of products to pick. If an item can not be picked for some reason the operative presses F3 and selects a reason (no stock, damaged etc). These F3'd items (other stock allowing) will later be picked on a different [Movement]. I need a query to identify the subsequent [Movements] and the associated fields following the occurance of an F3'd [ToAssignRef] & [Product].

I have a query, but it runs very slowly (perhaps due to the DB size currently 780K records). Is this the right approach, is there a better (faster) way to do this?

Code:
SELECT PickData.ToAssignRef, PickData.Product, PickData.Picked
FROM PickData
GROUP BY PickData.ToAssignRef, PickData.Product, PickData.Picked
HAVING (((DCount("[Movement]","[PickData]","([ToAssignRef]='" & [ToAssignRef] & "') AND ([Product]='" & [Product] & "')"))<>0));

View Replies


ADVERTISEMENT

Reports :: Showing All Detail Records If One Record In Query Meets Criteria

Mar 5, 2013

I am trying to create a report which basically includes the following:

Company, Wages, Contribution.

Each company reports wages for each employee every month. Then they also contribute to a general fund based on a percentage of the wages. For instance:

Company---Employee---Wages---Contribution
CompanyA---EmployeeA---$4000---$40
CompanyA---EmployeeB---$3800---$38
CompanyA---EmployeeC---$3800---$38
CompanyB---EmployeeA---$4200---$42
CompanyB---EmployeeB---$4200---$42

...and so on.

Each employee is required to contribute, in this example, 1% of gross wages to the general fund.

On occasion, the company does not pay in the required 1% of gross, say, for CompanyA EmployeeA, they only paid in $35.

Here is what I need to do. If any contribution amount for any employee is incorrect, I want to display all the records for that company, not just the incorrect ones. The report is grouped by Company, and may contain dozens of companies.

I am already passing a number of criteria to the report using a filter, including the date range and other fields which are informational.

View 6 Replies View Related

Queries :: Insert Text From Textbox On A Form When Combo Box Meets Criteria?

Jul 12, 2013

Is it possible to insert text from a textbox on a form (Data) when a combobox on (Data) meets a criteria?

Example: Test: IIf([Results]="Positive" text207)

So if the Results combobox is Positive then the text from Text207 is inserted.

View 9 Replies View Related

Queries :: Identify Duplicates With Three Criteria

Apr 5, 2013

I have the following columns in my table 'Catalogue'.

Contract_contractitem
contract
Supplier name
Item number
Short description
Long description

I need to identify duplicates if the short desciption is the same, long desciption is the same but the item number is NOT the same.

e.g.

4700000551_978, 4700000551, FISHER SCIENTIFIC BIOTECH LINE ApS, mbp-6014, 0,2 ml PCR Tube, Hotstart, wax bead, NS, 0,2 ml PCR Tube, Hotstart, wax bead, NS 96 tubes/pack, 10 packs/Cs

4700000551_977, 4700000551, FISHER SCIENTIFIC BIOTECH LINE ApS, mbp-6010, 0,2 ml PCR Tube, Hotstart, wax bead, NS, 0,2 ml PCR Tube, Hotstart, wax bead, NS 96 tubes/pack, 10 packs/Cs

View 4 Replies View Related

Queries :: Double Click On Results In Order To Go To Original Record Or See More Details

Jun 2, 2015

Is it possible to once you have run a query be able to somehow double click on the results in order to either go to original record or see more details? Similar to "show details" in excel?

I have made some queries that are working well however they are specific in nature and once found display a list of results. I have left a field that uniquely identify them and are after a quick way to view the details of the results from the query rather than take the ID# and manually search the original table.

View 8 Replies View Related

One Field Meets Criteria Of Another?!?!

Jan 16, 2007

Hi,
I have one field, for this example, FieldA and another FieldB.
I would like the following:
If fieldA =1 then FieldB must = 1 or 3 or 4. If not then I would like a message box saying it needs to check. Otherwise I don't want anything to happen.

How can I do this? and where do I put any expressions?
thanks
Sue

View 7 Replies View Related

Queries :: Identify Missing Records Between Queries

Jul 14, 2015

I have a query that will draw down student details who have completed a course in a given month (May for example), i would like to use this data to identify those learners who are not enrolled on a course in the next Month (June for example). There is no field that denotes whether a student has left only that a course assosciated with their ID has a completion date within that Month. There are approx 250 records.

In my head it should work something like this

1) Identify all learners who finsihed a course in May (Identify learner ID, must have a course end date in that month)

2)Cross reference these against all those who started a course in June and identify the students that have completed in May but did not start a course in June.

Is it possible to store all those who completed (May) in a table/query and cross reference those who started in June and identify of the May completions who did not start in June?

View 3 Replies View Related

Validation Condition Where At Least One Of Four Fields Meets Criteria

Jul 29, 2015

Windows XP
MS Access 2007
VBA experience is very limited

So in my forms 'BeforeUpdate' property I want to validate whether a condition is met. The condition is whether 'any one of 4 fields contains something (text or number)'. I have written this code for validating a single condition and it alerts if the field is left blank. This code returns a warning message when the field is blank.

If Me.[txtObjective] & "" = "" Then
MsgBox "A 'Problem Objective' is required, please complete this field.", vbOKOnly
Me.[txtObjective].SetFocus
Cancel = True

[code]...

how to write the SetFocus part. And I dont know if this is correct anyway.

View 6 Replies View Related

Select Those In A Column That Meets Character Count Criteria

Nov 4, 2005

Hey, I know this is probably a simple thing to do, but I couldn't find it anywhere on the web so hopefully someone here can help.

I have a column of UPC codes which are a bunch of #'s. I need to find the ones that have more than or less than 12 characters.

Thanks

View 2 Replies View Related

Modules & VBA :: Code To Add Data In The Listbox That Meets Certain Criteria?

Jul 17, 2014

I have a Access table that stores BatchNo,Scandate,NewBatchNo . As I can't attach Access database here so I have exported data from Access to Excel in Sheet1 to show how the data is stored in Access table.

Now I have to write code in VBA that will check the last NewBatchNo in the table . In the attached workbook its 194389. Now vba code should check the NewBatchNo which are blank before 194389 . Now in the records where NewBatchNo is blank , it should add the corresponding BatchNo,ScanDate in the listbox1 in form1.

e.g Sheet2 in the attached workbook stores Bathcno and Scandate of those records where NewBatchNo is null and before the Last used NewBatchNo which is 194389 in our example.

View 4 Replies View Related

General :: Automatically Delete Data That Meets Criteria?

Mar 15, 2013

I'm an amateur (is there such a thing as having databases as a hobby?) with little training. I volunteered to organize a growing volunteer group I'm a part of by making a simple database for the volunteer office staff. One of the things I want it to do is make name tags for our weekly meetings. I don't want to print one for each name on our list because the list is over 400 names long with only around 35 regular attendees. I made two tables--one is the MakeNameTag table that lists regular attendees (it is a lookup field on the Names table).

The other is the Absences table. It has two fields; DateOfAbsence and MakeNameTag. If someone misses a meeting (does not pick up their name tag), their name is added to the Absences table. A query then filters the table for dates in the last 28 days. If their names shows up four times in the last 28 days I want for their name to be deleted from the MakeNameTag table. How to do that automatically? Our office volunteers have minimal PC skills, so the solution needs to be very user-friendly.

View 3 Replies View Related

Queries :: Return List Of Records From Original List

Apr 23, 2013

I'm looking to move an excel sheet to access because the row counts are too much.The main thing it does is compare the supplied data against a list I hold in the sheet.There are not duplicate records, however..Some data is a direct lookup for a full match, but much of it is a count to see how many records contain a certain string.

I have 500 keywords which have a countif function in using wildcards.I need to create a query/report which will return a list of records from the original list which contains each keyword featured and how many times it features.I was going to do it in PHPmysql but the time it took to parse a million records for every keyword made it pointless.

eg:
keywords:
look
billy
magic

list:
"have a look and see"
"spanish dave"
"who is billy brag"
"looky looky I go hooky"
"who's the man from argentina"
"could it be magic now"

my spreadsheet would return a 1 next to ""billy" and "magic" and would put a 2 next to "look".

the sheet has the keyword in each row and next to the column:
=COUNTIF(list,CONCATENATE("*@",B13))
where "list" is the external data.

View 3 Replies View Related

General :: Using A Combo Box To Set Default Value For Subsequent Records?

Nov 29, 2012

I am trying to create a user friendly form to enter a list of competitors entering an event.

I would like the user to pick from a list of events and that event to be used for subsequent records until the user changes it.

I have used a combo box to pick from a list of events but that needs to re-selected for every new record. I then added a second unbound combo box to give the main combo box its default value. It works but is not very user friendly.

View 4 Replies View Related

Same Criteria For Querying Two Nearly Identify Tables

Dec 22, 2005

I have two tables, which have same fields. The tables are used for keeping record of news clippings, that are clipped in two different offices. I understand that the best way to manage the database will be combining them into the same table. Yet, it's not an option for the time for some technical reasons. Each table has the same fields as follow:

Input date
issue date
headline in English
news source
category

I want to have a list of news clips from the two tables by a specific category. I will have a form, that allows user to select category as the criteria to query the two tables. I manage to do that in a single table, but how to do it for two tables. This will be very help if you can help. ideally, I can use category to query news clips as well as academic journal clips (from another table) table.

Thanks.

View 2 Replies View Related

Identify Any Changes Made To Query Criteria ?

May 10, 2007

Hello,

I was wondering if there is anyway to be able to identify or monitor changes made to a query.

Basically looking to identify if anyone goes in and manipulates the criteria field within the query ?

Using Access 2003.

Any thoughts or help are greatly appreciated.

Thanks

View 2 Replies View Related

General :: How To Reference Original Records ID

Apr 8, 2014

So I have a list of records in a continuous form. Within that record is a checkbox called GRT. GRT stands for Gross Receipts Tax, when it is checked I have an after update event that adds a new auto-generated record for the GRT entry. The new record copies the date from the origional record, calculates GRT from the origional records amount field and some other things like that to create the new record. Creating the GRT record entry has went well.

But, if the user removes the GRT checkbox from, or deletes the origional record, I need to delete the corresponding auto-created GRT record. I thought I could do this by simply grabbing the origional records ID and copying it into the auto-generated records field called "AssociatedID".

Problem is, when I reference the origional records record ID, it always comed in as "-1". I can display the ID on the form and it shows correct, but if I msgbox the records ID, it shows as "-1". I cannot figure out how to capture the origional records ID so I can insert it into the new auto-generated record so the two records are linked so I can later use that link to delete the proper associated record.

How do I reference the original records ID.

View 4 Replies View Related

Identify Problem Records

Aug 16, 2005

Hi

i have a query that takes fields from various related tables to give me the information that i want

The primary table has 199955 records

The query displays only 199939

16 recordss are missing

I know why this is: the primary table (CWT reqd equip) has a field on it that is a primary key on another table (cwt Equip). On "CWT reqd equip" 16 records must have an entry in this field that is not present on the table where the entry is a primary key (cwt Equip).

I dont know how to identify what these 16 records are on the primary table. I want to get rid of them so i can enforce referential integrity between the two tables.

can anyone help? Have i wrote this clear enough?

thanks


p.s i've attached a doc showing how the query is built. the other tables other than the two i mentioned aren't important, for this.

View 2 Replies View Related

Forms :: Based On User Selection In A Form / Create New Records For Subsequent Form

Feb 3, 2014

The user will be creating a new project that contains a bunch of releases. The releases have standard names which are stored in a table tbl_ReleaseNames It should be noted that the list of names is not static.

The user selects which of the releases pertains to their project and then based on their selections, new records would be created in tbl_RFP_Release and then a subsequent form would open where it would display each of these newly created releases where they could enter additional information. I thought of creating an unbound checkbox associated with each of the standard names, and then checking to see if the checkbox was checked and then creating the new records followed by opening up the new form.

View 6 Replies View Related

Forms :: Identify RECORD BY Number And Year

Apr 14, 2013

I have a form linked to table tblINCIDENTS. When I create a new incident, I select DATE_LOGGED in a text box. This updates the field in tblINCIDENTS.

I would like to show on the form (and store in the table), e.g.

"Record 5 in Year 2013"

I already had the new record number as an autonumber, but now I created 2 extra fields in the table - recordno and year.

I have a hidden text box which, on DATE_LOGGED.CHANGE, finds the Year() from DATE_LOGGED. I have tried using a Query to find the MAX RECORDNO of YEAR. I managed to display the Max (having manually populated the fields!) in a textbox, but am now going round in circles!

View 14 Replies View Related

How To Identify Which Record In Continuous Form Was Clicked On

Nov 6, 2011

I have a continuous form with an image and a text box on it. When the image is clicked, some text should appear in a text box. The problem is that the text appears in the text box of the incorrect record.

Suppose the first record is the 'current' record (i.e., having the record selector to its left), and the user clicks on the image in the third record. Then the text appears in the first record, not the third.

Is there a way to find out which of the records was clicked on? Or, can I change the current record to be the one whose image was clicked on?

View 13 Replies View Related

Unique Number To Identify Each New Record Created

Sep 23, 2011

Example 1:

2011-1
.......
2011-3893 etc.

Currently I have an Access form which produces a new unique number to identify each new record created. To do this I use the unique ID autonumber from a table to identify the new records. I would like to change from this simple number to the the above format per example 1. The four digits to the left of the hyphen would always be the current year and digits to the right of the hyphen would be the unique auto incrementing numbers such as from my table. I need the year to auto increment by 1 each September 30th (new business year) and I need the numbers to the right to auto reset to 1 to start uniquely identifying records again for the new incremented year. As each record is closed I need the number to be written as a single entity in the new format to my database.

Example 2: After September 30th.

2012-1
.......
2012-447 etc.

View 3 Replies View Related

Queries :: Removing Special Characters Where Original Data Length Varies

Mar 1, 2014

I thought I had solved my initial problem of removing the apostrophe character for each zip code field. Most of the fields have data that reads '12345-1234'. I want to remove each (') character and the "-1234" so I end up with a zipcode of 12345.

I created 2 queries for this process. I first run a query with the following statement "Left([ZIP],Len([ZIP])-6)". That gets rid of the "-1234' ".

The I run the second query with this statement "Right([ZIP],Len([ZIP])-6)".

These 2 queries work perfectly if the original zipcode is " '12345-1234' ", but if it is " '12345' ", the entire zipcode is deleted.

I have attached 3 (.jpg)'s to show you what I am talking about.

View 7 Replies View Related

Queries :: Formula Identify Differences

May 22, 2014

Basically I would like to populate column three "OUTCOME" according to the following logic:

if train A10 has multiple destinations (we see this in the second column) then in outcome I woudl like to have "multiple destinations" if the destination is only one (in case of trains A15 and A16) , then I would like to have that city in column 3 "OUTCOME".

View 3 Replies View Related

Queries :: Form List Based On A Query Returns Original ID Value Not Field Text

Aug 14, 2014

I have a table x where the field value is selected via a combo box in a form that is populated from another table z.

When I look in x, it appears to have correctly stored the text from z, not the ID number.

I then built a Query, qX, which looks in x and grabs the fields I want. That query shows the text correctly in each selected field.

Now when I build a form, frmQx and use a List control, it displays some of the fields as ID values from the original table z, not the text values.

How can I get round this? I've searched and searched for an answer, sigh. Maybe I'm just not quite certain how to phrase the search.

View 5 Replies View Related

Queries :: DLookUp To Identify Base Cost Of Show

Feb 5, 2015

I have a table that holds information for a type of show and the base cost of the show. I am trying to make a query that gathers information for all the shows in the table and uses the Dlookup function to identify the base cost of that show. This will in turn be used in the same query to create a quote, but I can not get the Dlookup function to work and I am sure that I may be doing it very wrong.

The table is called ShowList. That table contains 3 fields, ID, Show Type, and Cost.

The query is called Venue Cost and will have a series of fields that work to develop some math on all items from another table called Venue Info. Venue Info uses ShowList to select the type of show.

What I want the query to do is look up the Show type from VenueInfo and then look up the base cost of the show type from ShowList. What I have been using is not working but is as follows:

DLookUp("Cost","ShowList","[Cost] =" & "[Venue Cost]![Venue Type]")

View 4 Replies View Related

Queries :: Unique Records BUT With Additional Criteria

Oct 27, 2014

The main table for the database I am working on contains the following fields:

ID
Mfr Control Number
Initial or Follow-Up
Follow-up Number
Suspect
Date of Initial Email
Date Received
Date Submitted
Date of Report
Serious
Brief Description
Causality
Notes

With some additional qualifications I wanted to find records that had an intial report but no follow-up. Which translates too I want records that are unique in the Mfr Control Number field (no duplicates).

I am trying to build a query and keep getting hung up on the unique aspect of fields. I started by trying to query only "Mfr Control Number" fields that are unique (no duplicates). As best I can figure for some reason I can not add any additional fields to that query. My current query is set up in the query build table such that I have added "Mfr Control Number" in two columns. The first column in Total I have "Group By". In the next column I have set Total to "count" with a criteria of 1. If I try to add any other fields from my table than I seem to lose those unique results. But I need to further filter to get the exact information I need.

I want criteria on the "Initial or Follow-up" field to only bring "initial"
I want criteria on the "Serious" field to only bring "serious"
I want criteria on the "Date of This Report" field of "<Date()-"15""

Is there some way to take the results of that initial query to then build a another query based just on those records? I could then apply the further criteria and run my report. Or is there a way to do this in one step?

I've tried to make clear my intention but know it can be difficult to get this kind of stuff down in writing in a clear fashion. I have to be careful to keep information confidential also so some of the details are vague.

View 2 Replies View Related







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