Newbie query count help

[ironvid]

Hi

I have a column with data displayed as below

;00;0000000000M;00;0000000000N;00;0000000000O;00

Each ;00;0000000000M stands for the name of a linked image file

so ;00;0000000000M;00;0000000000N;00;0000000000O;00 would stand for 3 linked images to that file.

I Need some help in writing a query to count the number of linked Images

so ideally i would have a total number of linked images in the table

the column name is pages and the table name is records

thanks for any help

regards stephen

 

[Fizzio]

How you count these is dependent on how you store them. if you have 1 field that stores the image file names then to count them is simple. In a select query QBE grid, click the Sigma (looks a bit like €) then select count on the totals line for that field.

Does that help?

 

[ironvid]

thanks Fizzio

At risk of sounding like the complete idot that i am can you give me an example of what you mean

they are all stored in the same field but I don't understand how it knows where to seperate them

regards stephen

 

[Fizzio]

Maybe I'm getting a bit confused with your data structure. How is your data structued at the minute. You have already indicated that you have a table called Records (not very descriptive mind you - not a criticism but it makes your structure easier to follow for yourself if the table names actually mean something a bit more specific)
How are the records in Records structured?
You have implied that there is a 1-many relationship between File -> linked image, how is this structured in your Db?

I need more info before offering a solution I'm afraid. If you struggle describing your structure, post a zip of an empty Db for us to have a look at.

 

[ironvid]

ignore the names, just used those names to make it easy to describe the problem

I can't send the DB because it's a large sql2000 database I just use access for the frontend,but i have made a sample of the table in question in access

there no keys setup in this table

if you need to see the sql database then i can attach a script to create it on your system if needed

please see attached zip

thanks again

 

[Fizzio]

Thanks.

The way your data is structured will make it extremely difficult to perform a count on the linked image files in a query as you have stored more than one piece of information into one field ie a reference to many image files. (not normalised data)

To effectively process this data, it would be much easier to use a looping recordset in visual basic to interrogate the information and count the values.

With the names of the image files containing punctuation, you have a tough job coding this as you cannot use a simple instr() function to split the data into chunks, you will probably have to split it into known length chunks and count the occurence of each value. If you are familiar with VBA, it is certainly a challenge, if not, get back and I'm sure one of the coding wizards (ie Mile-O et al) will be able to knock up something.

 

[Mile-O]

You'd, to be honest, be better off with a second table specifically for this data.

It would require three fields:

an autonumber to be the primary key
a field that holds the primary key of the table you have now
and a third field to hold each piece of data individually.

 

[Jon K]

In these two records:
Record 1 -- ;00;0000000000M;00;0000000000N;00;0000000000O
Record 2 -- ;00;0000000000M;00;00000000001

do you want to count them as containing 5 linked Images, or just 4 because ;00;0000000000M is repeated?

 

[ironvid]

Jon
the linked image name is unique so ;00;0000000000M could only appear once

Mile-O-Phile
all the data is entered automaticly by an app which generates the file names and enters them into the relivent field.
this makes it impossible to have another table or change the structure of the exsisting

At the moment I estimate that there are 4 million plus images at present and it's growing by 40,000 - 60,000 images a day

Is it possible to count the number of charcters and then divde that figure by 15 to give a count for the column.

regards....stephen

 

[nickcrisp]

Hi


If every filename is 15 long then

Len([FieldName]) / 15

as an expression will work.

Nick

 

[Jon K]

With unique linked image names, Nick's expression should work.

 

[ironvid]

thanks for the help guys but can someone please explain how I use the expression

Len([FieldName]) / 15

this is totally new to me.

regards......stephen

 

[Jon K]

I have added two queries to your sample.

The first query uses Nick's expression to find the number of linked images in each record.

The second query (a Totals Query) sums the numbers.