Creat Table Query

R

Rickk

Hello and Thank You. New user here. Im trying to figure out how to make a
Create Table query that will be able to divide an existing table into
separate tables everytime a field's value changes, how should I go about
doing this? The field that I want to base this query on is an eight digit ID
field. Thank you all so very much for any help you might be able to give me.
 
J

Jerry Whittle

Access doesn't support table-level triggers. Therefore a change in a record
can not kick off a query.

Actually my first question should be why? Usually putting the same kind of
data into multiple tables is a very, very bad idea.
 
L

Lord Kelvan

ok why are you wanting to split it into multiple tables regardless
access dosnt support triggers so you can only do it via a form when a
user uses the form to change a value you can execute a set of queries.

what is your table structure because it sounds like you are making
your data very redundant which isnt good.

Regards
Kelvan
 
R

Rickk

Thank you for answering Lord Kelvan. My place of work asked me to divide the
main table into smalller ones after grouping the id field. Everytime the ID
field changes in the table I have to create a table. I was thinking of using
a form to handle this but there won't be any users manipulating any of the
data. I believe the agency is looking for automation. My table structure is
only three fields, so far. The Provider ID field (text), The recipient name
(text), and the recipient id field (text). Thank you for any help you might
be able to offer.
 
L

Lord Kelvan

my point is why do you need to create tables why not just use queries
to simluate multiple tables and then everythign thim the query is run
different records will be displayed based on the values in the main
table

ie

if you table was

provider id recipient name recipient id
1 a 1
1 b 2
1 c 3
2 d 4
2 e 5
3 f 6
4 g 7

you coudl have a query for each provider

select * from thetable where provider id = 1

it will retrieve

provider id recipient name recipient id
1 a 1
1 b 2
1 c 3

and if your table changed to

provider id recipient name recipient id
1 a 1
2 b 2
1 c 3
2 d 4
2 e 5
3 f 6
4 g 7

the query will retrieve

provider id recipient name recipient id
1 a 1
1 c 3

is this what you are looking for

Regards
Kelvan
 
R

Rickk via AccessMonster.com

Thank you again. Yes this is what I am looking for, but my problem is right
now there are 256 different Provider Ids and I need to find a way to avoid
having to create 256 different queries. The table is due to grow in size as
well. Any suggestions?
 
L

Lord Kelvan

so you think it is better to have 256 tables insted of 256 queries...

what you can have is a paramater query to allow you to select
information for any provider just by entering a value

select * from thetable where providerid = [enter provider id]

when you run this query it will prompt you for an id just enter a
value and bingo.

if you want to use this in conjunction with a formall you have to do
is

select * from thetable where providerid = [forms]![frmyourformname]!
[textboxcontainingproviderid].[value]

hope this helps

Regards
Kelvan
 
Top