Easy method for showing text, not IDs in reports?
Easy method for showing text, not IDs in reports?
Hi,
Somehow, I have managed to create a database with a main table and 4 related tables with drop down lists;)
The issue is that the report is all numbers. Is it possible to just use the text names from the beginning or a way to have the report translate the ID numbers into text for the report?
I have to say that I'm not even sure what purpose the numbers serve...I was just following a tutorial which didn't explain. And I don't understand most of the terminology that is used here.
Btw, I am unable to install LibreOffice on this computer atm --- I am using an older OSX for some vital older software. Usually I just create spreadsheets but have been wanting to learn databases.
Thanks for any help!
Somehow, I have managed to create a database with a main table and 4 related tables with drop down lists;)
The issue is that the report is all numbers. Is it possible to just use the text names from the beginning or a way to have the report translate the ID numbers into text for the report?
I have to say that I'm not even sure what purpose the numbers serve...I was just following a tutorial which didn't explain. And I don't understand most of the terminology that is used here.
Btw, I am unable to install LibreOffice on this computer atm --- I am using an older OSX for some vital older software. Usually I just create spreadsheets but have been wanting to learn databases.
Thanks for any help!
Open Office 4.1.10
Mac OSX 10.12.6
Mac OSX 10.12.6
Re: Easy method for showing text, not IDs in reports?
It is hard to say what you need to do without knowing more about what is in the database. Can you upload it here? To upload a file, click Post Reply and look for the Attachments tab just below the box where you type a response. Uploading the file will only be useful if you are not connecting the Base file to an external data source such as a standalone database.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Easy method for showing text, not IDs in reports?
Here it is, thanks:)
- Attachments
-
- sales-per-shop-per-item.odb
- (40.84 KiB) Downloaded 359 times
Open Office 4.1.10
Mac OSX 10.12.6
Mac OSX 10.12.6
Re: Easy method for showing text, not IDs in reports?
I has been many years since I used forms and reports in OpenOffice, so I can give you only limited help.
Here is a query that returns all the information in your main table but with the ID numbers converted into the corresponding text in the linked tables.
Click the Queries icon in the left Base pane and the select Create Query In SQL View. Paste the above code into the blank query and run it to see the result. Is that the sort of display you want?
Here is a query that returns all the information in your main table but with the ID numbers converted into the corresponding text in the linked tables.
Code: Select all
SELECT "M"."SPSPMID", "P"."Product Type", "Sh"."Shop", "M"."Value", "M"."Date", "M"."Notes", "Mn"."Month", "Seas"."Season", "M"."Year"
FROM "Sales Per Shop Per Month" AS "M"
INNER JOIN "Product Type Table" AS "P" ON "M"."Product Type" = "P"."Product Type ID"
INNER JOIN "Shops Table" AS "Sh" ON "M"."Shop" = "Sh"."Shop ID"
INNER JOIN "Month" AS "Mn" ON "M"."Month" = "Mn"."MonthID"
INNER JOIN "Season" AS "Seas" ON "M"."Season" = "Seas"."Season ID"
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Easy method for showing text, not IDs in reports?
Thank you so much. Yes, that's what I was looking for and now I understand it more.
This is basically putting the information into a spreadsheet format, I guess. I had imagined that once I had a database set up, I could sort and display the data in a number of ways without too much effort but now I am thinking that it will require much more knowledge.
This is basically putting the information into a spreadsheet format, I guess. I had imagined that once I had a database set up, I could sort and display the data in a number of ways without too much effort but now I am thinking that it will require much more knowledge.
Open Office 4.1.10
Mac OSX 10.12.6
Mac OSX 10.12.6
Re: Easy method for showing text, not IDs in reports?
Yes, databases are much harder to learn and use than speardsheets but very much better in many ways. You actually made a very good start if that is your first database. There are some suggestions I would make if you are going to continue working on it.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Easy method for showing text, not IDs in reports?
Thank you for the encouragement! I would love your suggestions.
Open Office 4.1.10
Mac OSX 10.12.6
Mac OSX 10.12.6
-
- Posts: 318
- Joined: Sun Sep 06, 2020 8:27 am
Re: Easy method for showing text, not IDs in reports?
For the MacOS 10.12 your signature shows, the latest possible Version is 7.3 wich can be found in the archives linked from the download page.
https://wiki.documentfoundation.org/Doc ... quirements
OpenOffice 3.1 on Windows Vista
Re: Easy method for showing text, not IDs in reports?
None of this is a big deal.
1. I don't see the need to have a MonthID column. The table structure of an ID and separate columns of text values is used when when you want to consistently correlate to an entity but some of the details about the entity may change. So, you give a person an ID and have separate columns for the name and perhaps other facts. The name may change but the ID does not. But month names are not likely to change. You can still have a table to limit the values entered into forms but you can use a combo box and directly enter the month.
2. Is the season completely determined by the month? If so, I would add a Season column to the Month table and drop the Season table. Otherwise, there is a danger of the month and season not matching.
3. I would name the columns in the main table that store ID values to match the ID column in the linked table. So, Sales Per Shop Per Month would have a column named Product Type ID, not Product Type and the join condition would be
That is clearer in its intent, I think.
If the names match, in some database engines (not the built-in one for Base, I think) you could write
4. I would not have spaces in table and column names; use underscores instead. You can often get away with skipping the double quotes around names if they have no spaces or special characters. Lazy of me, I know.
1. I don't see the need to have a MonthID column. The table structure of an ID and separate columns of text values is used when when you want to consistently correlate to an entity but some of the details about the entity may change. So, you give a person an ID and have separate columns for the name and perhaps other facts. The name may change but the ID does not. But month names are not likely to change. You can still have a table to limit the values entered into forms but you can use a combo box and directly enter the month.
2. Is the season completely determined by the month? If so, I would add a Season column to the Month table and drop the Season table. Otherwise, there is a danger of the month and season not matching.
3. I would name the columns in the main table that store ID values to match the ID column in the linked table. So, Sales Per Shop Per Month would have a column named Product Type ID, not Product Type and the join condition would be
Code: Select all
ON "M"."Product Type ID" = "P"."Product Type ID"
If the names match, in some database engines (not the built-in one for Base, I think) you could write
Code: Select all
USING "Product Type ID"
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Easy method for showing text, not IDs in reports?
Thank you! That's great to know.Mountaineer wrote: ↑Thu Jan 11, 2024 11:49 pmFor the MacOS 10.12 your signature shows, the latest possible Version is 7.3 wich can be found in the archives linked from the download page.
https://wiki.documentfoundation.org/Doc ... quirements
Open Office 4.1.10
Mac OSX 10.12.6
Mac OSX 10.12.6
Re: Easy method for showing text, not IDs in reports?
Yes! It was just a matter of wanting a drop down. And thank you for very helpful the ID explanation.FJCC wrote: ↑Fri Jan 12, 2024 12:06 am None of this is a big deal.
1. I don't see the need to have a MonthID column. The table structure of an ID and separate columns of text values is used when when you want to consistently correlate to an entity but some of the details about the entity may change. So, you give a person an ID and have separate columns for the name and perhaps other facts. The name may change but the ID does not. But month names are not likely to change. You can still have a table to limit the values entered into forms but you can use a combo box and directly enter the month.
The season is just a back up because sometimes I receive payments that cover a few months...still working on how best to organize that info.
Currently trying to get my head around Parts 3 + 4. I'm sure I can do it. Thank you so much.
Open Office 4.1.10
Mac OSX 10.12.6
Mac OSX 10.12.6