Arrghh I'm going insane, plz help...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Arrghh I'm going insane, plz help...

Author

Topic

weedavie22
Starting Member

24 Posts

Posted - 2004-02-06 : 07:14:40

I've put together a quick 2 field database.

Table1 has FILEREF and AUTONUM

FILEREF has four records 0001GLA, 0001GLA, 0002GLA, 0002GLA
the problem I have is that I then want the AUTONUM to count the various groups of FILEREFs and assign a automatic next number.

So in the end I'll have 0001GLA /1, 0001GLA /2, and then start over with 0002GLA /1 0002GLA /2....

Any ideas?

SamC
White Water Yakist

3467 Posts

Posted - 2004-02-06 : 07:29:57

Not the simplest of data structures. There might be a few posts that ask "why are you numbering like this?" (an alternative scheme might be better).

'Course I don't know if these elements need to be renumbered if you delete a record. If they don't, then it's not to hard to number 'em when the values are inserted...


INSERT INTO Table1 (Fileref, Autonum)
  SELECT '0001GLA', COUNT(*) + 1
    FROM Table1
      WHERE Fileref = '0001GLA'

Next question - how to renumber these elements when a row is deleted?

weedavie22
Starting Member

24 Posts

Posted - 2004-02-06 : 07:52:52

The problem is there will be many of these codes in my database when I'm finished.

Is it possible to have a similar code without specifing 0001GLA and have it look at the FILEREF.

Just so that I could have reference based on the group of FILEREFS e.g.
There could be 10 0001GLAs so I would then have
0001GLA/1, 0001GLA/2, 0001GLA/3
There could also be 5 0002GLAs giving me 0002GLA/1, 0002GLA/2....

Deleting and renumbering the records won't be a problem.

Does this make sense??

cheers,

SamC
White Water Yakist

3467 Posts

Posted - 2004-02-06 : 08:06:54

I don't see your problem with the INSERT statement above , but I'm sure you have a good reason. Maybe if you specified the method you plan on using to INSERT it would help to identify a better method.

If you parameterize the insert, no big deal.


DEALARE @MyParm VARCHAR

SET @MyParm = '0001GLA'

INSERT INTO Table1 (Fileref, Autonum)
  SELECT @MyParm, COUNT(*) + 1
    FROM Table1
      WHERE Fileref = @MyParm

If you elaborate on why this method won't work, I'll try again. In anticipation, here's one of several possible exceptions: I want to insert a set of values all at once.

OK, then...

INSERT INTO Table1 (Fileref, Autonum)
  SELECT MyFileref, (SELECT COUNT(*) + 1 from Table1 WHERE Fileref = MyFileref)
    FROM MyOtherTable

SamC
White Water Yakist

3467 Posts

Posted - 2004-02-06 : 08:14:36

I didn't realize this was the Access forum. I was posting SQL 2000 syntax....

Sam

weedavie22
Starting Member

24 Posts

Posted - 2004-02-06 : 08:52:39

Yeah I noticed

but it still gives me a good idea on what to do.

cheers

Dave

weedavie22
Starting Member

24 Posts

Posted - 2004-02-06 : 11:41:56

Still don't have a solution.

Any ideas guys.

Thanks

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-02-06 : 11:47:34

OK I'll ask. Why do you need to do this?

SamC
White Water Yakist

3467 Posts

Posted - 2004-02-06 : 11:59:23

Post what you have.

weedavie22
Starting Member

24 Posts

Posted - 2004-02-06 : 12:04:49

The reference can be broken down like this.

0001GLA is the reference given to the Client

Each record will then have a autoref assigned to it, this will be based on how many records that particular client has.

So if the client 0001GLA has two records his refs will be 0001GLA/1, 0001GLA/2.

Hope this helps explain my question.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-02-06 : 15:35:01

update wee

set autonum = nz(dmax("autonum", "wee", "fileref = '" & fileref & "'")) + 1

where autonum is null

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-02-06 : 16:06:06

Oh I know - you need something like trigger. Alas.
But there is the huge heap of Form events and so on.

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-02-06 : 17:24:50

Yeah, if you had SQL Server, a simple INSERT trigger would solve this.

I'm not sure if this is doable, but maybe you can have something that periodically looks through your Access database and does an update on records that have a blank 'autonum'.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

weedavie22
Starting Member

24 Posts

Posted - 2004-02-07 : 07:13:33

update wee
set autonum = nz(dmax("autonum", "wee", "fileref = '" & fileref & "'")) + 1
where autonum is null

Soz Stoad im not sure where NZ and "wee" come into it??

Could you plz explain??

Cheers,

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-02-07 : 09:19:17

Replace "wee" with the real name of your table.

The nz() is a built-in vb function ("null-to-zero").

weedavie22
Starting Member

24 Posts

Posted - 2004-02-07 : 12:40:07

Cheers for your help Stoad, using the info you gave me I’ve modified the code slightly and attached this command to the auto ref control.

=Nz(DCount("fileref","table1","fileref = '" & [fileref] & "'"))

This works but unfortunately the AUTOREF field doesn’t hold the value and changes with every new record inputted.

What I actually need it to do is give me the total number of FILEREFS as of point of input and store it in the AUTOREF field that won’t change and instead increment by 1 each time.

Any suggestions would be really appreciated.

Cheers,

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-02-07 : 15:48:49

OK. Open the form in Design Mode.
Then Property Sheet of autoref control. Choose from dropdown list
field "autoref" as the control DataSource (instead of my formulae).
Nothing more with this control.

Now in Property Sheet of the form itself go to Events tab, then to
AfterInsert event, choose subroutine, click "..." and write into the
Sub code the below line (which in bold):

Private Sub Form_AfterInsert()
autoref = Nz(DCount("fileref", "Table1", "fileref = '" & fileref & "'"))
End Sub

weedavie22
Starting Member

24 Posts

Posted - 2004-02-07 : 21:08:33

Nice one Stoad that worked a treat.

Now I've tried to get Dcount to count the CLCODES from two seperate tables e.g.

AUTOREF = Nz(DCount("clcode", "Table1","clcode", "Table2", "clcode = '" & CLCODE & "'"))

It says wrong number of arguments

Doh! maybe its just not possible.

Cheers,

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-02-09 : 05:30:52

1. No need to wrap DCount() function into NZ(), because DCount
never returns Null value (other case with DMax()). So, you can
safely remove NZ() function from the "formulae".

2. D-functions can take as their 2nd parameter only 1 table/query.
So, create new query "myquery" with the following text:

select clcode from Table1
union all
select clcode from Table2
union all
select clcode from Table3

Now, the formulae goes like this:

=DCount("clcode", "myquery", "clcode=" & clcode)

Note: if clcode is of numeric data type you don't need to quote it.

weedavie22
Starting Member

24 Posts

Posted - 2004-02-09 : 05:50:36

Thanks mate, it seems to be working now.

I really appreciate the help you’ve given me with this.

Cheers

Dave

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources