Query with simple numbering.

[Megood]

This is my first post and after searching all over I still haven't found the answer that fits my problem. And quite frankly I found some harsh responses to questions that resembled mine, so please go easy.



I have a table that contains two correlating figures. I created a query that pulls the information for the specific show I am doing but I need to make a field that returns a number, one for each record, starting with 1 every time and counts up. I tried a couple variations on DCount function but that just didn't match what I needed. Autonumbering the table doesn't do what I want. I just need it to return a number that I could use in a report.


Now for a more detailed description in case there is a better way of doing it. I have a table that has a show field(that's what the query filters) and in each record I have 2 fields(unit 1 output and unit 2 input). I want each record to have a number so that my report can place a number in the correct place on the picture/diagram(already made and cant be changed from its configuration, represents something physical that needs to be seen). Eg. Show 1, Unit A, Unit B, 1. So on the picture can have a 1 on both the plug for unit A and Unit B, so that you can hook them up.

Any help would be great. Thank You. Let me know if I need to make anything more clear.

[June7]

What is the picture/diagram object and how does it connect to data?

Textboxes in report have RunningSum property. Set the textbox ControlSource to =1 and the RunningSum to OverAll. This can calculate a sequential number. I just don't understand how this relates to the picture you describe.

There is a query technique to calculate a sequential number but requires a unique ID field in table (the autonumber field can serve this purpose) or combination of fields to create a unique identifier. This example uses an autonumber ID.

SELECT *,
(SELECT Count([ID]) AS HowMany
FROM tablename AS Dupe
WHERE Dupe.ID < tablename.ID) + 1
AS Rank
FROM tablename;

Do you want to provide project for analysis? Follow instructions at bottom of my post.

[Megood]

Thank you for the reply, I will try this as soon as I can.



http://www.flickr.com/photos/43691696@N03/6941793676/

As you can see now, the for that is in use has the location of the equipment as A12 or E44. I am replacing the number that is currently on it with the actual name and the empty box below it is where I need to insert my new numbers. This is where I actually insert plugs into a physical unit and it has this layout and cannot change. I just want to be able to use the table I have with what goes to what and be able to put in a number starting with 1 in that box. I hope I make sense.

[June7]

Nope, don't understand any of this.

The original post talks about 'a show I am doing' and later post talks about 'location of equipment'.

I gave an example of how a query can generate a sequential number. You cannot apply that to your requirements?

[Megood]

Thank you for your help. I got the numbering issue fixed. Sorry it took so long. That project was put on the back burner for a bit.

My next problem is to create a report from that link I posed above. In that picture you can see numbers in the boxes and a blank box below it. As is right now they write a number in the blank box below that number. So say that A 24 has a 1 and B 37 has a 1, so you know to plug the cable into device A 24 and the other end of that cable to B 37 and so on with the next one showing 2.

As of right now I have the table with both the the units to plug into and not the number that corresponds to it that will be entered. I'm afraid its going to take a long time to create this form. I think I can use text boxes in the place of the blank ones that will show that number but I still need to figure out how exactly to do this. I hope this will help.

[June7]

You want a report or a form? Creating form or report to look like that to show blank boxes is easy. It might be difficult to show data in that arrangement. It looks like records are arranged horizontally (left to right). I doubt crosstab query can accomplish, for other methods review:
http://datapigtechnologies.com/flashfiles/crosstab.html
http://allenbrowne.com/func-concat.html
http://forums.aspfree.com/microsoft-...ry-322123.html

I think the last link has example closest to your requirements.

[Megood]

It needs to be a report. The links didn't seem to be what I need. I think what It'll take is a bunch of text boxes with the values of that is there and if it shows true enter the number corresponding for that query. That means all boxes will be tome my hand. I might be able to set up a numbering system by adding values to the letters and adding that to the number. Then I could increse the values by 1 for every box I make.