Categorising a report

Getting your data onto paper - or the web - Discussing the reports features of Base
Post Reply
Fishfarmer
Posts: 4
Joined: Sat Aug 24, 2013 2:20 pm

Categorising a report

Post by Fishfarmer »

HI

I am building a database of potential suppliers for a project.
I want to generate a report which lists the suppliers according to what they supply.

So I would like to have a heading saying Generators and then a list of companies below that who supply generators.
I know how to do this but the problem is that some of the suppliers supply more than one thing.

So say company A supplies generators, lights and cameras and company B supplies just lights
I would like company A to appear under the separate headings, Generators, Lights, Cameras and company B to appear only under Lights.

Unfortunately when making the table I am using for this I made a field called equipment and listed the equipment the companies provides in that field. Now Base only lists multiple companies if the equipment field is exactly the same.
So I have fields that look like

Cameras, Lights, Generators
Company A
Company C

Cameras
Company B

Lights
Company X,

Etc

I dont know if I am explaining myself properly at all but I want there to be one heading for each piece of equipment and the companies that provide that to appear there and if they provide multiple pieces of equipment to appear under multiple headings

Thanks very much
Open office 4.0.0. Windows XP
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Categorising a report

Post by Arineckaig »

One of the fundamental principles of a Relational Database is that any field in a record should only hold one indivisible piece of information. If I understand it correctly your the equipment field in your database sometimes holds multiple entries. There would appear to be a many-to-many relationship between 'companies' and 'equipment'. Thus, the RDMS normalisation rules indicate a need for three separate tables: first a table for 'companies', secondly a table for 'equipment', and thirdly a linking table (say 'CoEqip') which contains at least two Foreign key fields that respectively link to the Primary key fields in each of the other two tables.

The design of the database can then permit a listing of each item of equipment and all companies that supply it, or equally a listing of each company and all the equipment it supplies.

Please come back if you have problems or wish a reference to tutorials on the subject. Meanwhile I have attached a crude demo file to illustrate the principle where there is comparable many-to-many relationship between 'Clients' and 'Trips'.
Attachments
ClientsTripsMany2ManyDemo.odb
(28.53 KiB) Downloaded 233 times
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
Fishfarmer
Posts: 4
Joined: Sat Aug 24, 2013 2:20 pm

Re: Categorising a report

Post by Fishfarmer »

Thanks very much
I'll have a look through that. If you could point me towards any (dumbed down) tutorials that can help to, I'd be very grateful!
Open office 4.0.0. Windows XP
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Categorising a report

Post by Arineckaig »

I would suggest the tutorial that can be downloaded from:
https://wiki.documentfoundation.org/ima ... torial.pdf
At 189 pages it may seem daunting, but its key feature is the emphasis it places on the design of a database before moving on to creating forms and reports.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
Post Reply