Can we have two tables linked to one search query? I've some data that is confidential and woul like to have them in two different table. When I search for it using query form, only the relevant table results will be shown?
View RepliesI have two linked tables (Unique_ID)
Table1 has a record of unique tasks.
Table2 has a list of people to do those tasks
They are linked by a Unique_ID (one to many - Table1 to Table2).Sometimes there can be an instance where there are Unique_ID's in Table2 but the reference Unique_ID is missing from Table1.
I have a select query that can show me these like so.
table2 table1
Unique_ID Unique_ID
Like * Not Like *
I can open the query and simply delete the records fine however i wish to automate this.What I want to do is delete the unrefenced Unique_ID's from table2.I have tried to just convert this select query into a delete query but it asks me which table I wish to delete and I cannot see where to enter this information.
I have two tables,
Table A:
Year Type Count
1992 Walk 82
1992 Bike 43
1992 Vechicle 298
1992 Ship 3
1992 flight 3478
1993 Walk 47
1993 Bike 13
1993 Vechicle 638
1993 Ship 24
1993 flight 3111
1994 Walk 0
1994 Bike 8
1994 Vechicle 1298
1994 Ship 24
1994 flight 3234
Table B:
ID Type
T1 Walk
T2 Bike
T3 Vechicle
T4 Ship
T5 flight
The two table linked by the [type]
I need a query expression like: SlowWay: IIf([TableB].[ID]= 'T1' AND [TableB].[ID]= 'T2',[TableA].[Count])
FastWay: IIf([TableB].[ID]= 'T3' AND [TableB].[ID]= 'T4' AND [TableB].[ID]= 'T5',[TableA].[Count])
My expression keep gives me the error.
Is it at all possible to search across multiple tables with one query. I have a database with about 15 tables and my users would like to be able to search for text across all the fields in all the tables. I have a simple find record command on each table, but in case they are not sure which table to search i would like to offer a search function which in theory searches the whole database.
I have 15 tables (eg witnesses/policies/activities etc), each with different fields (eg name, address/ policy decision, policy reason/ activity undertaken, activity person etc etc) . In total i would like to be able to search about 50 fields. All i need returned from the search is the record ID from the relevant table, or anything to simply identify the record & relevant table. And then they could go to the correct place to look the record up.
I have been looking for an answer for a while. I have seen queries where you enter the parameters against each field going down a row each time for criteria being "OR" each time, but there are a limited number of rows on a query design so i run out of rows.
Hello,
I have been searching the forum for this scenario but could not find similar case, which is as follows:
I have a Search Form with many Text and Combo boxes, linked to search criteria in a query. There is also a sub-form showing the search results.
I am trying to replace one of the combo boxes on the form, with a List Box, so I can make multiple selections. For example instead of showing only "buy" transactions via the combo, I want to see both "buy" and "sell" transactions, which I believe could be done via a List Box.
For the combo box I have the following:
In the Field section I have:
[TransactionType]=[Forms]![frmSearchTransHist]![cboTransType] Or [Forms]![frmSearchTransHist]![cboTransType] Is Null
For Criteria I have - "True"
So, my question is what should I put on the query in order to make it work with a list box.
I also want to give credit here to Jon K who has an example of database with combos and texts - very helpful example!
http://www.access-programmers.co.uk/forums/showthread.php?t=103312
Any help is greatly appreciated!
Rgds,
Atol
I have a query linked to some combo/txt boxes on a form so the user defines which categories he/she is looking for then presses button and query comes up with relevant results. This works fine if the user inputs information that is actually there, but if they leave it blank it goes all wrong.
The statements I have in the query are as follows:
[Forms]![frmStockControlSigned]![comboSupplier]
[Forms]![frmStockControlSigned]![txtPurchaseOrder]
etc.
I thought the solution may be along the lines of this but it does not work:
=if([Forms]![frmStockControlSigned]![txtDate]=Null,[Forms]![frmStockControlSigned]![txtDate],"*")
basically i want to make it so the user does not have to enter criteria into evey box, thus if every search box is left blank it would show all records "*"
Right what im after sounds complicated but its quite simple.
I have a client database, in this database each client has 3 skill fields, i also have a form with a listbox and an unbound textboxes that are linked to the query so when the textbox is typed in the listbox updates.
Now ive sorted out the problem of not all fields showing it some of them are blank now the problem is.
When input text into one of the boxes the listupdates, now if theres text in two boxes. and theres data in one of each that matches it will show neither, because the query is looking for data that matches in both fields, now i need partial matches so it will give results of data in both fields.
not understanding fully what i mean?
i have zipped my db, if you open the db then open the form 'MainListRecTrades2nd' and fill in data in both primary and secondary trades that matches from the list you will see.
http://awilderbeast.com/CWSystem.zip
The solution i was thinking of would to have 1 textbox that searches all the fields and displays data from any of the fields in the list
Thanks for any help
I am new'ish to access, although we have had an SQL server 2000 backend for some time, it has purely been used for our MIS systems, now I want to develop using Access 2003 as the front end, and SQL as the backend, however, I don't know the most effective way to link to the data on SQL, I can use either linked tables through Access, or not use linked tables and just send SQL queries to the server using vba code, I am not sure which would be the best solution to creating a new application.
Any ideas whether I should use linked tables or fire off vba SQL queries ?
cheers
John.
:confused:
I have set up a table (A) that is linked to another table(B) in my database. This linked table (A) is then used in a number of queries. When the data in the original table (A) is updated it does update the data in the linked table (B) as it should. However, when I run the queries they do not bring up any records. It seems that they are not looking at an updated version of the linked table (A) because if I rebuild the query it finds the records as it should. Any advice on this would be very gratefully received.
View 1 Replies View RelatedI have a search form that uses a query to show results of a search, but everytime I press search everything comes up even though I have entered search parameters, even though my search requeries every time and the search used to work before I added new records today. Also when I press the query alone on the navigation pane it asks me for the parameters and then it actually works but it won't when I use my form.
View 2 Replies View RelatedI'd like to secure my frontend by disabling access to the linked tables, queries etc. I thought converting to .accde would do this but apparently not. Is there a way that this can be achieved?
I am aware that I can disbale the ribbons etc but they can all be turned back on using the options menu.
Hi,
I have an experience for running queries.
When I use query to run the linked tables. It is faster.
If I use query to run the tables (not linked), it is slowly.
I did both ways.
Can anybody tell me why running the linked tables by queries is faster than the no linked tables?
Thanks.
I am trying to create a query based on two tables with a many to many relationship.
The basic sketch of this relationship is one table has our employee information and the other has the phone information. Employees share some phone numbers so it has to be many to many and I have an employee_phone linking table.
When I create a query, I have no records in my phone or my employee_phone tables, but my employee table has a full list of employees. I want to use the query to enter the phone numbers in for existing employees, however when I put in the employee's name and then add the phone information, it creates a new record in the employee field. I just want it to tie new phone information to existing employee records.
If anyone can help me with this, I would be very greatful! Thanks
I have a database built in Access 2000. It consists of the following tables:
45 tables each containing the same fields - ID, Vehicle, Fit, Part No, Qty, Level, Higher Assembly.
1 table containing the fields - ID, Part Number, Description, Issue No.
The 45 tables are each for a different vehicle build of materials, whilst the single table contains the additional common data that each vehicle form pulls in when the build of materials part number is added.
I want to be able to undertake a search across the 45 vehicle tables to look for a specific part number that I enter in the search box, and then if any is found, for all those vehicle to be displayed, along with the part number, description and issue number from the single table.
Please could you advise me if tables have to be linked in order to perform a query on multiple tables? For example searching for a date and matching records on more than one table at the same time.
View 1 Replies View RelatedI have a number of linked tables that vary depending on use. How to delete them or drop them all from the database? I produced/stole some code that deleted all tables - but it didn't delete the linked ones.
View 3 Replies View RelatedI have a database with a number of linked tables that are linked to tables in different databases (not a back-end).for example, I have table1 that is linked to table1 in K:databasedb1.mdb.table2 linked to table2 in S:datadata.mdb.and so on...
However, recently we have moved all our databases to a new location.
K:databasedb1.mdb is now residing in O:masterdatabase
and S:datadata.mdb is now residing in O:masterdata
and so on...
I'm now in charge of relinking all those tables to point to the new location.I would do this in linked table manager one by one but we have 100s of tables linked to multiple different databases in different location.is there a way to create a VBA code that will automatically do this re-linking process?
so,
1. find unlinkable tables
2. search its new location under O:master
3. re-link it to the new location
Database names and tables names have not been changed. Just the location of databases.
I have three tables, tblPeople, tblAddresses and tblVehicles. I have a form called frmPeople that shows a single person record, and has subforms with the list of addresses and vehicles for that person (each record has several). I want to create a single search form with each of the fields in my tblPeople, tblAddresses and tblVehicle tables, and allow the user to type anything into any of the fields on the search form, and have the search form fetch records that match.
So far, I have the query built, and the tables all linked the right way (I think), and I have all the search fields coded. And it works GREAT... except for this one little issue: If I do a search, and I put "Smith" in the last name field, I will get several Smiths in my search result, one for each address and vehicle combination. In other words, if I have ONE Smith in the table, and two vehicles and two addresses for that Smith, the query results give me FOUR Smiths! One with Vehicle1 and Address1, one with Vehicle2 and Address1, one with Vehicle1 and Address2, and one with Vehicle2 and Address2.
What I would LIKE is to have ONE record in the query results, and that record shows me that one Smith. And if I then search for "Plymouth" in the VehicleType field, and I have Smith in the name field, I'll get ONE record, and it will be the one of Smith with his Plymouth (Vehicle2).
Here is how my query looks right now (this one ONLY has the last name and vehicle search function to save space), and this is the one that gives me multiple results (which I do NOT want, unless multiple PEOPLE match the search criteria):
Code:
SELECT DISTINCTROW tblPeople.LastName, tblPeople.FirstName, tblPeople.PersonID, tblAddresses.Address, tblVehicles.Vehicle, tblVehicles.Plate, tblVehicles.VehicleYear
FROM tblPeople LEFT JOIN tblAddresses ON tblPeople.PersonID = tblAddresses.PersonID
WHERE (((tblPeople.LastName) Like "*" & [NameField] & "*")
AND ((tblVehicles.Vehicle) Like "*" & [VehicleField]));
Did I write the query wrong?
Hi everybody. I got a query that displays object name and object type of access 2000 db. Unfortuenly it dose not display the linked tables objects(linked to tables in acccess 2000 db). could an expert tell me how i can fix this query so it displays linked tables object as well.Thanks
SELECT MsysObjects.Name AS ObjectName, IIf([type]=1 Or [type]=6,"Table","Query") AS ObjectType
FROM MsysObjects
WHERE (((Left$([Name],1))<>"~") AND ((Left$([Name],4))<>"Msys") AND ((MsysObjects.Type)=1 Or (MsysObjects.Type)=5 Or (MsysObjects.Type)=6) AND ((MsysObjects.Flags)=2097152 Or (MsysObjects.Flags)=128 Or (MsysObjects.Flags)=0 Or (MsysObjects.Flags)=16))
ORDER BY MsysObjects.Name;
Hi everybody. I got a query that displays object name and object type of access 2000 db. Unfortuenly it dose not display the linked tables objects(linked to tables in acccess 2000 db). could an expert tell me how i can fix this query so it displays linked tables object as well.Thanks
Code:SELECT MsysObjects.Name AS ObjectName, IIf([type]=1 Or [type]=6,"Table","Query") AS ObjectTypeFROM MsysObjectsWHERE (((Left$([Name],1))<>"~") AND ((Left$([Name],4))<>"Msys") AND ((MsysObjects.Type)=1 Or (MsysObjects.Type)=5 Or (MsysObjects.Type)=6) AND ((MsysObjects.Flags)=2097152 Or (MsysObjects.Flags)=128 Or (MsysObjects.Flags)=0 Or (MsysObjects.Flags)=16))ORDER BY MsysObjects.Name;
I have a delete query where i want to delete only the row that contains the max value of the IDnum field from the table STM, where it links two tables on CellTell
I currently have this:
DELETE DISTINCTROW STM.*, STM.IDNum
FROM dpl_00c_tbl_StmCellDups
INNER JOIN STM ON dpl_00c_tbl_StmCellDups.STM_CellTel = STM.CellTel
WHERE (((STM.IDNum)=(select max(IDNum) from `STM`)));
It doesn't want to throw out any values when i run it, or view it.
I have a make table query which creates the new table on the local front end.I have now split the DB, so need to know how I can get this query to make the table on the BE i.e. how do i link it.
View 14 Replies View RelatedMy report produces multiple copies of the same record. I know why, but don't know how to fix it.
EmployeeTable.
With a one to many relationship with TrainingTable (via employee PK as FK in trainingtable).
Training table has a one to many relationship with a table called Range.
Report is based on a query that picks up the Employee/Training/Range (range just describes the training unit).
However, If I have more than one range expressed organized a training unit, the report spits out several copies of the Employee record to display all the ranges.
Hi all,Desperate for help on this.The query below ran like a charm for this years database which was full of entries, but when I did a quick test run for next year with limited entries the 'Temp' table fields were all blanked. All the individual queries, 1 through 9, work fine on their own and work fine in this linked form when there is enough data in the database to cover all the fields. Apparently if only one query is blank all the fields will be blank.For instance if there are multiple registered households, table ‘1’ would normally show a count of those households, but if none of those households have children, table '2' which counts children will be blank and in turn blank out all the rest of the fields including table ‘1’.I've tried the ‘is null, '=0" and ‘nz’ routines on the Count(Tablename.Fieldname)'s, but can't seem ‘to get them to output a value of 0 in the null fields. I must be doing something wrong. Any and all help would be very much appreciated.SELECT Year(Now()) AS ProjectYear, [1].CountOfClaimID AS Households, [2].CountOfPersonID AS Children, [3].CountOfPersonID AS Participants, [4].CountOfSponsorID AS SponsorsTotal, [5].CountOfHouseholdID AS Sponsored,[6].FamiliesWithChildren, [7].SponsoredFood, [8].SponsoredGifts, [9].SDSD INTO TempFROM (SELECT Count(Household.ClaimID) AS CountOfClaimID FROM Household GROUP BY Household.RegStatus HAVING (((Household.RegStatus)=-1))) AS 1, (SELECT Count(Members.PersonID) AS CountOfPersonID FROM Household INNER JOIN Members ON Household.HouseholdID = Members.HouseholdID GROUP BY Household.RegStatus, Members.Status HAVING (((Household.RegStatus)=-1) AND ((Members.Status) = "Child" ))) AS 2, (SELECT Count(Members.PersonID) AS CountOfPersonID FROM Household INNER JOIN Members ON Household.HouseholdID = Members.HouseholdID GROUP BY Household.RegStatus HAVING (((Household.RegStatus)=-1))) AS 3, (SELECT Count(Sponsors.SponsorID) AS CountOfSponsorID FROM Sponsors GROUP BY Sponsors.SponsorStatus HAVING (((Sponsors.SponsorStatus)=-1))) AS 4, (SELECT Count(Sponsorship.HouseholdID) AS CountOfHouseholdID FROM Sponsors INNER JOIN Sponsorship ON Sponsors.SponsorID = Sponsorship.SponsorID GROUP BY Sponsors.SponsorStatus, Sponsorship.SponsorshipYear HAVING (((Sponsors.SponsorStatus)=-1) AND ((Sponsorship.SponsorshipYear)=Year(Now())))) AS 5,(SELECT Count([Table0].HouseholdID) AS FamiliesWithChildrenFROM (SELECT Households_All.HouseholdID, IIf([Children]>0,1,2) AS FamChildrenFROM Households_AllGROUP BY Households_All.HouseholdID, Households_All.ChildrenHAVING (((IIf([Children]>0,1,2))=1))) as Table0) AS 6,(SELECT DISTINCT Count(Household.HouseholdID) AS SponsoredFoodFROM Household INNER JOIN Sponsorship ON Household.HouseholdID = Sponsorship.HouseholdIDGROUP BY Household.RegStatus, Sponsorship.SponsorshipYear, Sponsorship.FoodHAVING (((Household.RegStatus)=-1) AND ((Sponsorship.SponsorshipYear)=Year(Now())) AND ((Sponsorship.Food)=-1))) AS 7,(SELECT DISTINCT Count([Household].[HouseholdID]) AS SponsoredGiftsFROM (Household INNER JOIN Sponsorship ON [Household].[HouseholdID]=[Sponsorship].[HouseholdID]) INNER JOIN (SELECT Households_All.HouseholdID, IIf([Children]>0,1,2) AS FamChildrenFROM Households_AllGROUP BY Households_All.HouseholdID, Households_All.ChildrenHAVING (((IIf([Children]>0,1,2))=1))) as Table0 ON [Household].[HouseholdID]=[Table0].[HouseholdID]GROUP BY [Household].[RegStatus], [Sponsorship].[SponsorshipYear], [Sponsorship].[Gifts]HAVING (((Household.RegStatus)=-1) AND ((Sponsorship.SponsorshipYear)=Year(Now())) AND ((Sponsorship.Gifts)=-1))) as 8,(SELECT DISTINCT Count(Household.HouseholdID) AS SDSDFROM HouseholdGROUP BY Household.RegStatus, Household.SDSDHAVING (((Household.RegStatus)=-1) AND ((Household.SDSD)=-1))) as 9; Thanks much,aldo
View 2 Replies View RelatedI have a query that populates a report linked to a mail merge. There is an IFF statement in it that checks to see if a condition is meet. If that condition is meet some standard text results. Is it possible to put a disclaimer with the standard text.For example,
Code:
IIF ([Results]="Negative","Nothing detected" (the disclamer would go here)
I am not certain the correct syntax to use to insert "not all regions were tested".
i need to Concatenate a fields data into one cell in a query according to linked table ID....
View 3 Replies View Related