Suggestions On How To Do - Maybe a CrossTab Query?

[kjv1611]

Okay, I am not particularly sure how I want to go at this particular query. I am trying to combine some details about records which I have in 2 seperate tables. Each of these tables contain data from the same primary table, and they include the same fields.

What I am looking at is a "notes" field, and I am looking for 2 specific types of notes, we'll just say note "abc" and note "xyz" for reference. What I want to do is to see on each account, what notes were added of both of these types (for each day).

So, what I would like it to end up looking like would be something like this:

AccountID abcNote1 abcNote1Date abcNote2 abcNote2Date xyzNote1 xyzNote1Date xyzNote2 xyzNote2Date and so on.

The fields I have in each table, currently, are:
AccountID
DateAndTime (this is the date referenced above) (this is the date/time when the note was added.
Note

So, I want each record to be determined by the AccountID, and the Date the note was added. That will at least narrow it down by day. Beyond that, i'm not sure how long we'll need to narrow it down before I look.

Any suggestions greatly appreciated.

I was thinking that if I do this in SQL Server 2005, that I'll be able to do it more efficiently than in Access. At first, I was considering using VBA in Access to accomplish this, but I'm hoping I can do it in SQL.

Thanks.

--

"If to err is human, then I must be some kind of human!" -Me

 

[philhege]

Yeesh.

How many of these "...and so on" situations do you have? Sooner or later, you'll run into the field count or record length limit. This situation doesn't really call for a cross-tab query; that's not what you're doing here.

If you're trying to lay a record out the way you want it to be displayed, that's the wrong approach. Use a simple query result (account, date, notetype, note) and design your presentation layer to handle the output.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[kjv1611]

philhege,

Thanks for the post. Yes, I agree this one sounds rather awful. The idea in my head seemed to be easy, but then thinking of putting it into query terms doesn't seem quite so simple.

The fields, in this situation (it would not be a recurring query), would actually be limited to 8 or 9, max. I pulled a query of the count of the possibilities, and found that the account with the most possibilities from one table was 9.

So actually, I'm thinking for my purposes, what I could do is a Cross-Tab query or some other option for the one table. Then build another query that compares the crosstab query to the other table, and only pull out those records in the second table that are on the same day as each record.

I may not be making this much clearer yet.

I'll post an example, shortly, of somewhat what is there, and what I want to be there...

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Okay, each table currently has this sort of data:

abc table..

AccountID Note NoteDate
12345 ...abc... 1/1/2006
321456 abc.... 3/4/2006
78964 ..abc.. 10/4/2006

xyz table..

AccountID Note NoteDate
54342 ...xyz... 6/7/2006
321456 xyz.... 12/9/2006
78964 ..xyz.. 10/4/2006

So, I would want to take this data, and combine it like this (and only for those which have a record in the "abc" table):

AccountID Note1 Date1 Note2 Date2
78964 ..abc.. 10/4/2006 ..xyz.. 10/4/2006


And so on. So, the total could end up being more than 8 different columns. Although I can't say it is impossible, I would think that it would likely be able to be limited to about 16 columns in a Cross-Tab query.


And as far as:

If you're trying to lay a record out the way you want it to be displayed, that's the wrong approach. Use a simple query result (account, date, notetype, note) and design your presentation layer to handle the output.

Could you expand upon that thought: as to what youd do for that? I am not planning on this initially being used as a report, but primarily to verify which of the accounts in table "abc" have a comment on the same day in table "xyz". The reason is that each abc listing is supposed to have an xyz listing - basically if action abc is noted, xyz is supposed to be noted as well (and that would more than likely happen within a set time frame - I'm guessing an hour or two at most, but at least in the same day for sure).


--

"If to err is human, then I must be some kind of human!" -Me

 

[KeertanaCR]

HI Kjv,

I am not sure what exactly you are trying to do, but you want the notes and dates from the above tables based on account id, i guess the following query would do,

Select abc.AccountId, abc.Note, Abc.Notedate,
xyz.note, xyz.netedate
from abc
Inner join xyz on (abc.AccountId=xyz.accountId)

and if you want to check the date conditions also, like both note dates should be same, you can as well say

Select abc.AccountId, abc.Note, Abc.Notedate,
xyz.note, xyz.netedate
from abc
Inner join xyz
(abc.AccountId=xyz.accountId and abc.Notedate=xyz.notedate)

Hope this helps!

Thank you
Keerthana

 

[kjv1611]

Yes, I do think that would be getting close to what I'm looking for/thinking of, KeertanaCR.

What I am really somehow wanting to do is to basically say, "for account ID 12345, which abc notes were on the same day, and which xyz notes were on the same date as an zbc note."

And, I may left one small detail out, sorry. On the dates, the format in the original data includes the date and time, not just date. I only want to compare the date (that being month/day/year).

I may have seen it before, but I am not sure off the top of my head of a function that will take just the date value from a date/time field, and compare that way. Is there a short way of doing this?

Thanks.

--

"If to err is human, then I must be some kind of human!" -Me

 

[KeertanaCR]

Change the date condition as follows would do:

convert(varchar,abc.Notedate,101)=convert(varchar,xyz.notedate,101)

Thankyou
Keerthana!

 

[kjv1611]

Thanks, KeertanaCR!

I'll try to give that a whirl here shortly, and see what I come up with. This all may get me where I'm needing to go. Right now, I'm just needing to look at the raw data, and I'll worry about exactly how to report any related findings accordingly.

I'll post back once I've finished, and let everyone know the results so far.

--

"If to err is human, then I must be some kind of human!" -Me