Can anyone help me find a way to assign specific locations to sorted record?

[Sethrutan]

This question is a bit involved, and I have to be somewhat vague due to the sensitive nature of the parts that we make, but here is the general idea:



There are 25 parts which have unique serial numbers and performance data. These parts are all going to be installed into one large system, in order for the system to perform correctly the parts have to be installed in certain locations. I can sort the performance data using 3 different queries to get them lined up, with one query telling me what the serial number for, say, 5 and 9 should go, the second query telling me where 24 should go, and the third query telling me the rest. The problem that I have, is getting that information into a report or a form. In excel (where I am currently doing this), we used macros that sorted everything, then just referenced specific cells to place the serial numbers in position order, but I have not been able to figure out how to do that in access.

I know that this description is somewhat vague, but I really need some help.

Thanks!

[ssanfu]

Welcome to the forum

Yes, really, really vague. Almost opaque... as in not clear at all.

Without a table structure, the SQL of a query, maybe some sample data, I don't see a way to help.


The object names (fields, tables, queries) don't have to be the real names and data can be fake. But there has to be something.....

[June7]

Agree with Steve. Without more info about data structure and relationships, cannot give specific advice.

If a single query as report RecordSource cannot serve, perhaps subreports are needed.

Regardless, I expect you need to use report Sorting & Grouping features.

[Sethrutan]

Ok, this is what I am trying to accomplish. Bear in mind the numbers and everything are all made up, but if you work from left to right you should get the general idea. The output should list the Positions in order, and then the serial number of the part that should go into it. Does that help?

Part	Performance data										Position
1	110		35	66		-35	56	L			A
2	120		25	60		-25	44	J			B
3	150		5	56		5	32				C
4	113		32	45		-32	23				D
5	117		28	44		-28	23				E
6	201		56	35		56	21				F
7	79		66	34			20				G
8	85		60	32			19				H	7
9	111		34	32		-34	11				I
10	116		29	29		-29	11				J
11	119		26	28		-26	5				K
12	137		8	26		-8	-8				L
13	166		21	25		21	-10				M	2
14	135		10	23		-10	-10				N
15	100		45	23		-45	-12				O
16	156		11	21		11	-25				P
17	168		23	20		23	-26				Q
18	177		32	19		32	-28				R
19	189		44	12		44	-29				S
20	165		20	11		20	-32				T
21	156		11	11		11	-34				U
22	168		23	10		23	-35	I			V
23	164		19	10		19	-45	K			W
24	133		12	8		-12					X
25	135		10	5		-10					Y	25
	25 always goes to Y		Absolute value of performance data minus nominal value	Sorted. Two largest variations from nominal assigned to specific points. (H, M)		performance value minus nominal	sorted, remaining values are assigned on alternating basis.	ect..

[ssanfu]

Nope, that didn't help me. Can't tell the relationships or which column is the serial number, etc.

Re-reading your first post, the only thing I can suggest is to create a table, name it something like "tmpRptData" with the fields you need and use the 3 queries you have now to populate the table - Append using the first query. Then use VBA to open record sets for the 2 & 3 queries and update the records.
Then use a query based on the "tmpRptData" table for the report/form.

You have a tough problem.

[June7]

Weirdest data manipulation I've ever seen. Totally nonsensical to me. Okay, "25 always goes to Y" is a fairly clear rule but why Y? What happens if you expand to 30 parts?

Maybe you better stick with Excel to get the order. Access can link to spreadsheet and build a report using the linked sheet as RecordSource.

[orange]

As Steve and June7 have mentioned, you are going to have to devise a clearer explanation/description/example so that readers understand WHAT you are trying to do.
We/I have no idea what

list the Positions in order, and then the serial number of the part that should go into it

means.

What would you tell someone who doesn't know you; who doesn't know your set up; who doesn't know Access nor Excel, What you are trying to do?
Plain, simple English???

An additional thought seeing as you are using Excel currently:

Could you have an array with 2 dimensions, 1) is position and 2) serial number?
You still need appropriate logic to identify the positions, and the associated serial number.
Perhaps just a list in an array is easier to understand than a bunch of numbers on a page (as per your post #4).
Just a thought for consideration.