Self-join and form issues

Hello,

I am completely stuck with one issue of a task, and it is driving me insane because the logic seems so simply, but I am lost as to how to make Access do what I want.

Basically, I have an entity/table, Work, with has several fields. One of those fields is Work_Name, which contains the names of various jobs/services. This field will also contain "package" deals, which are made up of various combinations of the jobs/services in Work_Name. I also have a field that simply marks whether or not an observation in Work_Name is a package.

I know this is an issue with self-join, and in the Relationships window, I have added a second copy of the Work table, and have created a third table for the relationship "CONTAINS." tbl.Contains has two foreign keys, "Package_Name" and "Service_Name", both of which refer to "Work_Name" in the Work table.

I want to create a form based on Work where I can enter services and, if I am entering a package, use a subform to:

1) Enter the package in Work, and
2) Enter the package and its component parts/services into Contains.

How do you get Access to know that Contains should only be populated/updated when you're adding something that is a package, but also enforce integrity in the sense that both the name of the package has to agree on both tables, and that the services listed as a part of the package have to exist in Work as well?

Please help, driving myself insane.

Thanks. Attached Files database_test_2.zip (30.8 KB, 2 views) Reply With Quote 10-05-2011, 12:55 PM #2 June7 Super Moderator Windows XP Access 2010 32bit Join Date May 2011 Location The Great Land Posts 15,120 I think you meant: This table will also contain "package" deals, which are made up of various combinations...

I think it will be simpler if you also consider Purchases of only one Work item as a 'Package'. So tables and relationships would be:

Purchases
PurchaseID (primary key)
ClientID (foreign key)
PackageID (foreign key)
ActualTime
ScheduledDate

Packages
PackageID (primary key)
PackageName
CostPerHour

WorkPackages
WorkPackageID (primary key)
PackageID (foreign key
WorkID (foreign key)

Work
WorkID (primary key)
Description

The problem I see is the CostPerHour field. If you offer special pricing for 'packages' then need pricing in the Packages table. If you don't offer special pricing then why bother with 'packages'? Without 'packages', would do:

Purchases
PurchaseID (primary key)
ClientID (foreign key)
ActualTime
ScheduledDate

PurchaseDetails
PurchaseDetailsID (primary key)
PurchaseID (foreign key
WorkID (foreign key)

Work
WorkID (primary key)
CostPerHour
Description


Post your answer or comment

comments powered by Disqus
Hi
I know very little about access and I am still learning. I am trying to create a database that has some complicated things I don't know how to work out.
I have a table of people and there contact information. They are professors and researchers in a field of science. I also have create another table with all of these peoples connections to each other.

For example:
person one worked with person 2, been a mentor to person 4, and cofounded an organization with person 6
in return
person 2 has worked with person 1, and been a student of person 11

(I hope this is not to vague, do you understand what I mean?)
And so the second table of connections is set up like a joining table in the middle of a many to many connection.

I want to be able to search for person 2, that persons contact information, all the people person 2 has ever been associated with, and bring up their contact information as well.

I have read about self joins and I thought I understood it, but I can't make them work. I thought understanding them would help me with this problem.

So what I want is some kind of loop back to the people table from the people table with a stop at the connections table in the middle. Maybe I am not setting up the primary and foreign keys correctly, how should I do this? Is there another way to do it?

Kelly

I have a table with a field that relies on another field in the same table. Very much like the Employee/Boss table design that is used for self join examples. I have developed the table and query but do not know how to make the form that would support the table design.

The field in the table are:

strID (Primary Key)
strName
strBoss

I think it will require a two form approach, one to create the employee and one to assign the boss. But I want the boss to be a combo box with only employees that have been created. Please help. I thank you for your assistance.

Phil

I'm taking an ACCESS course right now and we've just covered the topic of Self-Joins. (We're using ACCESS 2003.)

The examples in the class text show the Self-Joins being established within the Query window of query design.

My question is: Should Self-Joins (and, of course, duplicate copies of the table in question) be set up within the Relationships window, as well ?

I am running the following UPDATE statement on a table with about 1000 rows, using a self join:

UPDATE [Tax Report Co Date] INNER JOIN [Tax Report Co Date] AS [Tax Report Co Date_1]
ON [Tax Report Co Date].ID = [Tax Report Co Date_1].[ID+1]
SET [Tax Report Co Date].Co = [tax report co date_1].co, [Tax Report Co Date].[Date] = [tax report co date_1].date
WHERE ((([Tax Report Co Date].Co) Is Null) AND (([Tax Report Co Date].Date) Is Null));

The problem is that it doesn't update all of the appropriate rows "the first time". It leaves gaps of rows unchanged... almost as if it blocked itself from updating, or some sort of buffer/cache issue. If I run the UPDATE a second time it updates the remaining rows successfully.

Any ideas on why it takes multiple passes to perform this update? Can I do something differently?

I am quite new to Access. I am using Access 2007. I think I am dealing with a "classic" issue - including supervisor employee number on records and having them refer back to the same "Employees" table.

I have searched the forum and found several references to "self join", but I cannot get any hits back with the forum search engine when I enter the search subject as "self join". Maybe it's a grammar error and someone can give me the correct search box syntax.

I've seen a reference to the Northwind sample DB, but what I downloaded from the templates does not include this feature.

I really would just like a pointer to a good explanation of how self join is implemented - a previous post, a website, a book, anything. I can do the research, and would prefer to. It would help to narrow the potential sources for the answer.

I know literally hundreds of you forum users can supply a reference. Thanks in advance for your suggestions.

Conor

Hey Folks,

I got a complicated issue here ... this has to do with tables and forms. I am using MS Access front end linked to SQL Server back end for a gigantic survey for a non-profit. The survey is so large that it goes well over the 255 field limit per table/form with Access. In order to make this work, because removing fields is not an option at all, I split the large table into two smaller ones to get away from the field size limitation for both the tables and forms. The table that holds all of the primary info has a primary key that is set by SQL that works fine - int auto number ID unique non null with retired numbering if a delete occurs. Here is what I am struggling with: I need to somehow link the two tables together so that each time a user creates a new record via the form or via command buttons or any other means, I need that second table to automatically create a record for that person and populate the ID key on that table to match the primary table's key so I can link the data. I am unsure what is the most thoural and complete fool proof way to do this so I wont end up with this app errering out or worse yet somehow end up with not being able to join the info from the two tables properly and to the same person in question.

If I can figure the above out, I can then try to dump the second table, then linked, to the main forn to allow the users to update that info as well. Of course two separate forms are necessary due to fields on each form having the 255 limit.

Any help would be greatly appreciated.

Thanks,

Joe

I am having difficulty with a query. I found another post on the topic(titled Combine SubQuery and Left Join) Their solution worked perfectly, until i tried to add another subquery.


	Code:
	SELECT name.longname, a.SystemID, a.PDModifiedby, a.PD
FROM ([Copy of Table] AS a
LEFT JOIN [current log] AS log ON a.systemid=log.systemid)
LEFT JOIN (SELECT A2.customerID, LongName FROM [Copy Of Table] AS A2, GeneralInfo AS info WHERE
A2.CustomerID=Info.CustomerID)  AS name ON a.customerid=name.customerid
LEFT JOIN (SELECT systemid, max(OID) AS max_oid FROM [Copy Of Table] GROUP BY systemid)  AS max_id on
max_id.systemid=a.systemid
WHERE log.systemid Is Null 
And a.systemid Is Not Null
And a.PDModifiedDate Between Forms![Reconciliation Report]!Start_date And Forms![Reconciliation Report]!End_date
And a.systemid In (300178,300059,300056)
GROUP BY name.longname, a.SystemID, a.PDModifiedby, a.PD;

The issue arises when i add the below subquery. The subquery itself is fine, but i must not be adding it correctly into the rest of the query.

	Code:
	LEFT JOIN (SELECT systemid, max(OID) AS max_oid FROM [Copy Of Table] GROUP BY systemid)  AS max_id on
max_id.systemid=a.systemid

Any help you have your be greatly appreciated.

Thank you,

Chris

Hi,

I have a dbase with a form to look and search and stuff.
Now It hold clients and retailers.

What I want is to join certain retailers to a client. like:

Client "blabla"
Can get products from:
retailer1: name address Button: CONNECT A RETAILER HERE
Retailer2: name address Button: CONNECT A RETAILER HERE

But as you might see, the retailers and clients are in the same table.
I tried builing a self-join append query but it does not work. If I start it

The whole purpose is to prevent users to have to copy the address info from the retailers to the clients.

Anyone who can set me up? I appreciate it.

I am working on a database that was previously created and find myself with an issue. I cleaned it out and cleaned the tables to link them correctly. I also created new tables and since have created a form with subforms to capture all the information. The database is for capturing new clients with an intake for as the main table and all tables linking to it via the primary key. The form I created is frmCompanyDetails to capture all the information. Under this form I created tabs to house the subforms that pertain to each client "business". When I enter the information the record is not saved. The autonumber on the main table is not created to link the rest of the tables. I can upload the database for review purposes since I am not well versed and to expedite the learning process as well.

I attached two pictures. One is the form I am having issues with and the other shows my database relationships.

Question 1 - The first issue I am having is when the category is updated on the form, I want to populate the [Boxes].[Directory_ID] with the correct [Directory].[ID]. The directory ID is in the hidden column of the Category combo box which has the Directory table as its source. What I did was try to setvalue of Directory_ID by the amount hidden in the other column of the category combo box. It didn't work. Was I on the right track? I did check to see if I was getting a value for the hidden column by using a text box on my form.

Extra Information = The form source is the Boxes table.
The Box Number text field, Category combo box, RecordFYE text field, scanned field, and subform are bound to the boxes table but the rest are unbound.

Question 2 - Since three of the combo boxes are unbound (Department, Division, and Function), they always start blank. What I would like them to do is default to the value based on the category_ID of the record. In the picture it shows them filled in but that was because I selected the information.

Extra Information = The division, function and category combo boxes are all sorted based on the entry from the combo box above them. For example, what you select in Department will only show the Divisions for that department. I do this by filtering the row source. Attached Thumbnails     Reply With Quote 08-02-2012, 05:31 PM #2 June7 Super Moderator Windows XP Access 2010 32bit Join Date May 2011 Location The Great Land Posts 15,121 Not understanding data and form structure. I don't see a Category_ID field in the tables. Why would Directory_ID from Boxes be in the Category combobox RowSource?

Show the RowSource sql of the Category combobox. Show BoundColumn and ControlSource properties.

Is the Category RowSource controlled by selections of Department, Division, Function (cascading comboboxes)?

Want to provide db for analysis? Follow instructions at bottom of my post.


Options to display related data that is dependent on key in a combobox:

Form RecordSource is a query that joins the tables, jointype 'Show all records from ...". Then fields are available to ControlSource in textboxes.

The combobox includes the fields in its RowSource. Textbox ControlSource expression refers to the relevent column of combobox.

Textbox ControlSource is a DLookup expression (doable but not recommended).

I was discussing this in another thread but it was way off topic so I'm reposting it here.

Short version: I need to know how to setup a Self Join for a table with a many-to-many relationship to itself.

Long Version: I have a table of Objects (i.e. Desktop Computers, Access Points, Offices, Personnel). Many of these Objects have other Objects as properties (Users belong to an Office, Desktop Computers belongs to a User, etc.). So, because of things like Users being able to belong to multiple Offices, but also, of course, multiple Users being able to belong to one Office, it needs to be a many-to-many relationships. There's also an ObjectType table and each Object Type can be assigned and ObjectType as a property as well. I'd like for my final forms to be datasheet forms. Each datasheet represents all the Objects in one ObjectType and each column is an ObjectType associated as a property to the datasheet's ObjectType. Here's my current structure:

tblJunctionObjectTypes
-JunctionID pk
-ObjectTypeID fk (The ObjectType receiving another ObjectType as a property)
-PropertyObjectClassID fk (The ObjectType being assigned as property.)

tblObjectTypes
-ObjectTypeID pk
-ObjectCTypeName

tblObjects
-ObjectID pk
-ObjectTypeID fk

tblPropertyObjects
-PropertyID pk
-ObjectID fk (The Object receiving an Object as a property)
-AttributeObjectID fk (The Object being assigned as a property)


How do I setup the relationships and how do I build a query that gets all Objects of a particular ObjectType and all the Objects of ObjectTypes associated to it.

I have a 100M row SQL Server table (SStb) joined against 2K local Access (Atb) table top update 3 fields in 100K rows of SStb. Atb is a lookup table. In Access, the query takes 1.5 days to run.

I'm charged with optimizing same. With DAO, the parsing takes about an hour, cycling through the SStb rows, one by one. An hour is also too long.

SStb has several user columns, there's no SQL Server scratch tables to populate the 2K lookup.

Could I somehow build a temporary SQL Server table using Access DAO/ADO to hold the 2K, 2 column lookup rows? More suscinctly, can I populate a SS temporary table using Access DAO/ADO? If so, how?

Alternatively, can I populate 2 columns or 2K rows of the 100Krows of SStb with my lookup values and create a pass through query self joining SStb 3 times on the lookup columns?

Hopefully, this is clear. Thank you in advance for your thoughts.

Hello all,

Been a long time since i had to get involved in access, or SQL so my brain is devoid of all useful knowledge!

I'm trying to create a sales order processing system for work. I have a sales order input screen to the SOP database with 10 lines for a stock description, quantity and price. I've created a query that does the maths on qty * cost, vat and totals called SOTotal.

I've included the primary key to the query and used a pivot table to add it to the primary key on SOP, so it ties in with the records (that was the hope).

When i use =DLookUp("[sototal.Total1]","SOTotal") it will place the total for record 1 on every record i look at, doesn't seem independent. Even though the query has a diff value for said column Total1 for each record.

Decided that as the dataset won't be too large, i'd go against best practice and create a field in my table for the respective total's (as a fudge round the query not working). As a test i've used:

Private Sub Form_AfterUpdate()
[SOP.total1] = [SOP.price1] * [SOP.qty1]
End Sub

This will do the update fine, but freezes the record at the same time. Can't go back or forth, but don't get an error either....just when close it i get the message that it may have encountered an error, and all data will be lost!

Running out of ideas now, even when i just do a sum column on the form it sum's up every record's price1 column, so there's something fundamental i'm missing/forgetting in this..

Any help is greatly appreciated!

I have very limited ACCESS knowledge. I currently have a form from Infopath that has about 200 fields. These are filed in a library where other users review, update and save. About 20 of the fields need to be sent to an Access Database. I cannot get this to work properly so I am trying to see if I can develop the form in Access. Once these Access form are completed, can they be filed and reviewed or are they only forms to easily load data into the database and not saved for review?

I have a db setup to track customers and invoices. Design is as follows.

customers tbl:
customerID-PriKey (autonumber)
name
address
phone

invoices tbl:
invoiceID-PriKey (autonumber)
customerID
subtotal
tax
total

invoiceitems tbl:
ID-PriKey (autonumber)
invoiceID
item
price
quantity
total

One-to-many relationships are setup between customers and invoices based on customerID and between invoices and invoiceitems based on invoiceID. I have a single form for customers with nested subforms for invoices and invoiceitems. My goal is to have a single form to browse and add customers and browse and add invoices.

The challenge I am running into is that a new records has to be created in the invoices table before items can be entered for a new invoice. This is a challenge because all of the fields in the invoices table are either autonumbered (invoiceID), based on a foreign key (customerID) or calculated (subtotal, tax, total). So it doesn't make sense to require the user to enter data that creates a new record in the invoices table before they start entering invoice items.

My solution so far has been to use the On Enter event of the invoices subform to set the customerID field to that of the current customer and save the record before the user can enter invoice items. That works except if the user is browsing invoice records and browses to a new record, it bypasses the On Enter event and the user gets an error trying to enter invoice items. I've tried adding VBA code to set the focus on a control on the main form and to enable/disable the subforms, but that has just caused other issues. Another challenge is that invoice records can get created without associated invoice items ever being entered.

How can I improve my table and form design given my goal of managing customers and invoices on one form? Is there a way to ensure an invoice record gets created before items are entered for a new invoice? What can I do to prevent or cleanup invoice records that get created with no associated invoice items?

Thanks in advance for your help and sorry this is kinda long winded.

I'm trying to create a database of Publications (instruction manuals) where one publication is a modification to a parent modification. I'm storing all 'publications' in one table for better maintenance. This means that I have to use a Self-Join if I want to make one the parent, and one the child. Fundamentally this is no different than the Employee/Supervisor example I've found in many places:

http://www.databasedev.co.uk/self-join_query.html

I created a query with two copies of the same table with the 2nd one renamed for easier viewing. The query final comes up, but when I choose "ParentPub", it doesn't give me a drop down showing the other pubs so that I can choose one to be the parent.

What did I do wrong? I should be able to open up the table 'tblPubs', and get a drop down menu showing me all the pubs I can assign as a parent.

Thank you for your help,
Enrique

Hi There,

I'm sure we all know of the Northwind self join, "employees" and the "reports to" field

I have a question about the same situation.

The DB I'm working on has the exact same situation, the only difference is that it's not a autonumber PK but a natural PK, one that's quite a bit longer.

There's employees and their "Line Managers" which are also employees. I was thinking about leaving it the way it was so that each record within the table had the line manager named rather than having a field with the PK self join.

The only reason for this is because the PK is quite long "General 1 Assist" (that kind of thing) and I don't see the benifit in having this in two fields within the same table.

Can anyone help me out and tell me what your opinion is....

Any help would be grateful.

Thanks

I've included my tables, input data, output data and sql.
As you will notice the self join introduces errors in numbers in both Expr1 and Expr3.

I hope someone can see what is introducing the errors and how to fix it.
Thanks

I need some help I suppose is meant to be a self join query -

I have a table called tblExport with 4 fields – ID, Date, Destination and Status. I want to be able select records where Date is same, Destination is same and the Status is Success for some IDs and Transport for some others. So I can only see if on the same day to the same destination, status of one or more records was showing “Success” and one or more were showing “Transport”. I have attached an example spreadsheet with some records so that it makes more sense. Hope someone can help me with this.

Many thanks.
gsrai31

I have a self join query that pulls a bill of material 7 levels deep. Here is the SQL:
SELECT tblItemMaster.PartNmbr, tblItemMaster.ItemClass, "1" AS [Level 1], tblBOM.ChildClass, tblBOM.ChildPartNmbr, tblBOM.QtyReq, "2" AS [Level 2], tblBOM_2.ChildClass, tblBOM_2.ChildPartNmbr, tblBOM_2.QtyReq, "3" AS [Level 3], tblBOM_3.ChildClass, tblBOM_3.ChildPartNmbr, tblBOM_3.QtyReq, "4" AS [Level 4], tblBOM_4.ChildClass, tblBOM_4.ChildPartNmbr, tblBOM_4.QtyReq, "5" AS [Level 5], tblBOM_5.ChildClass, tblBOM_5.ChildPartNmbr, tblBOM_5.QtyReq, "6" AS [Level 6], tblBOM_6.ChildClass, tblBOM_6.ChildPartNmbr, tblBOM_6.QtyReq
FROM tblItemMaster INNER JOIN (((((tblBOM LEFT JOIN tblBOM AS tblBOM_2 ON tblBOM.ChildPartNmbr = tblBOM_2.PartNmbr) LEFT JOIN tblBOM AS tblBOM_3 ON tblBOM_2.ChildPartNmbr = tblBOM_3.PartNmbr) LEFT JOIN tblBOM AS tblBOM_4 ON tblBOM_3.ChildPartNmbr = tblBOM_4.PartNmbr) LEFT JOIN tblBOM AS tblBOM_5 ON tblBOM_4.ChildPartNmbr = tblBOM_5.PartNmbr) LEFT JOIN tblBOM AS tblBOM_6 ON tblBOM_5.ChildPartNmbr = tblBOM_6.PartNmbr) ON tblItemMaster.PartNmbr = tblBOM.PartNmbr
WHERE (((tblItemMaster.ItemClass)>="01" And (tblItemMaster.ItemClass)

I am trying to create a query to use in a report. I am needing to self join
one table. The table consist of Family Number, Child Number, Wish Item. I
am needing the query to concatenate the wish item to one field.

So I need a field to look like this:

basketball baseball
soccer football

The results I am getting with my current SQL (listed below) is this:

basketball baseball
basketball soccer
basketball football

What am I doing wrong?

SQL:

SELECT DISTINCT [Child Wish List1].[Family Number], [Child Wish List1].[Child
Number], [Child Wish List1].[Child Wish] + " " + [Child Wish List2].
[Child Wish]
FROM [Child Wish List] as [Child Wish List1] LEFT JOIN [Child Wish List] as
[Child Wish List2] on [Child Wish List1].[Family Number] = [Child Wish List2].
[Family Number] and [Child Wish List1].[Child Number] = [Child Wish List2].
[Child Number] and [Child Wish List1].[Child Wish] [Child Wish List2].
[Child Wish];

Hi,
I've encountered a problem with regards to binding a continuous subform to an SQL query which contains a self join. I can display the data fine however cannot perform updates, instead I'm confronted with an error message stating that the recordsource has changed.

Basically I'm trying to retrieve data from two rows in a sql table (using a s self join) and display them as one row in the subform. Unfortunately I need to be able to update fields from both rows (well only one field from the second row).

I understand the reason I cannot do this, as it can be deemed to be a many to many relationship in which case it will pose a problem. However the way that the db is setup means that we won't retrieve duplicate data.

eg.
Table Test (Primary Key on ID and Flag)

ID Flag Value
1 A 10
1 B 20
2 A 30
2 B 25
3 A 40
3 B 35

Query along the lines of:
SELECT t1.ID, t1.Value as [Value1], t2.Value as [Value2]
FROM Test t1
INNER JOIN Test t2 ON t1.ID = t2.ID AND t2.Falg ='B'
WHERE t1.Flag = 'A'

Results:
ID Value1 Value2
1 10 20
2 30 25
3 40 35

When binding this recordset to the contiuous form datasource I wont be able to update the values.

I've looked at unbinding the data but this seems to be extroadinarily difficult(if not impossible) with Access continous forms. Previously I only needed to display the Value2 data, so I used a user defined function to get the data, but now I need to be able to update it as well as Value1.

N.b. The same functionality occurs when trying this is in Enterprise Manager which makes me think there may be a SQL solution somehow.

Any ideas would be greatly appreciated.
Cheers

Hi

I have spent literally weeks trying to circumvent the 'blank' form issue when forms/subforms have zero records. How on earth is this supposed to work??

When I add a filter to a form and the form returns 0 records, I set allowadditions=true on my mainform; this prevents subsequent vba references to the subform (and/or its controls) from failing.

I store a given user' s filter/orderby settings in a table and when next opening the form, my form-open event applies the latest filters and orderby params. However, now when there 0 records returned and, again, I set allowadditions=true on my mainform, subsequent references to the subform do fail? (I think this is because the subform has not been opened?). This only happens when there is also a particular type of OrderBy specified i.e. if the user has elected to sort on a combobox control which is populated by a rowsource = "SELECT blah blah" statement. When these are added to the OrderBy an entry similar to that shown below appears in the form's OrderBy property:-

	Code:
	[Lookup_Parent__Table__Unique__No__Combobox].[Category] DESC

These orderBys work quite happily when records are returned.

This is how the combo is populated:-

	Code:
	SELECT TBL1.Friendly_Name As Category,
TBL1.Date_Record_Added,
TBL1.Added_By_Unique_No,
TBL1.Date_Record_Last_Updated, 
TBL1.Last_Updated_By_Unique_No 
FROM Table_Names AS TBL1 WHERE (TBL1.Deleted=False) 
ORDER BY TBL1.Added_By_Unique_No

It seems to me that the only way to get around this is to force the subform open again whenever a 0 records condition is found... perhaps with some 'dummy' data? but how would I do that?? I certainly don't want any 'dummy' records displayed in the subform.

How would you the experts handle this please?

Guys,
We are working on a complex database, based on continuous forms that seems to have issues with screen flickers and form refreshs when ever something needs processing, is changed or relates to code.
The functionality is fine but it looks less professional than you would expect because everytime you tab out of feilds or change filter criteria etc everything flashes and flickers.
Anyway to fix this compleatlly?
Some forms are worse than others but its pretty constant across the database.
Thanks
KG


Not finding an answer? Try a Google search.