Refreshing Tables In Database

I have a stored procedure that creates a table. It works fine except until
I
refresh the database, the table doesn't appear ... and views and reports
can't find it.
Does anyone know how I can do this automatically with SQL or VBA (I'm using
an Access project as my front end)?
Thanks!
HIf I had to guess, I would say that you create the table as one user and the
n
try to access it as another - just a guess
"Howard Brody" wrote:

> I have a stored procedure that creates a table. It works fine except unti
l I
> refresh the database, the table doesn't appear ... and views and reports
> can't find it.
> Does anyone know how I can do this automatically with SQL or VBA (I'm usin
g
> an Access project as my front end)?
> Thanks!
> H|||Howard Brody wrote:
> I have a stored procedure that creates a table. It works fine except
> until I refresh the database, the table doesn't appear ... and views
> and reports can't find it.
> Does anyone know how I can do this automatically with SQL or VBA (I'm
> using an Access project as my front end)?
> Thanks!
> H
This seems to be an Access related issue. I would post the question to
an Access group and see what they say.
David Gugick
Imceda Software
www.imceda.com|||I dunno ... the table doesn't appear until the database is refreshed whether
I run the code from a stored procedure or the query analyzer - which has
nothing to do the Access front end.
I would think that SQL would have a command or function for refreshing your
database. I just haven't found it yet.
H
"David Gugick" wrote:

> This seems to be an Access related issue. I would post the question to
> an Access group and see what they say.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Howard Brody wrote:
> I dunno ... the table doesn't appear until the database is refreshed
> whether I run the code from a stored procedure or the query analyzer
> - which has nothing to do the Access front end.
> I would think that SQL would have a command or function for
> refreshing your database. I just haven't found it yet.
> H
>
There's really no such thing as refreshing a database. Seeing the
objects in a list, for example, is a client issue (Access in this case
or could just as well be Query Analyzer). Once you create an object in
SQL Server, it's there, whether you see it in the user-interface of an
application or not. There's no real live-feed of database objects like
you have when viewing file, for instance, in Explorer - which keeps an
eye on folders for changes - most times. Even in QA, you don't need to
see the object in the Object Browser to run a query against it. Whereas,
I suspect, you need to see the object in Access to create a query or
open up the table in the Access UI. That was my reason for suggesting
you post to the Access group because possibly there is a feature of
Access that can mitigate this problem somewhat.
David Gugick
Imceda Software
www.imceda.com

Referencing a value on one page of a report in a textbox

Hello,
I am creating a profit and loss statement in SQL Server reporting Services.
This single report creates a one page P&L for each loction in the company.
Some of the calculations are based on the Sales of each location (i.e.
percentages etc.). I'd like to reference the sales total for the each
location in one of the groupings of the report. The problem is, when I do
"Sum(Sales)" it doesn't sum the sales figures for the location, but only the
grouping I'm in. Is there a way to do this expression so it sums the Sales
figure for the whole location? If not, is there a way to reference a
particular textbox on the report?
Any help or direction would be greatly appreciated.
Thanks!
RickHey Rico,
Try Using:
RunningValue(Fields!Sales.Value, Sum,"LocationGroupName")
Michael C
"Rico" wrote:
> Hello,
> I am creating a profit and loss statement in SQL Server reporting Services.
> This single report creates a one page P&L for each loction in the company.
> Some of the calculations are based on the Sales of each location (i.e.
> percentages etc.). I'd like to reference the sales total for the each
> location in one of the groupings of the report. The problem is, when I do
> "Sum(Sales)" it doesn't sum the sales figures for the location, but only the
> grouping I'm in. Is there a way to do this expression so it sums the Sales
> figure for the whole location? If not, is there a way to reference a
> particular textbox on the report?
> Any help or direction would be greatly appreciated.
> Thanks!
> Rick
>
>|||Thanks!
"Michael C" <MichaelC@.discussions.microsoft.com> wrote in message
news:1F8BB7D0-3298-4A46-BB9D-389C26386523@.microsoft.com...
> Hey Rico,
> Try Using:
> RunningValue(Fields!Sales.Value, Sum,"LocationGroupName")
> Michael C
> "Rico" wrote:
>> Hello,
>> I am creating a profit and loss statement in SQL Server reporting
>> Services.
>> This single report creates a one page P&L for each loction in the
>> company.
>> Some of the calculations are based on the Sales of each location (i.e.
>> percentages etc.). I'd like to reference the sales total for the each
>> location in one of the groupings of the report. The problem is, when I
>> do
>> "Sum(Sales)" it doesn't sum the sales figures for the location, but only
>> the
>> grouping I'm in. Is there a way to do this expression so it sums the
>> Sales
>> figure for the whole location? If not, is there a way to reference a
>> particular textbox on the report?
>> Any help or direction would be greatly appreciated.
>> Thanks!
>> Rick
>>

reeeeeally easy for you guys!

can someone post me an example trigger where.. inserting a new record in one table, creates a new record in another table with a new unique identifier incrementing by 1 from the last record.

so that I can edit it to suit my own work like:

eg. when I insert new person into people table, using a person_no as key.
= creates new pay record in pay table ith a pay_id as key like '0004' 1 more than the last record if it was '0003', and then adds that new person_no to the record for reference.

I know this is a really easy trigger, but it would be my first! Any example to edit would be great! thanks in advancecreate table tablename2(
id int IDENTITY (1, 1),
value varchar(100)
)

CREATE TRIGGER Trigger ON TableName
FOR INSERT
AS
declare @.value1 varchar(100)
select @.value1=columnName from inserted

insert into tablename2 values (@.value1)

Originally posted by simple_simon
can someone post me an example trigger where.. inserting a new record in one table, creates a new record in another table with a new unique identifier incrementing by 1 from the last record.

so that I can edit it to suit my own work like:

eg. when I insert new person into people table, using a person_no as key.
= creates new pay record in pay table ith a pay_id as key like '0004' 1 more than the last record if it was '0003', and then adds that new person_no to the record for reference.

I know this is a really easy trigger, but it would be my first! Any example to edit would be great! thanks in advance|||I think I follow this, but my equivalent of identity and value are in different tables. I don't think I can apply this,

Which is the part of the code to increment new value by +1 of the last?|||Which is the part of the code to increment new value by +1 of the last?

The column 'id' is created as an IDENTITY field, which means the value in this column is automatically entered. It is set to start with 1 and increment by 1 each time a new record is inserted. Notice that the INSERT statement inserts data into the 'value' column only, the correct incremented integer for the 'id' column is calculated and inserted automaticlly.

This will work if you are creating new tables, if you are working with existing tables and data you would either have to ALTER the table or calculate the new value for 'id' yourself. Take a look at the following code. (I changed the name of your person_no column to people_id, standard naming conventions are a good habit to get into) The trigger will find the maximum current pay_id, increment it by 1, then insert it and the new people_id into the pay table

use northwind
go

CREATE TABLE people
(people_id int)
go

CREATE TABLE Pay
(pay_id int,
people_id int)
go

CREATE TRIGGER tr_insert_people ON people
FOR INSERT
AS
declare @.new_pay_id int
declare @.people_id int

select @.new_pay_id = ISNULL(max(pay_id),0) + 1 from pay
select @.people_id = people_id from inserted

insert into pay
(pay_id,people_id)
values
(@.new_pay_id,@.people_id)
go

INSERT INTO people
VALUES (1)

INSERT INTO people
VALUES (2)

INSER INTO people
VALUES (5)

SELECT * from people
SELECT * from pay

DROP TABLE people
DROP TABLE pay|||and what do you suppose will happen when you have this:

INSERT INTO people
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3

...or any other set based operation?