Grouping a report with 2 fields from 2 tables

[Alexander S.]

Hello Community!

I'm trying to create a report with data from 7 different tables. It's supposed to be an assignment overview for a transfer company (driving people from A to B, dunno how to call it in English). I put assignment and customer details into the page header, which is working out fine so far. But now I want to show transfer details and it's proving to be a bit troublesome.
When creating the assignment, the user can choose whether the transfer goes from/to an address or from/to an airport. Depending on which one he chose the data is stored in different tables. To determine whether it is an outward journey/collection, outward journey/target, return journey/collection or return journey/target, I put a field in each of those tables ("Schritt", its value being either 1, 2, 3 or 4).

To visualize I made screenshot of the tables in relationship view, but since I'm a new member i can't seem to post images, so I'm giving you a pseudo URL. Hope that's OK
[abload (dot) de (slash) img (slash) transeren40p1r.png]

The final report should look like this:

outward journey
---collection (1)
---target (2)
return journey
---collection (3)
---target (4)

Having the "Schritt" value for each step in either one or another table (address(es) or flight data) is making it a bit hard for me to wrap my head around the problem. Is that doable with expressions or is there a way in VBA to solve this problem?

Thanks for your time and help.

Cheers,
Alex

 

[llkhoutx]

Use subreports to show what you want in a report.

 

[spikepl]

Depending on which one he chose the data is stored in different tables.

We stop right there. Similar stuff always goes into the same container - google and assimilate the concept of database normalization. Otherwise you have to know in which container to look for a particular instance of data, and that is simply not supportable in the long run.

Show a screenshot of the Relations window. You need to fix the structure before anything else.

 

[Alexander S.]

Other message boards were giving me similar advice. I didn't even notice how many similar fields I had in those 2 tables. A case of tunnel vision, i guess.

Before (with english explanations - copy and paste the links, can't post URLs)
abload.de / img / transeren40p1r.png

After
abload.de / img / transfer2sos2r.png

Depending on whether it is an adress or an airport transfer there are 1 or 3 fields being left open, but other than that it looks a lot more clean in my eyes. Do you see anything else that needs fixing?

 

[spikepl]

A case of tunnel vision, i guess.

Nope. Beginning Access fun.

Please zip your pix and attach them here (that works even for a low post count) The reason is that also subsequent readers can then fully enjoy the fruits of our labour.

.Please don't cross-post without providing links to your cross-posts. We answer questions for free. Please don't waste the time of the people helping you.
For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184

 

[Alexander S.]

Didn't know about the cross-posting etiquette, just thought it would raise the probability of getting a good answer. I posted on german forums though. If you're interested, here are the links:
www . ms-office-forum . net / forum / showthread.php?p=1624924
www . office-loesung . de / p / viewtopic.php?f=167&t=672267

I attached the screenshots. Hope everything's fine now!

 

[spikepl]

You have a good naming convention. But take care to do it out consistently: FahrerID should link to FahrerID, not to Fahrer (like you have implemented elsewhere). Datum in Access contains date and time - splitting Uhrzeit out is not really necessary.

As to the essential rest? I do not know enough about your business. Is it always a transfer between SomeAdress and an Airport? I.e. from SomeAdress to an Airport, or from an Airport to SomeAddress? No other possibilities/combinations?

 

[Alexander S.]

Yes, there are other combinations. The ones you stated are correct, then there is transfers from address to address and sometimes but rather seldomly there are round trips. Most of the time it's really just addresses and airports.

Here is how it works: "tblAuftrag" contains general assignment details, "tblAdFlgDaten" contains transfer details and connects to addresses/airport details, while the field "Schritt" specifies if it is a pick-up or destination ('1' and '2' for outwards, '3' and '4' for return). If you have to pick up more than one person from different addresses you can just have multiple records with the same Schritt-Value. I attached a picture showing what it looks like when filled out. If it is from/to an address the airport-related (FlughafenID, FlgMAus, FlgNr) fields stay open and vice versa (AbBrAdID).

So what i want my report to do is show those transfer details ordered by "Schritt", looking something like this:

=========================================
Outward Journey - Pick-Up (Schritt=1)
=========================================
[Passenger Details] (PassgID)
pick up from [Address Details] (AbBrAdID)
at Date and Time
=========================================
Outward Journey - Destination (Schritt=2)
=========================================
bring [Passenger Details] to 
[Airport Details] (FlughafenID)
at Date and Time
=========================================
Return Journey - Pick-Up (Schritt=3)
=========================================
...

I've tried to create the report. I did it in design view, dragged assignment and customer details into the header section and fields from everything connected to "tblAdFlgDaten" into the detail section. It would show the data just fine, like everything connected to the assignment, until I dragged a field from "tblFlughafen" into the report, suddenly it doesnt show any data at all. I'm rather confused as there seems to be no obvious reason to it.

I'm sorry for being such a newbie and writing lenghthy posts. Your help is much appreciated <3