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.
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)
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));
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.
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?
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 . .
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.
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'
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.
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?
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.
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)
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.
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:
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.
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.
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...
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!
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".
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.
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!
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...
