Query From 2 Tables

May 10, 2005

I have two tables with a one to many relationship but not every record has a match on the many side. When I run a query containing both tables and tell the relationship to use all records from the one side and only the records that match from the many side it returns correct execpt when the 1 side has a match only the matches display not the original record from the 1 side.

Table1
ID 1
ID 2

Table2
ID 2 SubID1
ID 2 SubID2

Query display wanted
ID 1
ID 2
ID 2 SubID 1
ID 2 SubID 2

My main result is a report.

Thanks

View Replies


ADVERTISEMENT

Update Query Using 3 Tables: Source, Joining, Destination Tables

Apr 19, 2007

I have some experience doing 'Update Query' using two different tables but I'm having a hard time doing an 'Update Query' using 3 tables.

I have my source table TP05XY with the fields 'Mark' 'Date' 'UTM_Edig' and 'UTM_Ndig'. Mark and Date are my primary keys (they together uniquely ID each record). I have my Observations table with the fields 'Mark' 'Date' and 'Obs_ID'. The last table is Locations with 'Obs_ID' 'UTM_E' and 'UTM_N'.

I want to update my fields UTM_E and UTM_N from UTM_Edig and UTM_Ndig. However, to do so, I have to go from my TP05XY table, through Observations table to update Locations table. Table TP05XY is joined to Observations through 'Mark' and 'Date' and Observations table is linked to Locations through 'Obs_ID' field.

I have tried a few options without success ... anyone knows how to do it?

Thanks,

Josée

View 1 Replies View Related

Tables :: Access 2010 SQL Statement With Tables And Query

Sep 3, 2014

I am using Access 2010.I have a table that I am using to pull my data from other tables and a query.My table is called tblMyData.One of the field names is level1. This field points to another table, and gives the user the choices for states (examples California, Texas, Maine).

Another field name is level2. This field points to another table and give the user the choice for type of customer (examples Business, Consumer)
The field name level3 points to a query. The query, qryFinalChoice matches up the choices based on level1 and level2.for example, the user can pick California for level1, business in level2 and California Widgets or Los Angeles Clothing store in level3 (plus about 20 other choices).

If the user picks Texas for level1, business in level2, business in level2 and Houston rugs, or Texas style restaurant in level3 (or about 15 other choices).

-I am recording 1 for California on level1, 2 for Texas and 3 for Maine in level1.
-I am recording 1 for business and 2 for consumer on level2.

The query qryFinalChoice has all the combinations for state, business or consumer, and lastly business name or consumer name.

-qryFinalChoice has line1 to match up the choices for level1 in my table.
-qryFinalChoice has line2 to match up the choices for level2 in my table.
-qryFinalChoice has line3 to match up the choices for level3 in my table.

I do not want any of the Texas business names appearing when the user picks California, or vice versa.

My SQL in my tblMyData tab for level3 looks like this:
Select line1 from qryFinalChoice where line1=1;

I am able to get all the line items where California is a selection.How do I change my SQL to pull all the line1 choices where I have selected from level1, and all the line2 choices where I have selected from level2 automatically based on my pulldowns?

View 1 Replies View Related

Tables :: Storing Query Data In Temp Tables

Dec 15, 2014

I'm looking into storing query data in temp tables for my reports run better. From what I'm reading, it seems best to have the temp tables in a separate db, and to break the links to avoid bloating of the FE database. I'm unsure how to do this with VBA, especially since my temp database will be password protected. When do I break the link - when I close the FE database?

View 14 Replies View Related

Tables :: Convert Multi Tables In One - Not A Query

Sep 21, 2012

I am using collect data via email process to collect data by email, purpose is to update not to add new records but to update. The condition for update is to have data from one table only.

I have 4 tables data from which should be sent by email. These tables are related. I made a query based on tables and query is update-able. When I use this query, wizard does not give option of updating the data but only of adding new records.

I tried to first make a make table from a make table query but that too have the same result.

Any ways to make treat these tables in a single table? Almost all fields except one shall be just to read and one field shall be updated.

View 5 Replies View Related

Tables :: One Query From 2 Tables Within Same Database

Jul 29, 2013

I have one database that I am working on and within it there are several Tables. 2 of the tables have similar information on various organisations and I need to query these 2 tables to get a simple mailing list of ALL organisation addresses and the main incumbent there.

Within Access (2007) and in the SQL View window, I have made a simple query to give me a list of all the organisations and incumbents but cannot get it to work correctly. Is there an easy way to get this information out of the 2 tables? I realise that the SQL in SQL View has to be formatted differently. I have also tried the Microsoft Query Wizard but that doesn't work either as it gives in a side by side list which is useless to me.

The Field names that are required are as follows:

<Incumbent>, <Organisation>, <Address1>, <Address2>, <Address3>, <County>, <Postcode>,

View 8 Replies View Related

Forms :: Update Form / Query Without Updating Underlying Tables To Query

Jul 23, 2015

I have a form which will be used as the basis to print a label.

It is bound to a query and when I open the form I pass over a 'where' condition to return 1 record. I then use the query to produce a report/label.

What I want to do is to update the form/query without updating the underlying tables to the query.

View 14 Replies View Related

Append Query, Multiple Tables To Multiple Tables In Another Database

Nov 29, 2007

Can a Append Query move all my data stored in multiple tables to another database with a identical table structure?

Because as I try to work the query, I keep getting prompted to "Select a table" I want to append to, and I don't want to append to just one table...

View 4 Replies View Related

Different Tables Query

Oct 12, 2005

Hi to all, as you can see I'm brand new in access. I just need some advise on how you can probably do this:

I have a directory with tables for every year scince 1984 to 2004 where the structure is the same, so now I want to have a form where I can put the last name of somebody, so access can pull every table and tell me where did it find a similar result.

I hope you can help me,

Regards,

View 1 Replies View Related

One Query Two Tables

Oct 26, 2007

I want to create one query from two tables. When I run the query with two table the data sheet is blank. How do I combine two tables so when I run one query all records appear from both tables?

View 5 Replies View Related

Query For 4 Tables

Nov 16, 2005

Hello again everyone:

I have a new problem! I have four dbf files imported as tables. Each table has the same fields. One of the Fields in the tables is Parts. What I want to find if possible is the following:

1. I want to see all unique parts in these tables. If table 1, 2, 3, & 4 all have part 1 I only want to see it one time. I guess what I am saying is I really only want to see each part one time even if it is in all 4 tables, 3 tables, 2 tables or 1 table. Can this be done? I hope you can understand this request and thanks for the help!

View 1 Replies View Related

Query With Many Tables

Dec 1, 2005

I have these five tables in a DB. I have a query for each table to append that table. I will run these queries the first of each month and have the tables show the counts. In this case I ran the queries on 11/29 and again on 12/1. I want to show the change in the counts from month to month.

CLIENTS DATE
10 11/01/2005
15 12/01/2005

STATES DATE
15 11/01/2005
15 12/01/2005

EFT DATE
12 11/01/2005
14 12/01/2005

NCP DATE
2 11/01/2005
5 12/01/2005

EMPLOYER DATE
15 11/01/2005
18 12/01/2005

Now I want to create a table query to combine the results from each table and date so that I may create a report from that table. The table should look something like this.

DATE CLIENTS STATES EFT NCP EMPLOYER
11/01/2005101512215
12/01/2005151514518

This is the query I’ve tried with no luck as this query comes up with 32 lines in the table when there should only be 2.
SELECT
[CLIENT ].DATE,
[CLIENT ].[CLIENTS],
[STATES].DATE,
[STATES].[STATES],
[EFT].DATE,
[EFT].[ EFT],
[ NCP].DATE,
[ NCP].[ NCPS],
[ EMPLOYER ].DATE,
[ EMPLOYER].[ EMPLOYER ]
FROM
[CLIENT], [STATES], [ EFT], [ NCP], [ EMPLOYER];

Can someone show me how to correct this query to get the results I’m looking for. Thanks

View 8 Replies View Related

Query From 2 Tables

Dec 18, 2005

I'm working on a maintenance system and sometimes, machine operators fill in MS Access forms with Machine Number or Location No or BOTH whenever a machine needs repairs.

Now, whenever I generate a report, only those entries containing an Asset No comes out on the report.

What I would like to do is to generate a report that shows primarily the Asset No and in instances where the Asset No is not recorded down, the Location No shows instead.

How do I do this on the query table?

View 1 Replies View Related

Is It Possible To Use The Same Query On Different The Same But Different Tables

Apr 19, 2007

I want to add excel spreadsheets to the tables and then use a query to filter them out

Can I make one query and apply them to the different tables?

Thanks

View 2 Replies View Related

Query From 2 Tables

Mar 5, 2008

I have a set of data in one table with another set of data in a seperate table, both tables have been linked so I have a MAX and MIN set of values -this works fine however I now wish to work out the difference ie;
MAX - MIN = ? but I now get an error as the data is in 2 different reference tables which are set by an external source so I have to use both tables linked by a common thread, I use the Date - any ideas how I can resolve this issue

Regards

Richard G

View 1 Replies View Related

Query Using 2 Tables

Feb 15, 2005

I have a query using 2 tables. However the second table is not always required and I notice that if the second table is not used then the info from just the one table is not included in the response. How can I rectify this problem?

View 1 Replies View Related

Query From Unlinked Tables

Dec 28, 2006

I have four tables....that do no require relationships.

The four tables have different fields, but have one field in common, the Name.

I want to create a query with all the Names from the tables.

How?!

View 1 Replies View Related

Multiple Tables And 1 Query

Apr 24, 2005

Hello all:

I have multiple tables in my access db, the structure of the tables (4 in all) are exactly the same except that the billing dates are for year 2001 to 2004 i.e. separate table for each of the years.

I don't want to copy the information in these 4 tables into 1 table because I have over 2 million rows in each table. However, I want to be able to run 1 query that will provide information from all 4 tables. E.g. filtering for billing dates between 2001 to 2004 for a particular customer.

How do I join the tables together so that I can run a query on all 4 tables at one time.

Thanks for your input.

Odun

View 2 Replies View Related

Tables/query Problem

Feb 27, 2006

Hi all,
I'm a newbie in access and i'm trying to make a small application in access that will have the following functionality:

- The user of the app can add new 'test flows' which can include 'test cases' and 'actions'.

- 'test cases' can include 'test cases' and 'actions'

- 'actions' are atomic units and include lines of code (text)

- The app should have a small query that can display a report with all the "Fathers" of a specific 'action' or 'test case'.

I need to build this tables somehow and not sure how, espcially because the 'test flow' table can include unknown number of 'test cases' and 'actions'.

Can someone please send me a .mdb file with this requirements implemented inside it and i'll analyze it and learn from it ?

View 14 Replies View Related

2 Tables + 1 Query = 1 Headache

Jul 28, 2006

I am trying build what I thought was a super simple data base, but this is driving me crazy.

I have a group of building maintenance manuals (12) each of which is subdivided into sections. I have created a list of all the section headings and respective page numbers and have imported them into a table (tblMASTER ) with these fields:

MasterIndexpkf
SectionNumber (unique sequential number 1-311)
SectionName
PageNumber

I have a second table (tblCROSSREF) with these fields:

CrossRefIndexpkf
MasterIndexfkf
SectionNumber
SectionName
PageNumber

The two tables are linked in a one-to-many relationship on the respective MasterIndex fields.

I also have a query (qryMaster) which includes ALL the fields in tblMaster which serves as a lookup to the SectionName field in tblCROSSREF.


What I want to do is to select a section number in tblMaster and enter all the possible cross references in tblCROSSREF by selecting the available section names from qryMaster.

This seems to me to be sooo simple, but I am having a real headache!

My primary problem is that when I use the lookup function in the SectionName field (in tblCROSSREF) it returns not the Section Name, but the Primary Key Field number.

This thing is making me feel like the AFLAC duck. Can anyone give me a clue about where I have gone wrong?

Thanks,

Rick

View 4 Replies View Related

In A Query - A Way To Merge 2 Tables Into One?

Nov 21, 2007

Hi all. Is there some way to merge 2 tables into one in a query? Is it possible? Thank u.

View 7 Replies View Related

A Query To Add Records Into 2 Tables

Dec 11, 2005

Hi all,

I'd like to know if it's possible, with MS Access to use a query that adds records from 2 tables into 2 other tables.

Thank by advance :)

Twinpath.

View 1 Replies View Related

Update Query-two Tables

Dec 20, 2005

Does anyone know how to use an update query however, rather than update the field with a specific value, the field is updated from another tables value of the same field name?

This is the sql I attempted to use however, when I execute the statement i get a enter parameter value when it gets to the Where statement?

Any advise would be greatly appreciated, I'm lost!

Update PendingPolicies
Set PendingPolicies.PolicyStatus = PolicyTable.PolicyStatus
Where PendingPolicies.PolicyNumber=PolicyTable.PolicyNum ber----Iknow there appears to be a space here but there is not in my SQL?

View 2 Replies View Related

Complex Query On 3 Tables

Feb 15, 2006

I need to create a query based on the following 3 tables.

table PA0001 has the following entries (first line is the header data)

EmployeeNumber - ContractType0001
1234 - 1
1235 - 2
1236 - 1
1237 - 1
1238 - 2
1239 - 3
1240 - 2

PA0016 has the following entries:

EmployeeNumber - ContractType0016
1234 - 11
1235 - 12
1236 - 13
1237 - 11
1238 - 11
1239 - 13
1240 - 12

There is a mapping table called, lets say tblMappingTable which has the following fields:

Contract0001 - Contract0016
1 - 11
2 - 12
3 - 13

Basically I need to write a query which interrogates all the records PA0001 checks the mapping table and gives me all the records in PA0016 which do not have the expected mapping result.

So based on the above data I would expect it to return only the non-matched data, which will be:

EmployeeNumber - ContractType0001 - ContractType0016
1236 - 1 - 13
1238 - 2 - 11

I would probably need to do another query which does the exact same thing but the other way around. It would need to interrogate all the records from PA0016 and using the mapping table check the records in PA0001. In my example above I would get the exact same result, but I suspect with large amounts of data, I would get different results, especially if the number of records were not the same in both tables.

I suspect I would need to write some SQL code or VB code to extract the above.

If one of you kind folk could give me some basic pointers that would be great.

Thanks,

Evan

View 2 Replies View Related

Creating A Query Across 2 Tables

Mar 20, 2006

Hey :)

is it possible to create a query for 1 table and for the same query to retrieve some data from another table

Cheers

Tom Evans

View 2 Replies View Related

Query: Comparing Two Tables

Mar 22, 2006

I'm trying to write a report which initially I didnt think would be hard to do, but when it came time to write the underlying query I realized it was a bit above me.

I cant give away too much of the database structure here, due to legal issues, so sorry for being vauge. Hopefully I can give enough information that someone can get me started.

I have three tables that will be used in this query. One is called NAMES and has all of personal details of contacts. The only fields that are needed from this table are the primary key and citizenship field.

The other two tables are virtually the same, and they are the two being compared. One is REGISTRATION and the other APPLICATION. Essentially what I want here is to be able to compare the number of who applied to the number who actually registered.

Here are the pseudo-tables to give you a better picture:

NAMES
nID
FName
LName
DoB
Citizenship

REGISTERED
programCode
startDate
endDate
nID (foreign key)

APPLIED
programCode
startDate
endDate
nID (foreign key)

I've tried a few different approaches which worked in my head, but when it came time to run the query the results were different. Any suggestions on how I should tackle this?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved