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 Replies
ADVERTISEMENT
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
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
Mar 13, 2007
I hope someone can help with this query I am having difficulty with. The table I am querying has several expiration dates and Yes/No Fields
Listed below is what I need and the query needs to return the name and data if only one or all of the following applies
Name
"A Date" is before today
"B Date" is before today
"C Date" is before today
"D Date" is before today
"A Yes/No" = No
"B Yes/No" = No
"C Yes/No" = No
In other words, if all dates are = to today or later and Yes/No Fields are = Yes - I don't need them in the results.
I have tried this several ways using AND and OR but either way my results are ALL the records. not just the records that meet any or all of the above criteria. Maybe it's too much to ask? :o)
Thanks in advance for your help.
Terry
View 3 Replies
View Related
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 2 Replies
View Related
Jan 13, 2006
Is there a way to query any system tables to list all tables that are being referenced in any queries, forms, reports, modules, etc.? We've inherited a rather large Access application containing some tables that we suspect aren't being used at all by the application, and we'd like to verify this suspicion before deleting these tables.
TIA
View 2 Replies
View Related
Sep 26, 2014
there are many threads about Table Linking but i coud not find one that answers my specific questions.
1. Can i either prevent database from linking to my tables? (or give permissins )
2. If not, can i make the table read only when linking?
3. Is there a way to confirm what databases are linked to my tables?
View 12 Replies
View Related
Aug 10, 2005
Just joined this forum and posting my very first thread.. ^^
I need to create query to pull data from two different tables. This should be really easy query for most of you. Once someone help me set it up, I can do the modifying.
If you look at the image, there are two tables. For each of pf_id (which is unique ID), I need to know the dept_name which is on other table. I know two tables can be linked with dept_id but I don't know how to create query. If you can help me , I would greatly appreciate it~
Result should be like below.
pf_id, dept_id, dept_name
___________________________
wh08051bh-Navy, 01-001, washed hat
wh60516bh-white, 01-001, washed hat
.
.
.
ru11072bh-white_with_navy, 01-002, roll up bucket hat
.
.
dh03871bh-Dk._Blue, 01-003, denim hats
.
.
<img src="http://www.highdots.com/forums/attachment.php?attachmentid=183">
http://www.highdots.com/forums/attachment.php?attachmentid=183
View 2 Replies
View Related
Aug 11, 2005
I am new to this forum and a beginner to intermediate with Access.
I was given a database that has 3 tables with data on the capture of alligators. One from 1998-2000, one from 2001-2003 and one from 2003-present. Each of these tables has different field names because they were created by different people on different projects. I need to combine the three tables into one that pulls such data as "date", "time", "size" etc. I don't need all of the fields from all of the tables just select ones and some of the tables do not have the information that I'd need in the final table. I've read through append, update, and make-table queries and am not sure if I can even solve this problem with a query. The error messages I get refer to null values or if I do get a table it has 14000 records, which is way more than the actual number of records.
Thanks for any ideas
View 4 Replies
View Related
Dec 12, 2006
Hi there,
I'm trying to query tables I have imported as 'linked tables' from a SQL database. I've built a query, but when I run the query it says 'Invalid Procedure Call'
Does anyone know why it's doing this?
Many thanks!,
Sasha.
View 1 Replies
View Related
Apr 21, 2008
During a client's stay with our agency, they are served among multiple contracts and are placed within multiple programs. Because clients move frequently between contracts and between programs, we have separate tables which have start and end date fields. It is common for any client to have multiple listings in these two tables, but with different event dates.
I am struggling to create a query that will capture each client's most CURRENT contract AND program. In the future, I'll want to create a parameter query that will allow the end user to enter a range of dates and capture all of the contract and program movement during a period of time.
Any suggestions?
View 4 Replies
View Related
May 11, 2005
I have linked tables from an Oracle database.
I want to run a query to find records that have dates in a defined range.
The date field in my linked Oracle table is in the date/time format.
When I run my Access query, I only get those records that have a date (and no time) in the field.
How can I get all records, even those with a date/time entry?
Thanks!
View 3 Replies
View Related
Feb 27, 2014
how to query data in my database based on a number of different criteria.I have reached a stage where I can get all the data I need from one query, however I can't figure out how to further query this data to return records from a table with the most recent date only. I have searched the forum, googled and experimented myself but I am running into "Aggregate Function" errors.In this scenario there are 3 tables. tblJobs, tblEquipment and tblInspectionLog. Each tblJobs record can have multiple tblEquipment records attached to it, and each tblEquipment record can have multiple tblInspectionLog records attached to them.
I would like to query the database for what tblEquipment records have been assigned to a tblJob ID and also return only the tblInspectionLog record with the latest Inspection_Date field.At the moment I am able to see tblEquipment records attached to tblJobs, however duplicate records appear due to multiple InspectionLog records associated with the equipment.
I have tried to filter records from tblInspectionLog using the "Max" criteria under Inspection_Date field in my query. This however returns an "Aggregate Function" error.
View 4 Replies
View Related
Jun 2, 2006
Hi all
I have field Name called EMPNAME
The records like this,
Brian,Smith
Ray,Vernner
Ken,Ibrahim
Now I want to separete them as Firstname: Brain
How do I find the comma "," in a text?
thanks
:confused:
View 5 Replies
View Related
Dec 10, 2004
I have a control (command button) on a form that opens up a report which filters out a single record. The filter is based upon a value in a text box located on the form. Within the VB code, I put some code to prevent the form from opening if the text box had no data in it.
When i click on the button (assuming the text box has a value in it), I get the error message I defined, and the report opens properly. This tells me there is an error being generated.
My question is, how I determine what error has been generated? And how do I fix it?
Here is the code I used:
//This is the code for the form.
Private Sub cmdButtonPreviewLabel_Click()
On Error GoTo Form_Closure_Error2
DoCmd.OpenReport "reportLabels", acViewPreview
Form_Closure_Error2:
MsgBox "Sorry, but I cannot print a label without a Job Number.", vbOKOnly, "Notice"
Exit Sub
//This is the code for the report
Private Sub Report_Open(Cancel As Integer)
On Error GoTo Form_Closure_Error1
tempvalue1 = Forms![frmjoblog].[JobNumber]
Me.Filter = "[tableJobLog].[JobNumber]= " & tempvalue1
Me.FilterOn = True
Form_Closure_Error1:
Exit Sub
End Sub
End Sub
View 1 Replies
View Related
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
Aug 28, 2005
Hi there,
I need to sort out the repeat customer, e.g. I have a list of customer's name, like Nike, Emerson, Alcan, etc... and if they sign another contract with my company again this year, I will name it like Nike 05, Emerson 05, etc...
So how can I get the result of how many customers have signed the 2nd contract with us. I have no clue now... :confused:
Anyone can give me some hint please! :)
Nadine
View 1 Replies
View Related
Oct 11, 2006
I am trying to identify how long inventory has been in a specific location. The information that I have is the current inventory level and movements that have put the inventory there. Assuming FIFO I would like figure out the following.
ex: I have 4 movements with various dates AND I have 3 pieces currently on hand, how do identify the date of the last 3 movements (assuming that the one that has been moved out was the first one in)? Of course there is a long list of part #'s, on hand quantities and movements into the location so the logic should work for all.
Sample data
Part # Date Quantity
123 09/30/1999 1
123 09/01/1999 1
123 08/30/1999 1
123 07/30/1999 1
456 09/30/1999 1
456 09/01/1999 1
Part # On Hand
123 3
456 1
the Result I would like to see:
123 09/30/1999 1
123 09/01/1999 1
123 08/30/1999 1
456 09/30/1999 1
Is there a way to do this?
Thanks
Pat
View 3 Replies
View Related
Feb 8, 2007
hi
i am in a situvation that i have to identify the dates which comes under particular week1 or 14.
View 3 Replies
View Related
Oct 29, 2012
What I am doing is trying to have access identify and label various lanes of transit.
So, for example. I have the below...
Origin Destination Via Transit Time
Hong Kong New York East Coast 38
Hong Kong New York West Coast 32
China New York East Coast 34
China New York West Coast 29
Is there a way in Access to have it identify that Hong Kong to NY via West Coast is the "DEFAULT" where HKG to NY via East Coast would be "ALTERNATE" ?? And then do the same for the next origin? The table I have now has over 71 different origins but I need it look at each origin independently.
View 2 Replies
View Related
Dec 19, 2006
Here is the problem
I have a tblOrders that has 1800 records with 31 fields primary key tblOrder which is a number.
Periodically users export data from SAP into a spreadsheet, which are changes to the original data but may not contain the exact same fields as the master tblOrders. The data in this spread sheet identifies changes and may contain aprox half of the fields that are contained in the master tblOrders. The field names however (in the spreadsheet) will be of the same type as in tblOrders.
What I would like to do is identify differences between the tblOrders records and the records in the spreadsheet, and then update the tblOrders to show the changes.
how would you proceed? Maybe create a new table by importing the excel spreadsheet into access and then somehow querying the differences and updating the master. If anyone has done anything like this b4 please could you update me...
Cheers in advance.
View 3 Replies
View Related
Nov 8, 2013
I have a global function which I have hooked onto the onClick event of controls. Currently I am using commandButtons as my control of choice. In my function I can then reference Forms.ActiveControl which is no problem.
However, for lots of reasons I really need to use Image controls. Now, my problem. Image Controls have an Onclick Event, but they do not get focus so the Forms.ActiveControl will not work.
How can I identify the image control that has generated the click event if the control itself does not get focus and so cannot be referenced by ActiveControl? Working on a possibly work around, but could do with the best brains in Access on the case!
View 3 Replies
View Related
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
Jul 19, 2015
I have data for multiple account numbers (for work) and dates, and I need to identify when there is a change in account number in order to add a new field with a count - which counts sequentially starting with 1 and then starts over at 1 when the account number changes.
Ex. The red column is what I am trying to create
Account Date_To Counter
12345 06/30/2015 1
12345 05/31/2015 2
12345 04/30/2015 3
19999 06/30/2015 1
View 2 Replies
View Related
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
May 1, 2014
I'm wondering if there's any way to find out who has a database open.
I've saved the file as an ACCDE for the users and kept the ACCDB file as my working file. Whenever I make changes to the db I need to save a new ACCDE file on top of the existing one. In order to do that, I need to ensure no one has the db open. Half the time I need to track down 1 of 15 people to find out who it is that has it open so I can kick them out and save.
I don't have any login features, but could use the POD # that each cubicle has associated to their computer if Access allows that...
View 8 Replies
View Related
