December 27, 2006 at 12:29 pm
my puzzle is that a record has a single linear set of fields - - and I need it reformatted for an accounting program to import that requires a fixed structure that is multi line. Want to reformat by making a temp table - then can export to excel or tab delimited.
What I have:
Record1: Field1 Field2 Field3
What I need:
Header1a Header1b Header1c Header1d
Header2a Header2b Header2c Header2d
Header3a Field1 (blank) Field2
Header4a (blank) (blank) Field3
Header5a
Have tried to line up into 4 columns. The specifics are not important...but the general idea is that there are a couple lines of fixed data which I call Headers. A header starts each line and a header ends it.
All the headers are fixed data and I have them. There are blank fields as in some records there is no data and that is ok when it does occur.
So to sum up: There is a query that returns to the user the correct record. Next there needs to be a 'prepare to export' button that will trigger an sql resulting in a temp table with this format....
Am mulling over an sql statement that will work and welcome very much a sample or two from you full time sql-ers out there to help me get my brain around it.....gracias.
December 27, 2006 at 1:06 pm
This would not be something that is done in SQL Server. You would use your front-end (presentation) to format the data returned by the query.
-SQLBill
December 27, 2006 at 2:00 pm
well indeed this is actually an Access db and not a question specific to SQLserver per se. In general the Access forums are not heavy in sql statements so thought I would ask here.
the structure format process can be done in code (probably VB) or it can be done (I believe) via an sql method.
in an Access split db - via either approach the code is done in the front end.
I kind of view it as a pure sql puzzle or a pure vb puzzle and am leaning toward sql at the moment... but perhaps it shouldn't be posted here as it is not really a sql server question per se...not sure.
December 27, 2006 at 3:00 pm
One BIG problem is that this isn't a SQL site, it's a site for Microsoft SQL Server. JET SQL (Access) and Transact-SQL (SQL Server) are not the same thing. The basic SQL is the same, but that's it. In a SQL Server forum like this one, you might get solutions using CASE, but that doesn't work with Access as IIF doesn't work with SQL Server. This site does have a forum for ACCESS questions, but it's usually for making SQL Server and Access work together.
-SQLBill
December 27, 2006 at 6:55 pm
yeah you are right. Over at sqlteam.com there is an area where people are into tuning sql statements - and that is what I am looking for...
December 28, 2006 at 9:11 am
--Without knowing more details, here's a starting point...
--though I agree that VB on front end is probably more straight
--forward...
SELECT
C1,C2,C3,C4 from(
SELECT
PrimaryKeyFieldName,1 as SeqNbr,'Header1a' as C1,'Header1b' as C2,'Header1c' as C3,'Header1d' as C4 FROM TableNameUNION ALL
SELECT
PrimaryKeyFieldName,2 as SeqNbr,'Header2a','Header2b','Header2c','Header2d' FROM TableNameUNION ALL
SELECT
PrimaryKeyFieldName,3 as SeqNbr,'Header3a',[field1],'',[field2] from TableNameUNION ALL
SELECT
PrimaryKeyFieldName,4 as SeqNbr,'Header4a','','',[field3] from TableNameUNION ALL
SELECT
PrimaryKeyFieldName,5 as SeqNbr,'Header5a',NULL,NULL,NULL from TableName)
as FiveLineRecordJoinorder
by PrimaryKeyFieldName,SeqNbr
--Also, Note that it assumes text for all fields, you will have to
-- use CAST([field1] as VARCHAR(10)) to convert to text for each of the
-- fields
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply