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
Categorising a report
-
- Posts: 4
- Joined: Sat Aug 24, 2013 2:20 pm
Categorising a report
Open office 4.0.0. Windows XP
-
- Volunteer
- Posts: 828
- Joined: Fri Nov 30, 2007 10:58 am
- Location: Scotland
Re: Categorising a report
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'.
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
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
-
- Posts: 4
- Joined: Sat Aug 24, 2013 2:20 pm
Re: Categorising a report
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!
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
-
- Volunteer
- Posts: 828
- Joined: Fri Nov 30, 2007 10:58 am
- Location: Scotland
Re: Categorising a report
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.
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
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB