Create an SQL View from VBA

Hello All;

I'm new to SQL, and developing my first application using SQL 2000 as the back end for my data.

Can anyone tell me if I can create an SQL View from VBA?

I'm trying to create a data view that access various records from a single table, and it has to distribute that data 2 14 different subforms, representing different days in a 2 week period, which is distingiushed by a field called "Day", that is numbered from 1 to 14.

I also have a summary subform that has the weekly summary (days 1 to 7 and 8 to 14) on seperate subforms. In total I have 16 subforms, which actually source from a single table, just split depending on the day in the period.

As I see it now, creating views iis the best way for me to go, but I also have to be able to change the period id on the fly, so I'm think I have to use VBA to generate these views.

Does any of this make sense, and am I on the right track??You might want to consider a more dynamic solution. Do the 14 forms all hold the same data fields? If so, why not use one form and base the contents on the day of a week. Make the day of the week a field in your table and populate your form based on a stored procedure that uses the day of the week as a parameter in the query.

Fixing your schema now will pay you back many fold in the future.

Avoid creating database objects on the fly in end user applications.

To answer your question, yes this is possible. Is it a good idea? No.|||... populate your form based on a stored procedure that uses the day of the week as a parameter in the query.

Fixing your schema now will pay you back many fold in the future.

Avoid creating database objects on the fly in end user applications.

To answer your question, yes this is possible. Is it a good idea? No.

Agreed with all the points here. You might also consider a user defined function that returns a table. I don't generally use these for multi-column result sets, but it is permissible to do so.

Perhaps you could post some ddl and sample data and improve your chance for getting a useable answer...

Regards,

hmscott

Course for Designing and Populating a Data Warehouse?

Hello,
A while back, I took Microsoft Course 2092A: Designing and Populating a
Data Warehouse with Microsoft SQL Server 2000. I have been reading
about SQL Server 2005 and see that it has several new data warehousing
tools. I want to learn more. Is there a course for building data
warehouses on SQL Server 2005?
Thanks in advance,
J Wolfgang Goerlich
I took Solid Quality Learning's end to end BI course and was very
impressed.
www.SolidQualityLearning.com
Note: No connection, just a happy customer.
Kevin

Course for Designing and Populating a Data Warehouse?

Hello,
A while back, I took Microsoft Course 2092A: Designing and Populating a
Data Warehouse with Microsoft SQL Server 2000. I have been reading
about SQL Server 2005 and see that it has several new data warehousing
tools. I want to learn more. Is there a course for building data
warehouses on SQL Server 2005?
Thanks in advance,
J Wolfgang GoerlichI took Solid Quality Learning's end to end BI course and was very
impressed.
www.SolidQualityLearning.com
Note: No connection, just a happy customer.
Kevin|||Ralph Kimball is to warehousing what Bill Monroe was to bluegrass music. He
has what he calls "Kimball University". If you want to learn about process
(rather than just product), his group is probably going to be the best.
<jwgoerlich@.gmail.com> wrote in message
news:1142512473.393687.317690@.j33g2000cwa.googlegroups.com...
> Hello,
> A while back, I took Microsoft Course 2092A: Designing and Populating a
> Data Warehouse with Microsoft SQL Server 2000. I have been reading
> about SQL Server 2005 and see that it has several new data warehousing
> tools. I want to learn more. Is there a course for building data
> warehouses on SQL Server 2005?
> Thanks in advance,
> J Wolfgang Goerlich
>

Counts on Subquerys

OK I have a query that bring back a unique identifier and text value
for instance

Select Distinct global_id, Page_url from PageList

global_id page_url
---- ----------
4306549 /true/attitudes.htm
1460753 /true/current/answerthis.htm
4303667 /true/current/answerthis.htm
4306549 /true/current/answerthis.htm

Now I want to do a count of the page_url.
Can this be done in one SQL statement using a sub query of some kindSELECT COUNT(DISTINCT Page_url) from PageList

??|||Originally posted by Brett Kaiser
SELECT COUNT(DISTINCT Page_url) from PageList

??

Brett, this doesn't seem to accomplich what I'm looking for. It's only bringing a count back of one for answerthis.htm, when there are clearly three records.

any suggestions|||I'm doing the best I can...but with the serial port to my telepathy device being blocked it's kind of difficult

:D

Maybe if you show us what the result you're looking for is suppose to be...DDl and sample raw data qould be very helpful to...

But how about another shot in the dark

Select global_id, Page_url, COUNT(*)
from PageList
GROUP BY global_id, Page_url

And yes you clearly have 3, ummmm, rows...

global_id page_url
---- ----------
4306549 /true/attitudes.htm
1460753 /true/current/answerthis.htm
4303667 /true/current/answerthis.htm
4306549 /true/current/answerthis.htm

Isn't the 1st row of your result set kind of ironic?|||Originally posted by Brett Kaiser
I'm doing the best I can...but with the serial port to my telepathy device being blocked it's kind of difficult

:D

Maybe if you show us what the result you're looking for is suppose to be...DDl and sample raw data qould be very helpful to...

But how about another shot in the dark

Select global_id, Page_url, COUNT(*)
from PageList
GROUP BY global_id, Page_url

And yes you clearly have 3, ummmm, rows...

The problem I'm having is that the unique global_id is breaking out each record on the group by. so when it is added i can't get a count of the page_url

Isn't the 1st row of your result set kind of ironic?|||Well was that it?|||How about this:

SELECT page_url, count(page_url)
FROM pagelist
group by page_url