I'm an Oracle guy, not an access guy, so I need some help please. I have 2 questions at least.
I have this query:
SELECT tOrders.ID, tCustomers.[First Name], tCustomers.[Last Name], t_OrderDetails.Product_ID, tProducts.[Prod Desc], tProducts.Price, t_OrderDetails.Qty
FROM tYears INNER JOIN (tProducts INNER JOIN ((tMonths INNER JOIN (tCustomers INNER JOIN tOrders ON tCustomers.ID = tOrders.Customer_ID) ON tMonths.ID = tOrders.Month_ID) INNER JOIN t_OrderDetails ON (tOrders.ID = t_OrderDetails.ID) AND (tOrders.Month_ID = t_OrderDetails.Month_ID) AND (tOrders.Year_ID = t_OrderDetails.Year_ID)) ON (tProducts.ID = t_OrderDetails.Product_ID) AND (tProducts.Month_ID = t_OrderDetails.Month_ID) AND (tProducts.Year_ID = t_OrderDetails.Year_ID)) ON tYears.ID = tOrders.Year_ID
WHERE (((tYears.Year)=[Forms]![pYEAR-MONTH-rpt]![Year]) AND ((tMonths.Month)=[Forms]![pYEAR-MONTH-rpt]![Month]));
And I want every ROW in the tProducts table to show up for each record in t_OrderDetails. The Orders Table is tOrders and each detail row is in 't_OrderDetails'. I went into design view and changed the links from t_orderdetails to tproducts as follows in this screenshot:
Anyway, in Oracle, I would just simply add (+) to each join column in tProducts but here the message states I must create a subquery and then include that query into the main query. How would I go about doing that?
Any ideas at all would be much appreciated. I'm doing a project for our church for free, and Access is all we have available to use. I've got too far now to turn back.
2nd Question is:
My ultimate goal, is to get this to work, and then to put the totals of each product in tProducts into it's own COLUMN on a report showing how much of something each customer ordered. There are only 4 to 8 products each month, so I hope to see something like:
Box of Food Special #1 Special #2 Special #3
George Jones 1 0 2 3
Dirk Smith 3 2 0 0
Tyler Jones 1 0 3 1
We need the sums to print in columns, not rows. I'm trying to get a loading manifest (for lack of better words) so the volunteers can see how much of each product the customer ordered. There will never (knock on wood) be more than 4 to 8 products ever.
Any ideas on how to do that?
Thanks for any help at all. I really appreciate it.
Attached is a pdf of the query window showing the relationships and table structure; (sorry for the quality) the linkage is also permanent at the relationship window. I created a form (columnar) of Rooms; loaded a subform (columnar) of the projects; and then loaded the students (tabular) as a subform on the projects subform. The data entry is flawless; tabs through each field and form to form in sequence.
After entering several rooms data I tested it at the query level by loading the three tables: rooms, projects, and students, and the permanentely established linkage with junctions came in automatically. I thought I was home free--but when I run the query, I get zero records.
When I attempt various joins, thinking this will yeild all records from the many tables and their match, I get "ambiguous outer joins" and it says to run a separate query and add it to the SQL Statement?
Thanks for any help,
Almost funtional in Ann Arbor........
Oh, and thanks Pat Hartman for the tip on linkage -- although I may have screwed it up anyway.
I'm new to the Access SQL world but I have a fairly complicated query that needs to be written for a project I'm working on. I am having trouble grasping the join concepts in access. My Query so far is:
SELECT u.user_logon_tx, ur.aff_sec_role_id, r.sec_role_ds, c.sec_cls_ds, o.sec_obj_ds, ci.aff_sec_cls_id, ci.sec_cls_item_ds, IIf(p.perm_in Is Null,0,p.perm_in) AS perm FROM aff_sec_cls_item AS ci, aff_sec_user AS u, aff_sec_role AS r, aff_sec_user_role AS ur, aff_sec_obj AS o, aff_sec_cls AS c, aff_sec_obj_item_role_perm p, ci LEFT OUTER JOIN p ON ci.aff_sec_cls_item_id=p.aff_sec_cls_item_id WHERE u.aff_sec_user_id=1 And ur.aff_sec_user_id=u.aff_sec_user_id And ur.aff_sec_role_id=r.aff_sec_role_id And o.aff_sec_obj_id=1155 And o.aff_sec_cls_id=ci.aff_sec_cls_id And o.aff_sec_cls_id=c.aff_sec_cls_id And ur.aff_sec_role_id=p.aff_sec_role_id And o.aff_sec_cls_id=p.aff_sec_cls_id And o.aff_sec_obj_id=p.aff_sec_obj_id;
I have gotten it to work by using just the tables I'm doing the outer join on but when I add everything else I get the Ambiguous Join error. I wish I was using Oracle Thanks for the help.
I have a database in which i am trying to create some forms in order to search for records. The database contains several tables. Some tables are linked using a compound key while others have a single PK. I created the search form which has different searching criteria. Once a user selects the criteria, they hit the search button (there are different buttons for each type of search) the button opens up a form to display all the data. The problem is that there are many tables to report on. I created a form with a subform. The tables associated with the main form are all being retrieved fine. however the subform doesn't seem to work. I created the subform using a query to select all the fields from the desired tables, i gave this query a criteria based on the main form so that the Id's would match. (ie. Main form ID = Firstable in subform.ID)
I got a blank sheet in the subform when i used the search. i presumed that my query was not pulling records because of the referential integrity rules. i went in and changed the relationship rules so that it would include all records from the first table and matching records from the second table. (middle option in the list when editing a relationship) I now get the following error when i try to run the query.
"The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in your SQL statement."
Could someone please tell me what this means as the help is... how shall we say it.. written in true Microsoft Fashion.
And though it tells me how to fix it, i'm not really sure what it's telling me to do. I am using Access2003 But the Database is in an access 200 file format.
Access 2000 tbl_TESTS (lists all active employees for a company) tbl_RANDOMS (table that lists which employee needs to take a test)
[EmpSS] is a social security field in both tables to connect them. I need a form that will allow me to show all active employees from tbl_TESTS and then select the option for a test from the tbl_RANDOMS.
tbl_RANDOMS EmpSS 1Q07 2Q07 3Q07 4Q07
tbl_TESTS CompName NameFirst NameLast EmpSS EmpInPool (yes/no check box for active employees)
I want a form that lists all of the fields above. Show all the active employees Then if I type something into one of the four fields for tbl_RANDOMS it adds a new record to the table.
I have added a simple DB for an example. When the form opens select Wal-Mart from the drop down. You will see the 8 people they have as current employees. Now it will not allow me to select one of the drop downs for any of the quarterly stats. Thanks.
I have several Option Group fields that are stored as Numeric with a datatype as Long Integer. I want to create a function that decodes these numbers into values (i.e 1 = open, 2 = in-work, 3 = shipped) and then use the function in a query.
I downloaded a sample DB that someone had posted to the site and I’m trying to modify it for my use. I thought I had everything correct but when I go to the form and try to enter any data into any field I get a popup box “The expression Before Insert you entered as the event property setting produced the following error: Ambiguous name detected: DocketNumber_BeforeUpdate. Would someone give this DB a look and tell me how to correct this problem. Thanks
I just restructured my DB and I was wondering if anyone can give me some advice on whether or not my joins/relations are correct. I left some joins/relations out because I wasn't sure what relation I should use.
I have a form and subform linked by the ID field (AutoNumber) on the Form and Record ID field (Number) on the subform. I have the field size on the Record ID field set to Long Integer.
When the form is opened, a message pops up "The LinkMasterFields property setting has produced this error: 'Ambiguous name detected: Add_client_info_Click' There was a macro set up with the name Add client info. However, that macro is not being used on either of these forms. I don't understand why this message is popping up.
Any ideas? I have checked each form, each form section and each field to make sure that macro didn't somehow get assigned, and I can't find it anywhere in relation to these 2 forms.
I've done a search of the forums, the results of which have more-or-less caused me to understand (roughly) what the problem might be and how to locate the code which is causing it. Sadly, I am a bit of a Visual Basic virgin, and I am reluctant to wade right in because if I screw up this database I'll have decimated my company's advertising and marketing strategy (mailouts!).
I was trying to make a command button on a switchboard (which I have done before, but not recently) -- and was taking a bit of a trial-and-error approach. I wanted the button to result in the previewing of a set of mailing labels (which the user would susbsequently print after reviewing). One of my first attempts resulted in the button automatically printing, so I "cut" it, and several close-but-no-cigar attempts follwed, all of which I used the "cut" command to dispose of. The last attempt I made resulted in the following message: "The expression On Open you entered as the event property setting produced the following error: Ambiguous name detected: Bulletin_Board_labels_Click. -The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure]. -There may have been an error evaluating the function, event, or macro."
I "cut" the button I had just created and thought that might fix it. It didn't. I still got the above message whenever I tried to open either this switchboard or the main switchboard. I tried closing down Access and restarting it. Same message, same problem.
Below is the code from the switchboard in question -- the last few entries are the relevant ones: anything to do with "bulletin board" is stuff I was working on. As far as I can tell there are currently no duplicate queries, reports, macros, etc, named "Bulletin Board Labels" (theres is one query named "Bulletin Board Query", and one report named "Bulletin Board Mailout labels", and that's it.)
The code for the switchboard follows...
Please help me resolve this problem?
:confused: Jennifer _________________________________________
Option Compare Database Option Explicit
Private Sub Command0_Click() On Error GoTo Err_Command0_Click
Dim stDocName As String
stDocName = "All Art Galleries" DoCmd.OpenReport stDocName, acPreview
Hey everyone! I just have a relatively quick question.
Situation:
I have a database where i have 2 tables. One table has items in one column, and the width, length, and height of the item. Another table has the exact same fields, except the only items are ones that need updated as far as their dimensions. The fields with those items include their new dimensions. How can I create a query to pull down all of the items with the correct dimensions?
I've got a table with a self join which represents a tree structure that can have variable depth.
I want to get a spreadsheet view of this for project review meetings. I can do a bunch of nested queries until I get all the branches but i would like something a little more dynamic, i.e. something that will automatically show the spreadsheet view of all branches no matter the depth of the tree.
So anyone got any suggestions on how to handle this?
Only been using Access for a little over 6 months. It's an ongoing struggle but a worthy string to my bow.
I have been using a simple join to filter out matching fields from a bigger table that exist in a smaller table. If you like i create the smaller table around what i need to see from the bigger one. I hope that makes sense.
So what i want to achieve now is what i'm calling a "compliment join". This is where i use the smaller table to filter from the bigger table but I am left with everything but what was in the smaller table.
Can anyone help me. I have a database table called orders. This contains and order status id. In a separate table i have the order status id and the what the id means i.e delivered, awaiting payment etc.
I need a record set that returns all the fields from the order table and the actual order status not the order ID.
I know i need to use a inner join but just can get it right.
Can anyone help me with this.
This is what i have but it return nothing:
Code:SELECT a.*, c.OrderStatusFROM Orders AS a INNER JOIN OrderStatuses AS c ON a.OrderStatusID = c.OrderStatusIDORDER BY OrderDate desc
EDIT:
Actually that is right and it does what, stupid me, frazzled brain at this time of night.
What i actually meant was how can i also pull the customers name from the database table Customer, based on the orderID?
I have a form with a drop down menu of people to filter a report of projects with the managers and up to 3 assistant managers. When I choose someone from the dropdown menu, I want all of their projects to come up on the report. Currently, only the projects that person is managing come up, not the ones they are assistant managing. In my query that is powering this report, I have joined the manager_id number in table A to an id_num field in table B. To make the assistant managers come up in the report, I need to join the assist1_id, assist 2_id, and assist3_id to id_num also. When I join assist1_id to id_num, I get the following error: The SQL statement couldn't be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in the SQL statement. The way I would like to fix this problem is by creating subqueries in the SQL view, but I don’t know how to code it. If you know how or if you know a better way of doing this, please help! :)
if i have a table with columns: Teacher ID1 | Teacher Comment1 | Teacher ID2 | Teacher Comment 2 |
i also have another table that links the teacher ID with their names called [Staff Profiles]
how do i create a query that returns the names of both teacher 1 & 2. i have tried:
SELECT * FROM [Subject Assessment] INNER JOIN [Staff Profiles] ON [Subject Assessment].[Teacher ID1]=[Staff Profiles].[Teacher ID]) INNER JOIN [Staff Profiles] ON [Subject Assessment].[Teacher ID2]=[Staff Profiles].[Teacher ID];
This throws an error. I have tried Aliasing but this also throws an error.
don't know whether this makes a difference, but the table which i gave was a join in the first place.....
i.e. teacherID1 | Teacher Comment1 is the tutor report of which there is one per student teacher ID2 | teacher Comment2 is the subject report of which there are many per student
Wierd Join needed...Here's my problem. it's been bothering for a bit...I have 2 tables, one with a date, and the 2nd table with 2 dates. I need to only pull the records from the 2nd table where the date in the first table come between them.TABLE 1DATEJOB#EMPLOYEE#TABLE2 JOB#EMPLOYEE#STARTDATEENDDATEThe query should take every line from TABLE1 and ONLY the lines from TABLE 2 that qualify.
Hello, I have the following code for a multiple join:
INSERT INTO [AppendAllFields]SELECT [TreatyList].[Treaty] AS [Treaty],[MLAC 42 Treaty Xref ER].[tai treaty] AS [TreatyType],[txn 01/04].[Policy_Number] (and more other fields from [txn 01/04] table) FROM [txn 01/04] INNER JOIN [MLAC 42 Treaty Xref ER] ON TRIM([txn 01/04].[Policy_Number]) = TRIM([MLAC 42 Treaty Xref ER].[Polnum]) INNER JOIN [TreatyList] ON TRIM([MLAC 42 Treaty Xref ER].[tai treaty]) = TRIM([TreatyList].[TreatyNo]);
Basically, the txn 01/04 table has a corresponding Polnum field in the MLAC 42 table, and MLAC 42 table has a tai treaty field, which corresponds to TreatyNo in TreatyList table.
However, when I tried to run this, I got an Syntax error.
From table 1, I join fields A, B, and C to fields A, B, and C on table 2. From Table 2, I join Fields 1 and 2 to Table 3. All the joins are Join 1.
When I pulled (Queried) fields D, E, and F from Table 1, field D from Table 2, and field D from table 3, I have a sum of $1000 under field (column) E from Table 1.
The second time I pulled data, I added fields A and B from table 1 to the query. However, I get a total of $1500 from the same column. i.e Field E from Table 1. I can understand that there will be more rows to provide further data breakdown, but I could not understnad why the total change.
Simple problem, but my access and sql skills are very limited.
I have two tables. One containing a group of frequent customers with a column called 'member status'. Another table containing a group of non-customers. The addresses in both tables have been matched using group1 software.
I would like to create a query that shows me all the customers with 'member status' = 'A' and all the non-customers who live at the same address.
When i run the query, everybody comes up as 'member status'=A. I think this is because there is no 'member status' field in the non-customers table, and i have failed to make the appropriate join or parameters. Is there a way to design a query that will show 'member status' for those who have it, and will display a null for those who do not?
