Queries :: Linked Tables - Query From Different Table In One Column
Apr 5, 2013
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.
View Replies
ADVERTISEMENT
Nov 28, 2013
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 4 Replies
View Related
Jul 9, 2015
I 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.
View 3 Replies
View Related
Sep 13, 2006
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 Related
Oct 19, 2007
I have a linked table that I want to create some queries for. I would like to add an extra column to the table which is the sum of two existing columns in order to be able to use this in the query. So far, I've come completely unstuck. Any ideas please?
Thanks.
View 1 Replies
View Related
Jul 15, 2014
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 Related
Apr 10, 2015
So I have this If statement that goes something like this;
Code:
IIf {ttblEQAnswer_FCLIT.ActionType} = 'Answer Affir Defense' andnot isnull({ttblEQAnswer_FCLIT.METPrep}) and not isnull({ttblEQAnswer_FCLIT.AttyInstr}) and
({ttblEQAnswer_FCLIT.METPrep})> date(1900,1,1) and not isnull({ttblEQAnswer_FCLIT.AttyInstr})
and {ttblEQAnswer_FCLIT.METPrep} < currentdate - 10
then 'Y' as [MET InstructNeed?],
I want to use the above formula to calculate a column. However, do I need to join the tables referenced above in order to get this formula to work? As it stands I'm getting an error that says "Malformed GUID in query expression".
View 3 Replies
View Related
Jul 19, 2013
How to take the results of query and copy them to a column in a table?
I am using sharepoint and ms access together, and Sharpoint in this instance will not reference the result of the query. I can only use table fields with Sharepoint...major pain....
Example, I need riders name, weight and height from the column in ALL INfo query to automatically fill the column table in ALL Info
queryreservations!AllINfo.value=tablereservations! Allinfo.value
and how to loop through records that are not filled out already?
seems like it should be a simple Do.command but I am not sure how to code it..
View 1 Replies
View Related
Mar 8, 2014
i need to Concatenate a fields data into one cell in a query according to linked table ID....
View 3 Replies
View Related
Sep 24, 2013
I am trying to run a simple update query to copy data from one column (Addrl1)to another column (Working_Addrl1) within the same file and I can't for the life of me figure it out. Then I need to repeat for addrl2 and addrl3 to working_addrl2 and working_addrl3.
View 7 Replies
View Related
Mar 24, 2014
I have created a cross tab to extract pipeline and sales for Q1 2014, Q2 2014, Q3 2014 & Q4 2014... the user can select the quater from a multivalued text box...
Now for the final output, have created another query which pull the above four quarter in each column from the cross tab...now the problem arises when i change the quarter to Q2 2014, Q3 2014, Q4 2014 & Q1 2014..it gives an error "Microsoft office Access database does not recognizes "Query name" as a valid field name or expression".
The error is because the second layer of query does not identifies Q1 2014.
How do i make access change the column automatically when the Q1 changes to Q2...
View 1 Replies
View Related
May 21, 2015
I am looking to add a column in a query that will give a Y or No to previous column data if it contains TEXT or NUMBER (It could read "TEXT" or "NUMBER" or even Y for text or N for number).
View 3 Replies
View Related
Dec 1, 2005
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:
View 1 Replies
View Related
Jan 23, 2008
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 Related
May 28, 2015
I have a table for a multiple parents linked to a child table. I need to figure out a way to only allow 1 parent to be coded as primary, 1 as secondary, and then the rest as other... I thought about making Primary/Secondary/Other a primary key. But then I can only have 1 other. I would have to make a finite number of parents that could be entered and I want an infinite number.... My end goal is to have a report that only has a primary and second parent on it, but the rest of the parents still exist in the table...
*Child_ID
Parent_First_Name
*Primary/Secondary/Other
View 8 Replies
View Related
May 13, 2015
I have what is probably a stupid question but I've been struggling with this one for a while. I have an ordering database which has an Orders table (containing Order ID, Date, Supplier etc) and an Order Line table within which I have a combo box for Product name, supplier, price, VAT rate, Line price etc. At the moment, I have the order line table as a subform within the Order form (run from the Orders table). The problem I'm having is the subtotal and total fields. At the moment these are in the Order Line table as I cannot figure out how to get these in the Orders table. In summary, can I make a calculated field in one table that calculates values from another table (linked by Order ID)?
View 3 Replies
View Related
Nov 11, 2013
I have two table
1 is name master containing EMPID(Primary Key)
2 is Saving which also contain EMPID
i want to entered data in Saving with each and every EMPID with Master
View 1 Replies
View Related
Aug 13, 2015
In query, I would like to compare two tables and get the result but I always get the round robin result.
Table1
OrderQty Company Order# 8 Oop BBS 12 abc AAA 3 abc AAA
table 2
OrderQty Company Order# 7 Oop BBS 12 abc AAA 3 abc AAA
Result I get which I don't want What I want is the last table
Order2.OrderQty Order2.Company Order# Order1.OrderQty Order1.Company 3 abc AAA 3 abc 3 abc AAA 12 abc 7 Oop BBS 8 Oop 12 abc AAA 3 abc 12 abc AAA 12 abc
The result I need. How to get this?
Order2#Company Order# Order1#OrderQty Order1#Company 3 abc AAA 3 7 Oop BBS 8 12 abc AAA 12
View 4 Replies
View Related
Mar 17, 2006
Hi, All,
I have so far only worked with single tables and queries but not with linked tables. Now the following problem has arisen.
We have a database all of whose fields except two are of type = Text. Two fields are type = Memo.
We have to submit this database, i.e. its main table, to an external company for automatic 'cleansing' (UK: Telephone preference service). The cleansing program converts the cleansed database into a comma-delimited text file and returns it to us as such, and we then have to convert it back into an Access table.
(That's the way our supplier works, it is not under our control, and we have to learn to cope with it.)
Now the cleansing program is being screwed up by our Memo fields, because they contain commas. So when the cleansing program sees a comma, it interprets this as the start of a new field, and the same happens when we convert the text file back into a table.
Two solutions were suggested by the cleansing company, but only the following seems suffiently automatic and therefore acceptable to us.
I NEED HELP IN IMPLEMENTING THAT SOLUTION.
-----------------------------
I want to move the two memo columns into a separate table (memo table), linked to the original table (main table). Have the ID column (unique identifier) in both tables to keep the records together. Then I send only the main table for cleansing and the commas in the memo can no longer work havoc.
I have an idea of how to create the memo table: make a copy of the main table and then delete all columns except ID and the two memo columns.
But then I have to link the two tables (I do not know how to do that); where do I start.
I must also ensure that when new records are created (now usually through a form), both tables are expanded. And I must get information from both tables into one form.
At present I do not even know where to start, except for creating the Memo table by copying it and deleting certain columns.
I have a good book (John Viescas: Running MS Access 2000), but it is huge, and the problem is urgent (it stops our tiny company from marketing). I have no time to study the whole book to find what I need in this case. A chapter or page reference would be very useful.
Or some Tutorial on the web that deals with my particular task.
Thanks for your help.
Adrian
View 4 Replies
View Related
Sep 2, 2007
Dear All
Since last week I am working on a Database which I will use for my company. To make a long story short, I didn't find any appropiate softwares nor examples on the internet, hence, with my some knowledge in Access, I am trying to make this "to be" useful tool for my self.
Short brief on the project:
This is going to be used for a trading company to register:
- Suppliers
-- Products
- Customers
-- Inquiries
-- Orders
-- Offers
*Relations:
- Suppliers can have one or many products
- Customers can have one or many Orders / Inquiries / Offers
Problems:
(I have tried many different ways, but going nowhere)
1) The "Customers" table have 3 tables (Orders/Inquiries/Offers) linked to it - and all are based on the primary key "CustomerId". --> How can I make a form to enter these data linked?
2) Under "Orders" I want to select supplier from the Supplier table and Product from the Product table -- How?
I know this is a lot of info and probably too vague, but if someone could assist me with a few problems - then I would really really appreciate it and compensate somehow.
Looking forward for your help.
Best regards
View 1 Replies
View Related
Dec 3, 2013
I am looking to copy one exceptionally large table to another. I can use VBA to copy the entire Report2013.accdb to Report.accdb while compacting at the same time. This is very fast and works well. However once it's copied I need to rename the table tblReport2013 to tblReport. None of these table are in the accdb that I am running the scripts from FrontEnd.accdb. I am working very hard to keep the data tables under the 2 GB limit, which is why linking and keeping the data separate is necessary. There is the chance that Report2013 will be on the network while tblReport will be local to the user, and may or may not be a subset of the Report2013.
I have considered a number of options:
DoCmd.Rename - I can't seem to tell it a database location
I could try
docmd.copyobject - and see if it keeps the file size the same or smaller then the original.
I could just write the sql statement and copy the data that way, and compact the table afterwards. The trouble with doing the transfer that way is hitting the 2GB limit, before I can compact/compress the newly imported data.It just seems odd to have to find another solution when all I really want to to rename a table and ensure the new new is still linked as a separate table.
View 1 Replies
View Related
Sep 17, 2013
I have multiple tables that are linked to excel. I am creating a product selection tool to make it easy to find the products contained all these linked tables. They are necessary as they contain pricing data and information necessary to be kept in excel which is regularly updated.
Is there a way to create one table containing data from all these linked tables? I tried using an append query but realise that when the linked tables are updated the table containing all the data wont be?
View 3 Replies
View Related
Sep 29, 2013
How can I update (some columns) in a table from the same table based on a Criteria column in the same table.
View 2 Replies
View Related
Nov 20, 2004
Hi, I have the following structure:
Products 1-M ORDER DETAILS M-1 ORDERS M-1 CUSTOMERS
I have ORDER DETAILS set as a junction table so that many products can be recorded within one order. All is good apart from when i go into ORDERS and create a new order. I click the subform which links to the ORDERDETAILS. I then pick a product number(look up from products table). The problem is this: In the ORDER DETAILS I want to display the unit price of this product simply by picking the product id.
Eventually this would form the basis of an order form where I can pick Product Id and have it display unit price.
Any ideas on this one, I'm sure its quite simple!!
Thanks in advance
View 1 Replies
View Related
Mar 5, 2014
My table (excel spreadsheet) sits on another directory from the DB. If I delete the table on this directory and immediately replace it with a table with the same filename, format, etc (only the data has changed) will each database user have to relink the table on their desktop DB? Or will the forms/subforms/reports still maintain their relationships/functions and just display the new data?
View 2 Replies
View Related
Apr 16, 2013
Background: In my Access FE, I have created a "linked table" to a file on our AS400 database. I know I can check the MSysObjects table for the linked table name, but sometimes the file actually does not exist on the AS400.
The file can be in one of three states.File exists with data.
File exists, but is empty.
File doesn't exist.
Question: What is the best way to determine the status of this linked table (file)?
View 1 Replies
View Related