join Query

[fantilator]

I have a database for networking devices and cable number withs ports etc..



basically I have 2 tables:

TBL_Devices:
-DeviceID
-Device_Tag
-Device_Location
-Device_Sublocation

TBL_Device_Ports:
-Ports_Cable_Number
-Ports_nr
-Port_Broken
-Port_Device_ID

With 1-many relation on DeviceID <==> Port_Device_ID

I have created a query that outputs following:

DeviceTag	Device_Location	Device_Sublocation	Ports_Cable_Number	Ports_nr
SW1	ROOM1	CAB1	1212	FA0/1
SW3	ROOM1	CAB5	1212	25
RTR6	ROOM6	CAB3	10365	GI0/0/3
SW1	ROOM1	CAB1	10365	FA0/2

I'd like to have a query that outputs following:

Ports_Cable Number	Devicetag1	Device_location1	Device_Sublocation1	Port_NR1	Devicetag2	Device_Location2	Device_Sublocation2	Port_NR2
1212	SW1	ROOM1	CAB1	FA0/1	SW3	ROOM1	CAB5	25
10365	RTR6	ROOM6	CAB3	GI0/0/3	SW1	ROOM1	CAB1	FA0/2

Any Idea how I can solve this matter, as I cannot find anything like this on the net.

[pbaldy]

I think this could be adapted to return 4 fields instead of 1:

http://allenbrowne.com/func-concat.html

[fantilator]

Thank you for the suggestion, do you think it will also update the query when an external program (Visio) is asking the query?

I'm pretty sure I can change the VBA code to return 4 fields, but I'm not sure is self made functions support external requests.

[pbaldy]

Sorry, I don't know the answer to that. You're saying Visio would be executing an Access query? I would think it would still work if the query was executing inside Access, but I can see why it might not. Try with a simple VBA function called from a query.

[fantilator]

In Vision you can link shapes to data, and do some formatting of the shapes based on that data.

I have tried adding a function to my query and the query is not visible in Visio.
So the VBA solution does not work unfortunately.

It should be done by SQL.