list two subfields in two separate tables in a single query

[botilismus]

hi. ı have two tables


first tables field names are "Stock Number" and "Technicial Specifications" for example;
Stock Number Technicial Specifications
4520116526 it will be black color.
4520116526 it will be long-edged.
5820156484 it will be red color.
5820156484 it will be short-edged.

second tables field names are "Stock Number", "Product Name" and "Unit" for example;
Stock Number Producted Name Unit
4520116526 Black Colour Pencil Piece
5820156484 Red Colour Pencil Piece

ı try to do these like this by using row numbers in query;

1. Black Colour Pencil
1.1. it will be black color.
1.2. it will be long-edged.

2. Red Colour Pencil
2.1. it will be red color.
2.2. it will be short-edged.

How can ı do this? Can you help me?

[Minty]

You won't do this easily in a query - but a report would be pretty simple.
Create your query to link the two tables and provide the basic data.

In the report group by the Product Name, and add the Tech Specs as the detail.

Row numbers in the report are a simple issue http://allenbrowne.com/casu-10.html

[botilismus]

Can I not do this with query? I need a query because I will use query with a button to send this to ms word later. Can you have any example database file about this?

[Minty]

It's not straight forward, particularly as you want it to reset per grouped record. http://allenbrowne.com/ranking.html#query describes some of the techniques required.
Why not use a report?
If you are exporting to word an access report can probably do a similar job, unless it's extremely fancy?

[botilismus]

Can you have any example by using report?

[Minty]

Nothing to hand - I only have a very complicated three level equipment listing that I created in SQL Server as it was too awkward to do in access queries, sorry.
What have you tried?
My initial suggestion would take 2 minutes with the report wizard once you have the query set up.

Forget trying to make it look pretty at the moment - just see if you can make it work using the inbuilt wizards to start with.

[botilismus]

Ok. At last can I send report to word later?

[Minty]

If you have to, but the formatting is not easy to keep.
Post up an example of the word document to see if it could be created in access, and then exported to PDF (which would keep the formatting).

[isladogs]

ı try to do these like this by using row numbers in query;

The Serialize function can be used to create 'row numbers' or a rank order in a query

Public Function Serialize(qryname As String, KeyName As String, keyValue) As Long

On Error GoTo Err_Handler


    'used to create rank order for records in a query
    'add as query field
    
    'Example Serialize("qry1","field1",[field1])


    Set rst = CurrentDb.OpenRecordset(qryname, dbOpenDynaset, dbReadOnly)
    
    rst.FindFirst Application.BuildCriteria(KeyName, rst.Fields(KeyName).type, keyValue)
    
    Serialize = Nz(rst.AbsolutePosition, -1) + 1


    rst.Close
    Set rst = Nothing


Exit_Handler:
    Exit Function
    
Err_Handler:
    MsgBox "Error " & Err.Number & " in Serialize procedure: " & Err.Description
    GoTo Exit_Handler
    
End Function

For example, in this query, the Serialize function is used to create a TableID field which serves as the row number:

SELECT Serialize("qryJSONFileTables","TableName",[TableName]) AS [TableID], MSysObjects.Name AS TableName, qryJSONFileTableNames.FileID, qryJSONFileTableNames.FileNameFROM qryJSONFileTableNames INNER JOIN MSysObjects ON qryJSONFileTableNames.TableName = MSysObjects.Name
WHERE (((MSysObjects.Type)=1) AND ((MSysObjects.Flags)=0))
ORDER BY MSysObjects.Name;

[botilismus]

Can you show this code on an access file please?

[isladogs]

Can you show this code on an access file please?

With pleasure
Attached is a simple example of student assessment marks for a fictitious school

2 tables - tblAssessmentMarks/tblAssessmentTypes

Various queries including three with rank order done using Serialize function
a) qryY7ScAss1MarkRANK - puts Y7 Science students in rank order by mark (descending order) - as several students have the same mark, the rank values can be repeated
b) qryY7ScAss1PupilRANK - same data but this time ranked in descending mark order by PupilID - so no repeated values for rank
c) qryY7HiAvgAssMarksRANK - Y7 History average marks by assessment in rank order

AssessmentRankQueries.zip

Hope that's enough to explain the idea

[botilismus]

ok thank you so much