Argh! Boring database question!

you want to use:

Descendants(USA, Counties)

that's going to list every county... basically skipping the state level and iterating down to the lower children level you want.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmad/agmdxfunctions_5iib.asp

does that help?
m.

MDX is a real pain in the arse to learn, I expect it's really quite simple once you know it. I'm looking for a new job though so it's in my interest to practise it, especially since I put it on my CV!

m.

I want to do this:

WHERE [CURRENT_MONTH].[YES]

but that returns everything, not just where current_month = YES, for some reason.

But I can get round that by putting this in the select list:

{[CURRENT_MONTH].[YES]} on axis(4)

cos then it does just return where current_month = yes, I just have an extra unnecessary column in the results, but that doesn't matter.

m.

fuck.

do any of you ever have to try and manipulate sharepoint lists using access? pain in the ASS. fuck.

do any of you ever have to try and manipulate sharepoint lists using access? pain in the ASS. fuck.

you've crossed out the wrong thing - sharepoint seems to be access x 398493343

dude, Access?

You mean SQL?

haha no. i'm trying to force some kind of thing to happen between access and sharepoint (basically update a sharepoint list from something in an access table!!!! wtf?!?!?!?) and it basically is impossible. at least in access2003 .. it lets you link a sharepoint list but it's unreliable as shit. update queries are hopeless, the most successful way ended up being to use ADO recordsets which is still crappy as hell it's like technology had suddenly gone backwards 20 years. stuff will fail without warning or error messages.

any other suggestions welcome. this is something i'm trying to do at work unfortunately i don't have much tools available apart from bog standard office 2003 and some version of WSS

Why not just migrate the Access interface to front the SQL Server table in Sharepoint you want to update in the first place?

Noob question: I have two tables I want to join, main and sub. Main has a bunch of records, sub is empty. I try to do:

SELECT main.* FROM main, sub WHERE main.id = '123'

Even though main has a record whose id is 123, I still get zero records back. Do I need an additional condition in WHERE clause?

Using SQL Server? Try this:

(Assuming that main.id is the primary key that matches up with primary key sub.id in table sub...)

SELECT * from main
INNER JOIN sub ON main.id = sub.id
WHERE main.id = '123'

You might also need to take 123 out of single quotes if it's an actual number rather than a string.

ILX, I have a boring computer question that involves tables exported out of a spreadsheet, and I hope you can help.

What I have are competition standings for livestock exhibitors. The spreadsheet used to have the kids' names in one column, expressed as (last name), (first name) -- with the comma. Now the last names are in Column B, and the first names are in Column C. I have to turn all this stuff from tables into text for easier print formatting.

Is there a way to automatically add a comma to the end of the text in one column? That way, after it's converted from tables to text, I can do a global search/replace and change all instances of "comma, tab" into "comma, space". The last couple of issues, I've had to do it manually, line by line, and that gets old with 600 repetitions.

Are you on Excel? You could use the CONCATENATE function to do this.

The other option is to use a word processor that has a macro editor, create a macro that does the replace, and run it over the document; TextPad does this and can be used in indefinite trial mode AFAIK.

I tend to build a new column that contains the text I want out. So, uh, if Col B contains Einstein and Col C contains Albert. I can make a Column D that contains a formula ala "=B1 & ", " & C1" and just pull the formula down to the bottom of the document and copy the results.
The & means to join or concatenate the strings to the left and right of it.

(hrm, sorry if the syntax is off there -- I don't have access to Excel on this computer.)

I dunno why I made the Albert Einstein example when I don't use it. It SHOULD in theory at least result in column D containing "Einstein, Albert".

I'm not using the spreadsheet itself, I just get tables sent to me in a .docx file, which I open in OpenOffice. If there's a solution that involves my not having to mess with the spreadsheet, that would be great.

http://wiki.openoffice.org/wiki/Documentation/OOoAuthors_User_Manual/Getting_Started/Creating_a_simple_macro

I would do this; my macro would basically be "search for tabs until I get to the one I want to replace, replace it, then move the beginning of the next row"

Couldn't say for sure without seeing it, but I'd probably copy the rows into a regular text document to get rid of the table-formatting; then highlight whatever ends up separating the columns and search-replace it with ", "

xp: or that. Macros are grebt.

I can't search-replace that way, Oystein, because there are other columns that would be affected. Column A is the kid's ranking and Column D is their point total. Don't want a comma replacing each tab, and in the case of one of the tabs, I don't want to get rid of it at all. I think Dan's solution will work -- thanking u DJP!

ILX, pls help with a boring database question.

I have two enormous spreadsheets full of data relating to UK companies and commercial space, with 4.5m and 2.1m lines respectively. My company uses this data for economic analysis, but because the files are so big, Excel can't open them. I can use them in R (which is currently the extent of my programming knowledge), but my colleagues can't code. When they need data, they have to ask me to filter and export subsets for them.

Is there a cloud-based database that could a) store this data and b) is user-friendly enough that colleagues could do the filtering and exporting themselves? Or is this something that needs a bespoke setup? Like I guess what I have in mind is that I can just upload the spreadsheets, colleagues don't need to actually see the data, they just need an interface/front end that lets them filter for a specific area and export.

You could set up an Azure account, upload the data to Azure data storage, and then set up a Power BI dashboard to display the data they need?

Obvs will need to set up security / permissions and some kind of data pipeline to ingest the Excel into the database + some kind of automation to stop the Azure instance when its not doing anything so avoid unexpected usage bills.

Folks use this on newspaper/data-driven journalism projects: https://datasette.io/

Thank you. I was looking at Azure but the wording on the website was so jargonny I couldn't tell if it would be right for us, so that's helpful. (I was struggling to google this issue in general because I don't know what terminology I should be using.) I'll check out datasette too.

I hesitated to say azure as im really not our infrastructure/data guy and amnt too sure how it scales down but wouldve thought it a runner for a look at least

before going down that route, could Access maybe handle this, if it's just for filtering flat data? I think Access can handle more data than Excel, and if you've got Excel you probably have Access already, so no extra costs?