Duplicate Data in Query

[HeyMoe]

I'm not quite sure if SQL can do this, but here goes.

Table1 contains a list of document scans, simply called DocScan001, DocScan002, DocScan003, DocScan004 etc.

DocScan001

DocScan002

DocScan003

DocScan004

DocScan005

DocScan006

etc

001 is the scan of the front of the document A, 002 is the scan of the back of the document A.
003 is the scan of the front of the document B, 004 is the scan of the back of the document B.
005 is the scan of the front of the document C, 006 is the scan of the back of the document C.


etc etc.

Table2 contains the actual names of the documents.

Certificate1

Certificate2

Certificate3

etc

I would like to create a query that shows me the name of the scan and the name of the document it is a scan of.
As you can see, it needs to duplicate the name of the document.

DocScan001	Certificate1
DocScan002	Certificate1
DocScan003	Certificate2
DocScan004	Certificate2
DocScan005	Certificate3
DocScan006	Certificate3

Any help would be appreciated.
Thanks.

[ArviLaanemets]

You need:
tblDocuments: DocumentID, DocumentName, ...;
tblDocumentScans: DocumentScanID, DocumentID, [SheetNumber], ScanNumber (SheetNumber field is optional, for case the document has more than single sheet, and it isn't possible from scan number to determine, to which sheet it belongs too - e.g. when the number of scans per sheet varies).

The query below will return the list of all document scans, along with document name, or a document name with empty scan number when no scans for this document was registered (and any other info you can read from those tables or calculate from read data)

SELECT doc.DocumentName, scan.ScanNumber FROM tblDocuments doc LEFT JOIN tblDocumentScans scan ON pg.DocumentID = doc.DocumentID

In case you don't want documents not scanned yet never displayed, you can replace LEFT JOIN with INNER JOIN in query string. Or you can user WHERE clause in LEFT JOIN query to display either only scanned or unscanned documents.

[HeyMoe]

Thanks for that Arvil.
But... I ****** up a little bit in my examples.
Sorry 'bout that.

The document names will not be consecutive. In numerical order, but there may be gaps in the numbering.
And the scan number is not in any way associated with the document name.
eg. Table2 DocumentNames may contain this:

Certificate11

Certificate45

Certificate72

Certificate111 So the Query output may be this: DocScan001 Certificate11 DocScan002 Certificate11 DocScan003 Certificate45 DocScan004 Certificate45 DocScan005 Certificate72 DocScan006 Certificate72 DocScan007 Certificate111 DocScan008 Certificate111 Again ... sorry but that.

[ArviLaanemets]

Before you can query anything, you must have this info stored somewhere. I.e. you MUST have some table, where all scans are linked with specific document. Otherwise you can only guess!

About document names - if you use autonumeric indexes (DocumentID in my example), then when registering documents, they can have whatever names you enter (But it will be wise to avoid duplicate names!). The name is used to be displayed whenever this is needed, and it is all for what it is used.

[HeyMoe]

Thanks Arvil.

you MUST have some table, where all scans are linked with specific document.

That's what I want the query output to do.

Maybe can't be done with SQL.
Maybe with VBA.

Cheers.

[ArviLaanemets]

[QUOTE=HeyMoe;517169...That's what I want the query output to do.

Maybe can't be done with SQL.[/QUOTE]
That's right! SQL reads what you have entered into tables, and displays it in format you want, or inserts this info into some another table.

Maybe with VBA.

It's possible. You have to read the info from existing tables pice-wise, check it based on some rules, and then write based on read info a table which you missed at start. But this can be only a starting step, because otherwise whenever someone registers a new document, or a new scan, you have to run this VBA procedure again. And you never (especially when you aren't a single user doing all this registering) can be sure, that always the naming rules you aplied at start are followed. And in case this happens, some scans will be linked with wrong documents (and those may be not the scans or documents added lately). So you'll have a lot of work for years in future guaranteed

[HeyMoe]

That's right! SQL reads what you have entered into tables, and displays it in format you want, or inserts this info into some another table.

[June7]

Scans table needs a field that contains ID of related Certificate record. If there will always be 2 (and only 2) scans for every certificate and can be sorted so that these pair records follow each other, a VBA procedure could be run to update this field for existing records. Then for future records, make sure the field is populated right away.

Provide your db for analysis. Follow instructions at bottom of my post.