Replies

(Edited by HansV to make url clickable – see Help 19)

What about…
http://support.microsoft.com/default.aspx?…kb;EN-US;283875%5B/url%5D
HOW TO: Transpose Data in a Table or Query in Access 2002 (also available for Access 2000 & 97)?
Assumption: you have no more than 255 records because the maximum number of fields in a Microsoft Access table is 255.
The second method (VBA function) seems the most valuable, but seems a big to kill *…
Still I hope it helps.
(thanks to google + search string “transpose table Access”!)

* board: did you ever consider yet a smily expressing a fly? Might be useful in some expressions…

Thanks Cecilia . I’m just a bit embarassed for (instead of my Iif(…)-proposal) not having used the probably much faster Nz(), mentioned by our moderator(s). But that’s why it’s always so good to have them around

Cecilia,
I don’t completely understand much of it but MAYBE I can help you with the last question.
First I want to generalise your question in order to know whether I got it right.
Does
“Has anyone been able to create a query that always has a set of standard rows, possibly displaying 0’s when there are no values? If I could do this, then my subreports would show the basic information”
mean something like:
“I have a crosstab query showing sales type (row header), region (column header) and sum of profits (value). What do I need to let all sales types (= rows) appear, instead of only those shown in the crosstab?”
Am I right?
Then, a first step might be taken by a next query combining (all fields from) your crosstab query on the left with a recordset representing all possible sales types (using a sales type lookup table or query) on the right. Now: right outer join them by their common field (sales type / sales type ID?). As such, the result should show a row for each possible sales type.
I don’t know how you should insert the ‘0’, but that must be possible too. In the worst case, by using a calculated field for each column header in your crosstab (e.g. “Region1_: Iif(IsNull(Region1);0;Region1)”.
I hope I don’t confuse you more that it helps
Hasse
ps I remember Hellen Feddema writing (more than?) once about handling empty (sub?)reports with code. So for that aspect of your question, searching the Access Archons might reveal something adjustable for your needs…(!?)

OK, I agree, of course, with Charlotte. No-one is forbidden to use identical ID’s (or…) for different entities, or different name for the same ‘field’ in different tables, or even in the same table. I just don’t consider it such a common practice that it doesn’t raise any questions when there’s a join presented between two fields which by their name, if not by content, might as well indicate something rather different.

Further I agree totally with HansV asking for a small ‘postable’ extraction of your database, so that we might examine it ‘live’…

ps Just one more try: don’t you have a combo box behind the ’empty’ field referring to a wrong data source? Then, it might find no match between the actual stored data and the combo box entry data, resulting in a blanc white field…

Dear valuable members of the board,
from my humble experience, yes, I think this is a tip for general use.
It certainly seems a bit arrogant inserting this post now, in the end , but reading through this thread, scrolling down, in my head, I was already wondering why you didn’t use AutoExec to trigger a function (or another procedure through that function) at the startup of a database. As far as I used it (but this, to be said, not at a professional application level) it has always worked fine. For what it’s worth…
Hasse

Peter,

I’m not sure if I totally understand your question, so don’t be too harsh on me if you don’t understand the answer , but…
A try: can you somewhere in your data selection & … process rank your weeks from 1 to 52 (instead of their absolute values, which you currently use, I presume)? Such constant set of weeks nr. 1 => 52 might be easier to handle and as such a step closer to what you want to accomplish in your report,…

E.g. Imagine a form with a text/combo box in which you enter/choose the start date of the primary selection. Use that date as a parameter in your query (by adding a calculated field, e.g. “StartDate: [forms]![EntryForm]![EntryControl]”). Derive the ranking of your weeks by comparing them to that StartDate: every ‘real, absolute’ week (e.g. 03/04/21 – 03/04/27) will be transformed in a ‘relative’ week number X where X = 1 for first week after (or including, if you wish) that date, X = 2 for second week after …
(To avoid negative or too high numbers, you’ll have to allow only a definit range of entry values in the text/combo box.) Now, you have a constant value set which you can use in the queries & reports based on it.

Does this help you in any way?

If it didn’t, can you provide us some more detailed & step by step information about the precise structure (fields used,…) of the tables & queries you start from & use while preparing the data source for your report?

Hasse

Another possible silly question…
How do you manage to join two tables by using different ID’s (song single)?
In plain English: if I translate your SQL, I read: show all records for which the SONG shared by the publishers is the same as the SINGLE you talk about.
If this is no mistake (and then, I still wonder how you could get a decent record set as a result of this query), didn’t you forget a relation table SingleSongs somewhere inbetween tblSingles and tblPublisherShares?

HansV
you completed it better than I should have been able to (just one reason why it’s always so good to have some moderators in the house )
thanks!
Hasse

Can’t this code in your form’s current event do the job?
(Or am I too fast…)

Private Sub Form_Current()
If Me.Subformcontrol.Form.RecordsetClone.RecordCount = 0 And Not Me.NewRecord Then
Me.Subformcontrol.Visible = False
Else
Me.Subformcontrol.Visible = True
End If
End Sub

Then, you can still use your button to show the empty subform again in records with no sub-datasets when desired.

fruz,
I’ld think there are many freeware tools available to fullfill your needs. So I’ld think your question should be answered in no time at Google.

In case you’ld like to create one on your own in Access, first think about how detailed it should be. Then, for example, you can build a simple ToDoList database with:
– one table: tblTasks = ID, project, task, entry date (date), deadline (date), priority (integer), remarks (memo), done (yes/no)
– one query: qryToDo = sorting tblTasks on deadline (ascending), priority (ascending), having one criterium ‘done = no/false’ => this can serve as your ToDo-list.

Hasse

Thanks for the suggestions!
(Freeware) Wilbur looks great at first sight. I don’t know yet how to incorporate this in an ‘outside the mailing software’ email archiving strategy, but that’s a next step. Anyway, I see immediately already some very interesting issues to apply it on too.
I didn’t have the chance to check out (not freeware) Boiler base though, but printed out the web site content for an easy read on the train, in case it offers better features… I’ll try to remember this thread and add more value if I find some other interesting alternatives.
So thanks again – you’re on the edge of saving a group plenty of time!

Hey,
does someone have any experience in joining files and e-mails in one system? Software providing document management facilities might do, but I’ld first like to explore other paths…

Preferably, a good organisation & archiving of e-mail follows the structure of your directory structure. But still, then, you have everything in ‘seperate worlds. I would like to solve this by storing the mails & attachments on my pc or network. This should be done in a way that the e-mail is still searchable enough,… Moreover, this should reduce the migration efforts in case of a move to another e-mail software program…

Untill now, I’m experimenting by storing the mails as emyymmdd_from_to.txt & emyymmdd_from_to_attachment.xxx. But this most probably is a dead end which won’t work for general use…

I’ve read some seminar articles about the idea to store e-mails as XML files. The idea: most e-mail software products provide an export facility to store your e-mail as a txt file. As such, it’s a ‘small’ step to a facility which stores as a XML codes txt file, containing tags for all fields (from, to, cc, bcc, date, subject, body,…), optional contextual information and also a tag for the attachment(s) storage location (?), which guarantees the link to the those file(s), which need to be detached from the e-mail during the storage process. Files can be read by anything (based on) a (html?/)xml viewer. (I’ll skip here more technical details, conditions,…). The author suggested that the leeding software products should once develop features or extensions to make this possible.

Does anyone know yet of such things available/in development?

Hey, Ifwatson,
honestly, ‘programmer’ might still be a bridge too far in my case. For example, I think I still lack some automatisms skilled IT-people have,… But I’m able to do moreless what I want in VBA. Access is more a hobby to me, but as I’m having the opportunity to amuse myself with it (he he) at my job as well, I-m a bit a professional as well (though I think most of us can say that :-).

Thanks both for telling why ‘doing on your own’ what Access also offers, is sometimes unavoidable.

And by the way, Charlotte, thanks for your tip. Often, many ways lead to Rome but this is a neat suggestion, especially when you use continuous forms.

Hasse

even though I use it only for personal use, I can for sure recommend Fastmail too. They have a free (non commercial only) & paying (3 levels) service, each with its own limits on allowed number and size of e-mail per hour/day.
More info at: http://www.fastmail.fm/pages/fastmail/docs/pricingtbl.html

Hey, Ifwatson,
I’m happy I could, for a change, help someone else too :-).
Please notice that I changed my posts: I cut the code of the second function procedure, which factually wasn’t needed at all.
Just one last question, out of curiosity: why do you want to sort using code instead of the available Access sort buttons?
Hasse