i want to find out all the tables that contain a particular field. eg.employee_ID exists in employee table and other tables, how i can find out what are the other tables? can i run a search query to do this?
View RepliesI have:
1) a table (T) with fields (F1, F2, .... , Fk, .... Fn). I do not know "n"
2) a form (F) bounded to this table
I know that I have a control bounded to field Fk.
How I can find what control is ?
Something like this:
Dim MyCtl As Control
Dim ctl As Control
For Each ctl In Controls
If ctl IsBoundedToFieldFk Then
Set MyCtl = ctl
Exit For
End If
Next ctl
How would one go about comparing 2 tables and finding those records that are not common to both tables? For instance, I have a table that I suspect some records have been accidently deleted by the user. I would like to compare that table to an older version of the table, and list only those records that exist only in the older version of the table.
Thanks.
Ok this might be a really stupid question, but in the quest to always make my life a little bit easier thought i would ask !
I have been asked to find the relationship between a number of tables and queries, so that this company can find out which tables and fields in tables are no longer really used, in other words how many queries and which ones is table A used in if you understand what I mean !
doing it manually means checking every query (and there are a lot !!) and seeing which table they use.. what I want to know is what is the quickest way to to this? can I do it from the table end? in others find out how many queries Table A is used in ? or do I have to go through each query individually? if this is the case my thoughts were just on copying and pasting the sql view from every query into a a word or txt doc and then doing a search /find for each table name? but if anyone has a more efficient idea on how to do this if u cld let me know !! :confused:
This may have already been addressed, but I can't even think of how to word it to do a good search.
I have one table, tblInvoices. I've done a query to search for "Micro*" in my [supplier] field, along with [Item].
I then need to pull from the same tblInvoices, any other records that contain the same [Item] as one that was queried in qryMicro.
I've tried to make a relationship between tblInvoices and qryMicro with [Item] but am getting too many records. I don't want to view records from qryMicro as I'm going to do a Union query to merge the two queries. I've tried
Field: Item Criteria tblInvoices.Item = qryMicro.Item w/o a relationship between the two but it's no better.
Any ideas as to how I can accomplish this?
Thanks,
Toni
The database I'm working on is used for personnel budget projections. Because some employees are hired mid-year, I need to be able to use various dates in my projection calculations.
I have 3 different tables - one with the employee start date, the other with the fiscal year start date, and the last with the start date of certain special pay tables. In order for my projections to work correctly, I'll need to return in a query the minimum of these 3 dates. I know how to do a minimum value in a single field within a table, but don't know how to select a minimum from multiple values in multiple tables. Is this possible.:rolleyes:
Im using Access 2000.
Essentially I want to find out which of the entries in my master table have matching entries in my other tables, and list the ones that do.
So if I have an entry in the master table for “productA”, and there are also matching entries for “productA” in tables “SupplierC” and “SupplierD”. I want to perform a query that will output a list showing “SupplierC” and “SupplierD” (I have A & B tables but if there isn’t an entry in them for “ProductA” I don’t want them on the list.)
I intend to use the results to populate the values of a combo box in future so I require the list to be in a single column, rather than across many columns. Does anyone know if this is possible?
My master table is called “OurProductsTable” and the four supplier tables are called “SupplierA”, “SupplierB”, “SupplierC”, and “SupplierD”.
Each Table has a primary key called “ProductID” and I have linked them together on the relationships screen.
I’m not sure if this is the proper method but I also made another field in each of the supplier tables called “CompanyName” and set the default value as the name of each supplier, so if the entry for supplierC matches the master table entry I can return the “CompanyName” value of “SupplierC”.
Here’s what I thought the code should kind of look like but I don’t know how to apply it properly in a query:
hi
i have a database to manage utility bill payment , it consist of
- Bills :
- billID
- Benificiary Name
-Cost Center
- Bills Transaction :
-TransID
-bILLid
-BillDate
-BillAmount
-Payment Transactions :
-PayTranID
-bILLid
-PayAmount
-PayDate
-BankRef
i made a union query from Bills Transaction and Payment Transactions to calculate bills balances which is : billid,sum(Bills Transaction.BillAmount)-sum(Payment Transactions.PayAmount)
all is working well , but the problem is i cannot find any relation between billtransaction
and billpayment ( per bill ) , cause i wish to payment details for each single bill transaction
the normal case is : bills issued as monthly basis but may fully or partially pay as the following cases :
- each bill transaction may fully pay one time
- in some cases : each bill transaction may fully pay but in multi settle
- multi bill transaction (per BILLID) may fully pay one time
IN CONCLUSION : each bill transaction should be stteled fully within one or two or maximum 3 months , say bill balance for each bILLID shall be zero.
how i could find a relation between this two transaction ( bills and payment ) to preview
payment information for each single bill transaction
exapmle :
billID : 39
BILL Transaction BillPayment BillsBalance
Bill Date - Amount PayDate- Amount
jan08 - 1000 1-1-2008 1000 0
feb08 -1200 5-2-2008 800 400
15-2-2008 400 0
mar08 1900 1900
apr08 1100 30-04-2008 3000 0
may08 1200 05-05-2008 900 300
jun08 1300 30-06-2008 1600 0
I have 3 tables, with the same field in each of the three tables. I need to find numbers (within those fields) that are similar in all 3 tables. If a number appears in all 3 tables, then all the data for that number need to be pulled from each table and placed in one row, all corresponding to that one number.
I can get this to work for 2 tables, using a query, but not three.
I am trying to find company matches between 2 tables. The issue I have is that the spellings on table A differs from table B.Some of the differences are minimal like "St. Annes" and "St Annes". And some really big "St. Annes" and "Annes, ST London".
View 1 Replies View RelatedI was using Access 2010 and just updated to 2013. My first use of a 2010 accdb file gave me this error "Could not find installable ISAM".My OP is Windows 7 Pro 64
View 14 Replies View RelatedI am trying to create a query and in the expression builder to find the max value from 3 different fields in 3 other queries.So each of the fields are called "TopSpeed" and the 3 queries are called "Test", "Training" & "Race".So in my new query I would like to return the MAX speed value from the 3 combined "TopSpeed" fields.Something like
MAX(DMAX([TopSpeed], [Test]), DMAX([TopSpeed], [Training]), DMAX([TopSpeed], [Race]))
Hi,
I have two tables of titles (DVDs and CDs). Each table has a price associated with each title in an adjacent column. I would like to match the titles between them and compare prices. Any help would be greatly appreciated. Thanks. EDS
Hi well as the title says i have a database with a LOT of tables, and i need to find the tables that contain a certain heading, eg reference 6, is there an easy way of doing this?
View 1 Replies View RelatedHey all
Im rather new to access but have to use it at work. Ive been asked to see if there is a way to delete duplicate records from a table.
Now, I have 2 tables. The 1st table (tbl_list) contains records of various customer details. This list is old.
The second table (tbl_new_list) contains new customer details.
We have found that we have the same customer details in tbl_new_list that are in the old table.
Im looking for a way to compare these 2 tables so that the values in tbl_list are not in tbl_new_list.
I have tried numerous methods using append queries but nothing seems to work.
Thanks in advance people
P.S the data will be compared through a telephone number.
I have several tables which have an indexed, no dup field. When inputting a entry that is not in the referenced table, how can I be taken directly to the input form for that field.
View 1 Replies View RelatedI have a main form that has fields from different tables. This is a research study, so each form enters data into different tables. Well, each record is a person's data. Instead of scrolling through each record, I need an option on the main form that allows me to search for a specific person and have their data populate into the form.So far I am able to make a combo box that pulls up the record from one table. Well, HOW do i do it from ALL the tables!?? Do I have to make a query?
View 3 Replies View RelatedEmployees submit information into a form which translates the information into a table. The table has been in use for years. By accident some employees were writing to an archived table while others were writing the the active table. This resulted in a field called "WorkID" being duplicated across the two tables.
Bottom line I am trying to write a query finding duplicates across tables but basing the duplicate only on certain columns.
I have a database with two tables, one for the amount that was estimated in each cost section, and one for the actual amount billed for each cost section. The tables have the same number of fields, all with the same names. They can be linked together with event ID. Each table has over 100 fields and I would like to find the difference between what was estimated and what the actual was for each event. I would also like to see which cost section has the most and least variance. I am trying to do this without going through each cost and putting [tEst].[CostName]-[tActual].[CostName].
View 2 Replies View RelatedWe are a non-profit that does blind mailings for our membership drive.
The company who we buy names and addresses from sends us a delimited file that has these fields as the headings
" ID, FULLNAME,COMPANY, ADR1, ADR2, CITY, STATE, ZIP, FIPS"
Once they send out the mailings, people then send in back a remit slip with a contribution that gets scanned through a program that creates a file that gives us these titles
"ALT ID, AMOUNT PAID, RUN DATE, TENDER, FUND, PURPOSE, SOLICITATION, MEMBERSHIP QUESTION, MEMBER TYPE, CONSTITUENT TYPE, SEGMENT". The "ALT ID" and "ID" are the same in both tables.
I need to find a way to merge the tables and combine the fields that have the same ID # , and then have it create a csv file that reads like this (see below) for only the files of the people that responded so that I can import it into our membership software.
"Alt ID","Title","First Name","Middle Name","Last Name","Suffix","Address1","City","State","ZIP","ct y_code","Amount Paid","Run Date","Tender","Fund","Purpose","Solicitation","Me mbership Question","Member Type","Constituent Type","Segment"
I have a report with 2 access tables (1 Master table and another a daily feed table)
The Master table keeps a log of all incoming records. (once append it to this table, should not show in future reporting)
The Daily feed information within the last 48 hours. (uploaded from an excel report into access temporary table)
When the daily feed table gets completed, I append the records and updated them into the Master to avoid duplication.
When I upload the daily feed table and I match it against the Master table to find duplicates, how can I delete the duplicates from the Daily Feed table?
This is my code to find duplicates:
SELECT CMPreport.ID, CMPreport.MbrName, tblMaster.ID
FROM CMPreport LEFT JOIN tblMaster ON CMPreport.ID = tblMaster.ID
WHERE (((tblMaster.ID) Is Not Null));
I am importing the updated Employee Roster information from Excel to a table called "Weekly Roster Check" (contains new info). I have a table called "Current Chit Board" that has an Employee Roster that I update every week (contains old info). I want a query to find employees who are promoted and their title has changed. then I want to run an update query to update these results to the Current Chit Board table.
Every method I try, returns either no information or information that is not what I want.
I am desinging a report, I cannot see some fileds in Sorting and grouping. The query that I am runing has those fieds, How can I get to see all the fileds in sorting and grouping?
Thanks
When opening a form i get asked for a parameter value for a field that has been deleted. it obvioulsy still exists somewhere but I cannot find it. i have analysed performance, all tables, forms and queries but it doesn't come up with this filed anywhere. the field name has even been spelt wrong so i thought it would be easy to track! Help - what else can i do to find where this parameter is being called from??
View 4 Replies View RelatedI'm trying to convince Access to find a value and then convert said value into the name of the field it came from.
Below is my current code:
Code:
LowestMark:
Minimum([AvgOfOpening1],[AvgOfConfidentiality1],[AvgOfEmail1],[AvgOfTenureStatement1],[AvgOfOwnership1],[AvgOfClearExplanation],[AvgOfProbing1],[AvgOfUseofTools1],[AvgOfResolveBeforeS2S1],[AvgOfBridgeSolutions],[AvgOfSummarizeWrapUp1],[AvgOfOfferFurtherAssistance1],[AvgOfOneFaceOneVoice1],[AvgOfAppreciateBrandCox1],[AvgOfInteractwithCustomer1])
(Minimum is from a module that finds the lowest value in the list.)
I need my query to tell me which field gives that value.
Example:
Agent Name AvgOfEmail1
instead of:
Agent Name 41.7%
how to make this work?
I have a table tROE with a field listing all dates starting 1/1/10 to date (populated), and three fields for currency exchange rates [USD], [RSD] & [EUR] which are empty and need to be populated. I have another table tROEPartial that has the exchange rates for some of the dates starting 1/1/10 but not all. Their structures are identical. I want to add the exchange rates from tROEPartial to tROE where the dates match, leaving the unmatched fields in tROE blank.
View 3 Replies View Related