I want to create 7 databases, one for each day of the week. Each day of the week has about 20 unique columns, but they all use the same column which has time values (e.g. 1:00, 1:30, 2:00, etc). Can I define this database range which has 2 separate ranges?
To clarify, my common column (with time fields- DBTIME) is A2:a31. My Monday range (DBMON) is Q2:AD31. I want the cells in column A appended to the left of my Monday range to do a DGET function. Same for other days which have similar row numbers but different columns. When I use DGET(DBTime;DBMon,1,criteria), the DGET searches everything from column a through AD and I get the wrong result. I don’t want it so search columns B-P. Thanks.
- A spreadsheet is not a database. It is a cheap surrogate at best.
- Seven tables for each day of week is a serious mistake just like one sheet per client, one sheet per article, one sheet per month. This is not how databases work. It is plain wrong design even if the “database” is just a spreadsheet.
Use a proper database engine designed for the task of managing that data and query for the day to get a result set that you either copy/paste into Calc, or else call into Calc via the limited Calc-specific database functions.
Thanks for the comment. I do understand that spreadsheets are not databases. Just using the terminology (DGET) in Libre office and other spreadsheets to use a simple lookup function which is readily available in MS Excel, but of course I guess you get what you pay for in Libre Office. Sounds like you’re not a fan of Libre office either…
My design was for simple purposes originally, but over a couple of years my requirements have expanded and may have outgrown my design. May be time to re-think this but I really don’t that the time for a re-design.
True for M$, where you pay extra for the Database (Access), and as many people don’t have Access in their paid plan, Word and Excel need to include some database-functions to make for exampme mail-merge working without database.
.
On the other side LibreOffice is much more integrated, so a form for the database is a special Writer-dokument and mail-merge is realized with help of an odb/database-file, as a database-connection to external Databases and support for HSQLDB, Firebird, dBase, Text/csv-Files is already included…
.
He is no fan of using Calc or Excel as “poor man substitute for database”, and considering the price of LibreOffice one may replace poor with lazy…
.
Simple would be: Use one table.
In a spreadsheet you could filter in the table, filter days to another sheet or for statistics often pivot-tables wil be a possibility. In a database, if you really need this you could have 7 queries to extract data from the master-table, if this separation is necessary…
PS: By the (complex) rules of this site, please don’t use “answer”, if your post is not an answer to the question wich usually is the starting-point to a thread on this “ask”-site. (It’s a bit different from a forum.)