December 6, 2002 at 9:41 am
I have a Database that unfortunatly was written in Access 2000 instead of SQL Server. Anyway I have a table with No primary key (Which by they way I didn't do) that has multiple entries for a key value, There is a column that we have to report on that we want to do a distinct count and price for that distinct price then print that either to a Excel spreadsheet for formatting or Word for reporting
here is kinda what the table looks like
--------------------------------------------
Key ProCode Price
1222 TRBLX 12.95
1222 TRBLZ 19.95
1222 TRANZ 12.95
I can do this fine in QA but I need to list each of these out on word doc (to a bookmark) or a Excel spread sheet to a range. There are multiple columns in the database just like these three, minus the key so might be PjdCode, PjdPrice, other stuff, I have been looking at this for months and just can't get it working ok.
If anyone knows how to do This I would greatly appreciate the help.
Thank you in advance
Stephen
December 6, 2002 at 9:48 am
This may seem simplistic, but if you can do it in QA, why can't you just throw that into a DTS and write it to excel (If you need a range, criterialize it.)? What is happening that is giving you problems?
December 6, 2002 at 9:52 am
Well, the problem is that it's part of a VB6 application that I don't believe I could use a DTS package.
Also the problem is on one line there could be potentially 170 different codes that would be very time consuming to write. I would like to get a distinct value for each one, (based on order date), count of all of them and then a total price. I believe I am going to have a problem with this in reporting. It could be the forest for the trees syndrome, since I have been staring at this for months and then this thing got sprung on me.
We are using VB6, Access2000 and Word/Excel templates. I know it seems simple but it could also have null's in the column as well. that is where the difficulty comes into play
December 6, 2002 at 9:54 am
You can easily call a DTS pacakge from VB6. In fact, you can save out the DTS pacakge in VB format, then add it in as a separate project or modules and call it like any other object.
Steve Jones
December 6, 2002 at 9:56 am
VB can call DTS packages. And for the nulls, eliminate them in your query returning the results. A parameter passed into your DTS package could supply the code changes for your ranges.
Oops, I see now Steve has already said that.
Edited by - scorpion_66 on 12/06/2002 09:57:10 AM
December 6, 2002 at 9:57 am
If your data is still held in Access2000, then you could create a Query in Access then output it direct to Excel. (There is an export to Excel command in Access VBA which I can't recall at the moment.)
Edited by - paulhumphris on 12/06/2002 09:58:08 AM
December 6, 2002 at 10:02 am
I just thought about it some more, and if you don't like the DTS route, just use recordset objects, eliminate the nulls in the query results, and use an excell object to write to. Puts the recordset handling in your app, but it would work. If you populated your recordset object right, another recordset object could be used to hold the values for one code's worth at a time, and you could loop through them.
December 6, 2002 at 10:09 am
Good Idea's everyone, Thanks, I didn't think about DTS, We are having the VB code handle the record sets but getting it to print like a report is the trick and if I do eleminate the nulls would that kill the record? And with the potential of sal the table looks like this
--------------------------------------------
Key Code1 Price1 Code2 Price2
123 TRBL 19.95 Null 0.00
123 null 0.00 TDSB 12.95
-------------------------------------------
If I strip out the nulls wouldn't that kill the query if I did it something like this
Select * from Table1
Where Code2 is not null
I am thinking I have to create a query that will dynamically look for distinct values in each column then find where it's null and get rid of them.
I am still a little confused and I might be able to do it in DTS and put it into a temp (dynamically created) tables in access then delete the table after the report has been created. Any additional thoughts
December 6, 2002 at 10:18 am
By eliminate the nulls, I meant something more like IsNull([field], "alternateValue")
don't remove records, just replace null values.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply