I have a database with a main table (One) and satellite tables(many) all related in one to many relationships. Access won't let me make a query from all these tables so that I can create a report. I had the same problem creating a data entry form, but got around that by writing it in VB6. I need to be able to create a report straight out of Access. Any ideas anyone?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
One to many relationship - Reports??
- Thread starter Imbriani
- Start date
Access will, unless you have more than the maximum number of tables in a query. Indeed Access will go on a lot longer before it gets bored than the person you are writing the report for. Reports should be easy to understand.
So what are you trying to do exactly. (or give us any detail whatsoever)?
So what are you trying to do exactly. (or give us any detail whatsoever)?
- Thread starter
- #3
I have an Access database that collects data entered from an online form off the internet. I have to take that same database (tables) and manipulate the data in Access for reports, etc. There are seven tables, one main table (Fumigation) and six satellite tables (commodity, readings, origin, etc.). These are related in one to many relationships, the Fumigation table being the one and all the tables being manys. When I try to create a query using these tables, Access will only let me include in the query the main table and ONE satellite table. No more, no matter what I do. In order to use this data ideally, I need to be able to create a query that includes ALL the tables so that I can create a data entry sheet, reports, etc.
I haven 't been able to find out definitively whether or not Access will allow querying, etc when tables are related in this manner. I guess that's my first question, is this even possible and under what conditions.
I haven 't been able to find out definitively whether or not Access will allow querying, etc when tables are related in this manner. I guess that's my first question, is this even possible and under what conditions.
How are you 'including' tables. Are you doing the query in the Query window using QBE or are you writing SQL in the Query window or are you writing VBA etc?
Just while I'm waiting, if you go into ther query window ans select Fumigation and two other tables, then draw join lines, then select some fields, then press the button, what message does it give you to tell you why you have only got two tables in the query?
Just while I'm waiting, if you go into ther query window ans select Fumigation and two other tables, then draw join lines, then select some fields, then press the button, what message does it give you to tell you why you have only got two tables in the query?
- Thread starter
- #5
I have tried to create the query in both the Query window using QBE and by writing SQL. In the QBE, I select the Fumigation table (the main one - one relationship) and then ADD another table and another table. When I click the Red exclamation point, I get a closed dynaset, not updateable. If I delete one of the last two tables, I get an open dynaset that is updateable. My original task with this was to create a query including all seven tables upon which to base a data entry form. When I discovered I couldn't do that, with some good advice from here, I designed a form using subforms for data entry that works. However, now I need to do reports querying information from all the tables and I have found that I cannot. Folks here at work who are supposed to be well versed in Access say that this type of query should be possible, and no one seems to be able to tell me why it won't work.
- Thread starter
- #7
Yes, the tables are all joined in one to many relationships, the tblFumigation being the one and the other tables being manys. Sort of like a bicycle wheel with tblFumigation in the center and the satellite tables around like spokes (make sense?). Every satellite table has a unique identifier of its own and lngFumigationID as a foreign key. lngFumigationID is the primary key for tblFumigation. Every table is joined at the same place on tblFumigation and I'm wondering is this is possible since I can't find an example of such a join.
Here's the SQL:
SELECT tblFumigation.strReportingCity, tblFumigation.strReportingstate, tblFumigation.dtArrivalDate, tblFumigation.dtInterceptDate, tblFumigation.dtConfirmDate, tblFumigation.dtFumOrderDate, tblFumigation.dtFumDate, tblFumigation.strPestOrder, tblFumigation.strPestFamily, tblFumigation.strPestGenus, tblFumigation.strPestSpecies, tblFumigation.strEANSerial, tblFumigation.strtrtfumigant, tblFumigation.strtrtschedule, tblFumigation.strtrtdosage, tblFumigation.strtrttime, tblFumigation.strtrttemp, tblFumigation.IsExempt, tblFumigation.IsFood, tblFumigation.IsResidue, tblFumigation.intspacetemp, tblFumigation.intcommoditytemp, tblFumigation.strgas, tblFumigation.strenclosure, tblFumigation.strContracter, tblFumigation.strInterceptionID, tblFumigation.strFumigationSite, tblFumigation.strQuantity, tblFumigation.strCarrier, tblFumigation.strArrival, tblFumigation.strlading, tblFumigation.strinspectorID, tblFumigation.strweather, tblFumigation.strcapacity, tblFumigation.lngfans, tblFumigation.lngfancfm, tblFumigation.strgasunits, tblFumigation.strfantimes, tblFumigation.strgasstart, tblFumigation.strgasfinish, tblFumigation.strgasintro, tblFumigation.strgasadded, tblFumigation.Isapproved, tblFumigation.stremail, tblFumigation.strRemarks, tblFumigation.strCalculations, tblFumigation.IsDone, tblFumigation.strsamplenumber, tblFumigation.IsMorePests, tblFumigation.lngReasonID, tblFumigation.strInspector, tblFumigation.strInspectorBadge, tblFumigation.strReviewer, tblFumigation.strReviewerBadge, tblFumigation.lngFumigationID AS tblFumigation_lngFumigationID, tblFumCommodity.strCommodity, tblFumCommodity.lngFumigationID AS tblFumCommodity_lngFumigationID, tblFumCommodity.lngFumComID, tblFumOrigin.strOrigin, tblFumOrigin.lngFumigationID AS tblFumOrigin_lngFumigationID, tblFumOrigin.lngFumOriginID
FROM (tblFumigation INNER JOIN tblFumCommodity ON tblFumigation.lngFumigationID = tblFumCommodity.lngFumigationID) INNER JOIN tblFumOrigin ON tblFumigation.lngFumigationID = tblFumOrigin.lngFumigationID;
Here's the SQL:
SELECT tblFumigation.strReportingCity, tblFumigation.strReportingstate, tblFumigation.dtArrivalDate, tblFumigation.dtInterceptDate, tblFumigation.dtConfirmDate, tblFumigation.dtFumOrderDate, tblFumigation.dtFumDate, tblFumigation.strPestOrder, tblFumigation.strPestFamily, tblFumigation.strPestGenus, tblFumigation.strPestSpecies, tblFumigation.strEANSerial, tblFumigation.strtrtfumigant, tblFumigation.strtrtschedule, tblFumigation.strtrtdosage, tblFumigation.strtrttime, tblFumigation.strtrttemp, tblFumigation.IsExempt, tblFumigation.IsFood, tblFumigation.IsResidue, tblFumigation.intspacetemp, tblFumigation.intcommoditytemp, tblFumigation.strgas, tblFumigation.strenclosure, tblFumigation.strContracter, tblFumigation.strInterceptionID, tblFumigation.strFumigationSite, tblFumigation.strQuantity, tblFumigation.strCarrier, tblFumigation.strArrival, tblFumigation.strlading, tblFumigation.strinspectorID, tblFumigation.strweather, tblFumigation.strcapacity, tblFumigation.lngfans, tblFumigation.lngfancfm, tblFumigation.strgasunits, tblFumigation.strfantimes, tblFumigation.strgasstart, tblFumigation.strgasfinish, tblFumigation.strgasintro, tblFumigation.strgasadded, tblFumigation.Isapproved, tblFumigation.stremail, tblFumigation.strRemarks, tblFumigation.strCalculations, tblFumigation.IsDone, tblFumigation.strsamplenumber, tblFumigation.IsMorePests, tblFumigation.lngReasonID, tblFumigation.strInspector, tblFumigation.strInspectorBadge, tblFumigation.strReviewer, tblFumigation.strReviewerBadge, tblFumigation.lngFumigationID AS tblFumigation_lngFumigationID, tblFumCommodity.strCommodity, tblFumCommodity.lngFumigationID AS tblFumCommodity_lngFumigationID, tblFumCommodity.lngFumComID, tblFumOrigin.strOrigin, tblFumOrigin.lngFumigationID AS tblFumOrigin_lngFumigationID, tblFumOrigin.lngFumOriginID
FROM (tblFumigation INNER JOIN tblFumCommodity ON tblFumigation.lngFumigationID = tblFumCommodity.lngFumigationID) INNER JOIN tblFumOrigin ON tblFumigation.lngFumigationID = tblFumOrigin.lngFumigationID;
OK I'm kind of thinking you can't do that, or at least it'll never produce what you are looking for.
SQL always produces a 'table', so you have to think what that would look like when you have two (or seven) one-to-many collections. How does SQL line up the second one-to-manys with the first set? What you'll get is a sort cartesian cross product. Lets say there were two primary records. The second table has 3 values for each so you then have 6 rows. Now add the 3rd table with 3 rows for each primary row. Because SQL has no idea which of the scond table rows apply, when it brings this in, it needs to put each of these rows against each of the existing rows (for each primary record). So you now have a jumble of 18 rows ie with lots of duplication of the second table. Jet then may have difficulty letting you update the recordset.
Truth is I can't remember doing this although I can't see why it is not a common problem. Maybe someone else can see quicker than me what's going on here.
SQL always produces a 'table', so you have to think what that would look like when you have two (or seven) one-to-many collections. How does SQL line up the second one-to-manys with the first set? What you'll get is a sort cartesian cross product. Lets say there were two primary records. The second table has 3 values for each so you then have 6 rows. Now add the 3rd table with 3 rows for each primary row. Because SQL has no idea which of the scond table rows apply, when it brings this in, it needs to put each of these rows against each of the existing rows (for each primary record). So you now have a jumble of 18 rows ie with lots of duplication of the second table. Jet then may have difficulty letting you update the recordset.
Truth is I can't remember doing this although I can't see why it is not a common problem. Maybe someone else can see quicker than me what's going on here.
- Thread starter
- #9
These tables were set up by someone else and I"ve inherited the problem. Any suggestions on how this SHOULD have been done? The programmer that did this said that only data pertaining to the unique ID for each record, say it's record 122, would be pulled out of each table. I see what you're saying and it makes sense, but I, too, wonder why this isn't a common problem.
I did notice in trying to solve this that in many of the examples I found on table relationships, that the tables were sort of "daisy-chained" together. For instance, table one has it's unique ID, NumID, and the next table has NumID as it's foreign key and it's own ID, Table2ID and so on through all the tables. Is there something about this sort of relationship that's different from what I have?
I did notice in trying to solve this that in many of the examples I found on table relationships, that the tables were sort of "daisy-chained" together. For instance, table one has it's unique ID, NumID, and the next table has NumID as it's foreign key and it's own ID, Table2ID and so on through all the tables. Is there something about this sort of relationship that's different from what I have?
If you join A to B to C to D to E, then there's no problem. Joining A to B and A to C and A to D is going to cause problems. Just at the minute I'm not sure why Access/Jet is complaining. It should be able to run the query. Whether the query wont allow update because Jet can't do it or because it is theoretically impossible to do, I'm stuck on at the moment.
The solution? I think you want several one-to-many 'displays' aligned by one common key. SQL cannot do this in one query. Certainly I think you are going to have to start with up to 7 queries. How you get them to display is beyond my knowledge of what you can do with Access reports.
The solution? I think you want several one-to-many 'displays' aligned by one common key. SQL cannot do this in one query. Certainly I think you are going to have to start with up to 7 queries. How you get them to display is beyond my knowledge of what you can do with Access reports.
To display several different "many" sides, use subreports. For instance, if you have tables of:
[blue]
[/blue]
The favorite colors aren't related to foods but they are both related to people. If a person can have more than one favorite food and more than one favorite color then you use forms with subforms or reports with subreports. Subforms and Subreports have Link Master/Child properties which would be set the the field [PeopleID].
Duane
MS Access MVP
Find out how to get great answers faq219-2884.
[blue]
Code:
tblPeople
===========
PeopleID
LastName
FirstName
tblFavoriteColors
===========
PeopleID many related to tblPeople
Color red, green
tblFavoriteFood
=============
PeopleID many related to tblPeople
Food potatos, Cake, Cookies, ...The favorite colors aren't related to foods but they are both related to people. If a person can have more than one favorite food and more than one favorite color then you use forms with subforms or reports with subreports. Subforms and Subreports have Link Master/Child properties which would be set the the field [PeopleID].
Duane
MS Access MVP
Find out how to get great answers faq219-2884.
- Thread starter
- #12
I had no idea there was such a thing as subreports! I suppose I should have assumed there was. Are subreports and subforms Access' way of dealing with a situation like this, when tables are related in one to many relationships? Thanks for all your help. I'll try the subreport method and see how that works!
Kathy
Kathy
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question