query probelms

I am a new access user and haveing problems joining tables into a query.
table A is a list of engineering spec numbers and ES_id
table B a list of details pertaining to the ES (namem rev, etc)
table C is a list of procedures numbers pertaining to a ES.  This table can
have from 0 to 10 precedures per ES.
Table D is a list of details pertaining to the procedures (name, rev etc)
Table E is a list of drawing numbers pertaining to a ES. This table can have
from 0 to 10 drawings per ES. 
Table F is a list os details pertainging to each drawing.
I have no problems linking table A and B becasue it is a one to one
relationship.
Also no problkems linking tables C and D and  linking E and f.
The problems I am haveing is linking tables A and c and A and D.
I want the data to come out like this:

ES #  and details
     LP (process number)  details
     LP (process number) details
          Draing number) details
I've tried lookups and don't get the correct results.  what am I doing wrong.

-- 
Message posted via http://www.accessmonster.com

0
Pkobo58
4/12/2007 1:52:05 AM
access.queries 6343 articles. 1 followers. Follow

1 Replies
831 Views

Similar Articles

[PageSpeed] 24

On Thu, 12 Apr 2007 01:52:05 GMT, "Pkobo58 via AccessMonster.com" <u33245@uwe>
wrote:

>I am a new access user and haveing problems joining tables into a query.
>table A is a list of engineering spec numbers and ES_id
>table B a list of details pertaining to the ES (namem rev, etc)
>table C is a list of procedures numbers pertaining to a ES.  This table can
>have from 0 to 10 precedures per ES.
>Table D is a list of details pertaining to the procedures (name, rev etc)
>Table E is a list of drawing numbers pertaining to a ES. This table can have
>from 0 to 10 drawings per ES. 

Why 0 to 10 - why not as many records as you need? Each drawing should be in
its own record; E should have fields

DrawingNumber <Primary Key>
ES_ID <link to A>
<other information about this drawing>

>Table F is a list os details pertainging to each drawing.
>I have no problems linking table A and B becasue it is a one to one
>relationship.

So there is one and only one detail for each ES? Or are you "committing
spreadsheet" by having one *field* for each detail? One to one relationships
are quite uncommon; if it really is a one to one, you could just include table
B's field in table A.

>Also no problkems linking tables C and D and  linking E and f.
>The problems I am haveing is linking tables A and c and A and D.

Is there an ES_ID field in C or in D? How are you trying to do the link? 

What is the Primary Key of each table?
What (if any) Relationships have you established in the Relationships window?

>I want the data to come out like this:
>
>ES #  and details
>     LP (process number)  details
>     LP (process number) details
>          Draing number) details
>I've tried lookups and don't get the correct results.  what am I doing wrong.

Since you don't say *how* you've "tried", nor what the errors were, I'm a bit
in the dark!

             John W. Vinson [MVP]
0
John
4/23/2007 5:44:16 AM
Reply:

Similar Artilces:

Query printers in 2008 domain using vbscript or WMI
I have the following code which I use to query published printers in AD on our domain (2003). We have now setup a 2 way trust with another domain (2008) which I can browse to with no problems. The problem occurs when I try and run this script with the remote domain in the LDAP query returns "Provider: A referral was returned from the server." Const ADS_SCOPE_SUBTREE = 2 Set objConnection = CreateObject("ADODB.Connection") Set objCommand = CreateObject("ADODB.Command") objConnection.Provider = "ADsDSOObject" objConnection.Open "Active D...

Validation List Query
Hi all, I've got a validation list showing a project problem status: New, Resolved, In Progress. These options are a named range 'Status'. Thus I do Data>Validation>Allow:List>Source:=Status No problem there. However, from time to time I need to input a custom status, which could be anything. The problem being that once I have a cell validated it does not allow me to type anything in it. Is there a means that keeps the validation drop down, but also lets me input my own data if, and when, I need to? I could always remove the validation from the individual cells, but h...

Running a parameter query off more than one multi-select list box
I have finally been able to create a form with a multi-select list box and a command button where a click of the command button opens a query with the selections in the multi-select list box as its criteria. So far so good... now I am wanting to use more than one multi-select list box as the criteria for the query. The code I used to successfully run a query off a multi-select list box is as follows (with Command6 being the name of my command button, TESTQ being the name of my parameter query, MyTable being the name of the table my list box gets its values from, MyTableID the name of the fi...

Return Random Sampling from Query
If I have a first query that returns 3000 records, is there a way to build a second query based upon the first to pull a random sampling of say 225 records from it? QB SELECT TOP 225 * FROM QueryName ORDER BY Rnd(IsNull(NumberFieldInQuery)*0+1); It would work best if NumberFieldInQuery is the primary key field in the original table. NumberFieldInQuery needs to be a number field. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "QB" wrote: > If I have a first query that returns 3000 records, is th...

print query prompts in report header when no data to report
a report which uses a Query with prompts(expr1: xyz) and other criteria selects no records. The report header prints, but (expr1) which is a field in the header is blank. I want the prompts to print in the header even when no data is found. http://www.tek-tips.com/faqs.cfm?fid=6763 If you use references to controls on forms, the values will always be available even if the query returns no records. -- Duane Hookom Microsoft Access MVP If I have helped you, please help me by donating to UCP http://www.access.hookom.net/UCP/Default.htm "ConfusedCraig" wrote: > a report wh...

Retrieving consecutive pages in web query What's wrong here?
Hello How come I keep always getting the same 100 items instead of the next 100? I wrote this code , of which I do not understand every line but something similar worked in other queries. Could you help? I'd be very gratefull. Herman Sub Macro5() Dim i As Integer For i = 0 To 3 With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://www.iec-iab.be/nl/diensten/zoeken/Pages/ Zoeken.aspx?all=true&page=i+1&count=100&ext=on&intern=on" _ , Destination:=Range("$A$1").Offset(i * 103, 0)) .Name = "Zoeken.aspx...

Calculation query, how to avoid extreme values?
Good morning, I have 1 table with a few fields (A, B, C) that I'm using for various calculations. For various reasons are some of the input "weird" and I want to leave that out of the calculation (average). For example, Field A requires > 0 AND < 100, B < 400 Now, if one record doesn't fullfil A requirement I don't want to use it for A's average calculation BUT I want to use it for B's average calculation (if it satisfy B's requirement). This means that if I use the query criteria A: >0 AND <100 it will filter out all other records an...

Recursive Query
I've been working with a recursive query recently and I've been unable to get the expected results. I'm looking to pull a customer purchase record for each time they purchased a particular product and to give them a discount if it's been over a year since they purchased a product and then additional discounts if it's been a year since they last received a discount. So, if I have the following information: LineID Customer ProdPurchaseDate 1 Cust1 2007-05-05 2 Cust1 2007-09-20 3 Cust1 2008-04-16 4 Cust1 2008-06-...

Need some query help.....
I'm very new to Access. I was wondering how I could display a field in a query that is conditional. I will do my best to explain... I want to add a field to my query that grabs the info from Project.Badge or Project.BadgeET depending on if the field TestType is displaying "Eng" or "Pkg" so it would be something like this If TestType = "Eng" then Display Project.BadgeET ElseIf TestType = "Pkg" then Display Project.Badge End if im assuming I would type some formula in the Criteria section?? but under field, i can on...

User Defined Query Function Not Working with Multiple Criteria
Using Access 2007 I created a user defined function used in a query that works fine on single criteria but not two or more. These work fine: MyFunction="Open" MyFunction="Closed These don't work: MyFunction="Open Or Closed" MyFunction="'Open' Or 'Closed'" What do I need to type to get this to work? Unless you let us know something specific about the function itself, it's going to be difficult to diagnose why it isn't working. Consider posting the function ... (and wouldn't the function spell out how many...

Help w/Query
I am trying to build a query based on 2 tables. Main Table has Entry#, Date, Location, Product1, Amount1. Product Table has ProductID, Content, and Catagory. I am trying to add the Content and Catagory for the product that is selected in the main table. Because later on i need to sum up the amount of product in each catagory. So far i have an append query with the tables i need. I have a join between product1 and productID where all records from main table and only records from Product Table where those are equal. When i run the query i get product1 listed for every content and cat...

parent child link query
I have a query which has a parent and child link tables and would like to have all the records in the parent even no child for this parent. It seems MS Access gives the link only the parent has child. I try to use outer join, but MS Access gives me it contains ambiguous outer joins. Any information is great appreciated, Use a left join -- FROM Parent LEFT JOIN Child ON Parent.PrimayKeyField = Child.ForeignKeyField -- Build a little, test a little. "inungh" wrote: > I have a query which has a parent and child link tables and would like > to have all the...

Rules Query
Looking to create an 'out of office' reply. I am at the point where a reply template should be should be selected. The browse function is grayed out. I have saved a template named outofoffice. Cannot proceed. Guidance sought. Maurice;117727 Wrote: > Looking to create an 'out of office' reply. I am at the point where a > reply > template should be should be selected. The browse function is grayed > out. I > have saved a template named outofoffice. Cannot proceed. Guidance > sought. See if this helps: http://office.microsoft.com/en-u...

Web Query question
I am trying to download stock options through the following vba code, however i am having problem with separator between "m" field which is month and "s" field which is stock symbol, I appreciate any help i can get, With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://finance.yahoo.com/q/op?s=" & Range("b1").Value;&m=2010-05", Destination:=Range("$A$11")) thanks david I think you are mising (at least) one quote? Reg Migrant "David" wrote: > I am trying to download stock optio...

Querying in an XML file
Hi, I have the XML file shown below. Now I am trying to retrieve all the 'Event' nodes using XPath. The code being used is shown below. The output being ouput is the data in the SubNodes given as one string. (Output shown below) Is there a way how to iterate through the sub-nodes(From, To, Lookahaed). What I was doing is three different XPathExpressions for each sub-node, but I think thats not very effecient. Can someone help me out Thanks in Advance /* code */ StreamReader reader = new StreamReader(filePath, Encoding.UTF8); XPathDocument doc = new XPathDocument(reader); XPath...

Excel not saving query parameters
Hi, I have folowing problem... it seems like excel problem... who knows.... using excel 2007 I've created simple macro enabled excel workbook. Then I use Microsoft Query to access one of my SQL Server tables ... create query with parameter. Then I use excel "Parameters" dialog to set parameter as a reference to specific cell. everything works fine.... however when i close and reopen excel file parameters setup is lost... how to preserve parameter setup? Hi, I'm having exactly the same problem in MS Excel 2007 & I came across this thread - I see you posted a while ago b...

Query Question 04-16-07
Hello all and TIA... Access 2003 Win XP Pro I am writing a mktbl query that pulls data from a SQL Server. One particular text field is 16 digits long, so the field in the table I am "making" is a text field and is 16 digits long. However, I also need to run an append query after the mktbl, where the same field is 17 digits. When I run it, it truncates the last character from the SQL table and stores it in my table. Is there a way to force the mktbl query to say, regardless of how many characters the field is that I am pulling from, I want this field to be 17 digits? I tri...

Query dates for Chart
I have tblEmployee with [employeeID], [name], [startdt], [enddt], etc. I want to build a chart/report which will give me the number of employees employed by month (Jan07, Feb07, etc.) . I can create a query with an expression for Jan07 which returns a true result as expected. I just think there is a better way to do this. I feel like I am "forcing" the issue. Any suggestions? -- Teach me to fish! Thanks for the help. Pax, M It would help to have a table/query of each year and month in the entire range required. You could then add this table/query to a query with tblEmpl...

Parameter Query
I'm running a simple parameter query and it's working fine, however when I go to load the report based on the query it keeps asking for the parameter over and over again. The main report and sub report are based on the same query, but when I finally enter the parameter enough times only the main report data appears, the sub-report comes up blank. Is there a way a parameter query can run a main report and sub report at the same time? T.Kay wrote: >I'm running a simple parameter query and it's working fine, however when I go >to load the report based on the qu...

query on records based on time interval
If I have a table something like this:startdate | stime | endate | etime | tool3/3/07 | 8:47 | 3/4/07 | 00:30 | A3/4/07 | 3:57 | 3/4/07 |4:30 | B3/4/07 |8:21 | 3/4/07 |9:20 | CCurrently I have a form which use to open dailyrecord.I have one textbox([date]) for enter date and a button to view.Is it possible when I wan to open a new form with all the records on3/4/07it will only give me the records from 3/3/07 7:00 till 3/4/07 7:00(thus, only show tool A and tool B)my currently event procedure for the button is below:Dim strSQL As StringDim strWhere As StringIf Not I...

Query: how to count up data from different fields
Hi, I have a query that brings a bunch of text fields from a table, each fields value can be either Yes or No. It's a kind of an audit questionnaire and No means an error. So i'm trying to count a number of No's in my query. Any idea how it could be done? Thanks You need to give a bit more information on the structure of your table. Do you have multiple fields in one record that contain Yes or No? If so, are you trying to count the number of No in the record or the Number of No responses in all the records for each question? Assuming you want a count of NO repons...

query criteria 12-29-07
I have a criteria test in the [DR].[Received Date] field of a query. The criteria is based on data which is entered into a form. >=(IIf(IsNull([Forms]![CSR dg]![txtEarliestReceivedDate]),[DR].[Received Date],[Forms]![CSR dg]![txtEarliestReceivedDate])) The true side of the IIF allows for all received dates if no data is entered into the form. I would like to instead not do a criteria test at all if there is no date entered into the form. I've tried things like "" or NULL in the criteria, but to no avail. The reason for wanting to do this, is that I have two tables linke...

Help Regarding an update query
Hi, iam trying to run an update query. the situation is as follows, i have linked excel sheets in my table. so i am using the update query to update all the records that i have them in my access tables. i tried this before many times and i was successful in doing that. But in some tables, it gave me an error message "Type mismatch in expression". i checked each and every field in my access table and in the excel sheets and they are all with the correct format. any help?? any ideas? On Fri, 31 Aug 2007 08:47:16 -0700, Samer <samoora44@gmail.com> wrote: >Hi, iam trying to r...

How do I reference a Web Query on another sheet?
I am taking a Microsoft Office college course. I have a Web Query on worksheet one and I reference it in worksheet two, but when I hit enter it displays a 0. Do you want to reference the results of the query, or the quert itsekf? -- AP "ReAnna" <ReAnna@discussions.microsoft.com> a �crit dans le message de news:986F7D26-54D3-4105-8352-0475492EC7DE@microsoft.com... > I am taking a Microsoft Office college course. I have a Web Query on > worksheet one and I reference it in worksheet two, but when I hit enter it > displays a 0. I want to reference the results of ...

Add Fake Records to Query from Table
I have a table that contains real data and then I have a table that has dummy data that is needed for another program that I'm sending the file too. I need to figure how to put them in the same colunm in the table. Here is my statement. SELECT [DBA_DEVICES]![deviceName] & " " & [DBA_ITEMS]![itemId] AS [Station Name & Item], DBA_ITEMS.itemName AS [Item Desc], DBA_POCKET.pktParQty AS Par, DBA_POCKET.pktRefillPoint AS Refill FROM [DocFinity Fake Records], (DBA_ITEMS INNER JOIN (DBA_POCKET INNER JOIN DBA_DEVICES ON DBA_POCKET.deviceIid = DBA_DEVICES.device...