Joined: 10 Jan 2007
Location: Oregon, USA
|Posted: Wed Jan 10, 2007 2:46 pm Post subject: newbie - database backend
|Hi folks, I'm new to this forum.
My question is about the database and its integration with the rest of the office suite, particularly calc. I'm attempting to create a database with some customer information in it, and import that information, live if possible, into calc spreadsheets. This has about as much to do with calc as it does with base, so I'm posting it here unless somebody thinks its better off in another forum.
So far, with the help of various links found on this forum, I've managed to create a simple database with 1 table and 3 fields: Customer ID, Last Name, and Tel. Code. I assume that I can add more later if necessary. I've also been able to import portions of the database into calc, using the Data ---> Datapilot --> Start menu item, selecting my database and table, and dropping the part I want into the spreadsheet.
This is where things get a bit interesting for me. What I'm trying to accomplish is having a customer list that is the same over multiple spreadsheets - update the db, spreadsheets get updated too. Here's an example of my problem: I have a spreadsheet where we keep all of our mail costs, per customer, which we bill back to them monthly. Often, our customer list changes, so I end up having to edit that, and other spreadsheets to keep it current. I drop the 'Last Name' part of the database column on the spreadsheet, and then have a long row for cost data-entry, then add up the sum at the end. Now, when I go and add a new customer to the list, it changes the size of the list of names, and the row of data doesn't correspond with the customer name anymore, since the name gets shifted up or down. This is bad, obviously, since I can't add customers without risking them getting charged for other people's costs.
So, if you haven't fallen asleep yet, here's my questions
1. How can I "lock" the row of data-entry and calculations to the last name column, or vice-versa? So, if I add 2 new customers to the database, sort the name column alphabetically (easier to search through), the data sticks with the correct customer.
2. Is the Data---> Datapilot route the best way to go about getting the information out of the database and into the spreadsheet? I've seen a couple of other ways to basically get the same thing done, and don't know which is going to be "right".
3. (wishful thinking) is there a way to make the Last Name column "sticky" so it always shows on the left side of the screen, no matter how far to the right you scroll? This would be nice, since no matter how many entries you have, you'd be able to see that you're on the right row.
3. Is my head screwed on straight? Really! I can work my way around a spreadsheet OK with the basic stuff, but this is my first foray into using a db as an information backend. I've searched through most of the documentation on this site and in the forum, but haven't come up with anything that closely resembles my issue. If I'm looking at the problem from a completely backwards/inverted/upside-down perspective, please let me know!
Thanks in advance,