Serial Number Search Form (enter list of serial and see latest related movement for each record) (1 Viewer)

Jordonjd

Member
Local time
Today, 17:54
Joined
Jun 18, 2020
Messages
96
Hi everyone.

In my database its normal to scan a serial number for an item and see where it was last used (Movement, Shipment, etc)
Currently I have a form with a textbox to enter the serial and then a query displaying the related movement from the movement table.

But I would like the user to be able to scan multiple serial numbers and produce the most recent movement for each serial scanned.

At the moment I have:

- Make Table Query (to supply me a blank local table to scan serial numbers into)
- I can have a Dlookup txt bringing a field from the Movement table Query

But I would like to display more related fields from the movement table, [Move ID], [Date], [Move reason], [Movement Type]

is there a method or producing this without having to have multiple txt fields with different Dlookups in them?

below is a very basic example, but its the serial and then the Dlookup field but i would like to pull more related info but in this kind of layout

1606135804464.png


For clarity in this screenshot above, those test serials all relate to the same movement so that is why the movement id's are the same in the second column)

then after they are done searching etc it will clear the table, or just have the make table happen each time they want to open the form

Any ideas? (thanks in advance everyone)
 

jdraw

Super Moderator
Staff member
Local time
Today, 12:54
Joined
Jan 23, 2006
Messages
15,364
Please provide a description in simple terms of the "business" to be supported with this database.
Seems there are Products of some sort and each has an identifying Serial number; and there may be various stages/phases in the development/handling that are the subject of these searches.

If you have a graphic of your tables and relationships, please attach to a post.

From experience, it's a good practice to NOT have embedded spaces in field names.

Good luck with your project.
 

Mike Krailo

Well-known member
Local time
Today, 12:54
Joined
Mar 28, 2020
Messages
1,030
By recent movement, I would equate that to current location of a particular serial number. So whatever the last entry was for the location of said serial number should be what your query is looking for. Do this for each serial number desired.
 

Jordonjd

Member
Local time
Today, 17:54
Joined
Jun 18, 2020
Messages
96
Please provide a description in simple terms of the "business" to be supported with this database.
Seems there are Products of some sort and each has an identifying Serial number; and there may be various stages/phases in the development/handling that are the subject of these searches.

If you have a graphic of your tables and relationships, please attach to a post.

From experience, it's a good practice to NOT have embedded spaces in field names.

Good luck with your project.
Hi, thanks for the quick reply.
(sadly i have a history of bad field/table naming although i thought the "_" undescore seemed to be ok)

Our business is refurbishing, building, handheld devices. Most of which have a unique serial number.

When these items are booked in, sent to the workshop, shipped, etc a stock movement is created and the relating serial numbers are captured as well.

In the event that sometimes units get mixed, lost, found, without paperwork someone may need to search the serial number to see the last time it was "Moved". This can range from 1 or 2 devices to a mixed tub of 50 items +

The relationship is just a "one to many":
tbl : Stock Movement
PK: MovementID

Tbl: SnforStockMovment
FK: MovementID
Serial number

I have no problems with pulling data from these tables or querying.

Im trying to build a form that the user can use in the specific situation of having many Serials to scan and provide their latest related movement.

I basically want a Dlookup field but i want to be able to pull other field data from the query the Dlookup is using, almost like Column(1,2,3) on combo boxes
 
Last edited:

Jordonjd

Member
Local time
Today, 17:54
Joined
Jun 18, 2020
Messages
96
Sorry its DLast that i was testing out, but these are the extra fields that i would like to populate instantly after the serial is scanned.

I was hoping to find something other than creating another 9 Dlast fields

1606140254353.png
 

jdraw

Super Moderator
Staff member
Local time
Today, 12:54
Joined
Jan 23, 2006
Messages
15,364
I have looked at your post #4 and have the following comments/observations.

General process steps appear/could be: (just tying to understand the process)

items are received from X
item booked in
(entered to inventory?)
evaluation?? to see if repairable or for parts??
sent to the workshop
assigned to Person PP
repaired/refurbished
tested
packaged
shipped
to Customer/retailer??
removed from inventory??
a stock movement is created

and the relating serial numbers are captured as well (existing number or a new assigned number??)
are there related financial records (sales,supplies...)
are there related Customer details involved..?

Underscores ("_") are fine. I was responding to
the movement table, [Move ID], [Date], [Move reason], [Movement Type]
 

Jordonjd

Member
Local time
Today, 17:54
Joined
Jun 18, 2020
Messages
96
I have looked at your post #4 and have the following comments/observations.

General process steps appear/could be: (just tying to understand the process)

items are received from X
item booked in
(entered to inventory?)
evaluation?? to see if repairable or for parts??
sent to the workshop
assigned to Person PP
repaired/refurbished
tested
packaged
shipped
to Customer/retailer??
removed from inventory??
a stock movement is created

and the relating serial numbers are captured as well (existing number or a new assigned number??)
are there related financial records (sales,supplies...)
are there related Customer details involved..?

Underscores ("_") are fine. I was responding to

Yes in a manner of speaking that is the general gist of the steps and other data is held and related in the DB.

This request about the form is for the specific scenario is "What are these devices doing here, where did they come from?"

Instead of scanning each serial and looking the history up individually i would like a quick method to be able to scan a list and see the most recent movement related to that sn

Scan the list:

Serial 1, move id xxx, 23/11/2020, x1, Order, Ref 1xxx, "ah it was last scanned to that order"
Serial 2 ….. etc etc
 

jdraw

Super Moderator
Staff member
Local time
Today, 12:54
Joined
Jan 23, 2006
Messages
15,364
And it really depends on your table structure and relationships.
Conceptually, you have a Table of Items with serial numbers, and a table of MovementTypes,
and a junction table eg ItemMovement.

In ItemMovement, fields such as
ItemId (or serial number)
MovementTypeId
MovementDate

You could check the Last MovementDate for each Item/serial number in your list. (you could put into temp table)

You could use a set up a shown in this link (read include vs exclude) to get the info you're looking for.
You might want to post a sample copy of your database with a few test records.
Good luck.
 

Jordonjd

Member
Local time
Today, 17:54
Joined
Jun 18, 2020
Messages
96
And it really depends on your table structure and relationships.
Conceptually, you have a Table of Items with serial numbers, and a table of MovementTypes,
and a junction table eg ItemMovement.

In ItemMovement, fields such as
ItemId (or serial number)
MovementTypeId
MovementDate

You could check the Last MovementDate for each Item/serial number in your list. (you could put into temp table)

You could use a set up a shown in this link (read include vs exclude) to get the info you're looking for.
You might want to post a sample copy of your database with a few test records.
Good luck.

Thanks again for the quick reply. i think in my attempt to explain the situation i made it more confusing.

The idea was to build a form that looks like an empty datasheet, with many empty columns
Then someone could scan a Serial number into column 1 creating a new record each scan,
As each column one is populated with the serial number input, column 2, 3, 4, 5, 6, etc would automatically be filled with the data from a query of my choice.

so its a live form retrieving and populating the rest of the row each time a serial is entered.

I can do the by creating Many DLast fields but this breaks the form with slowness, it provides the result and method i want but is no way functional sadly
1606144594425.png


Its no worries, i was just wondering of another method other than Dlast that could give this desired live action result

If i cannot find a live way of doing this then i will just create a form for the user to scan the whole list, click a button, and then i will query that list against my other data
 

Minty

AWF VIP
Local time
Today, 16:54
Joined
Jul 26, 2013
Messages
10,354
Left Join the serial number in your table to your movement query.

It should lt you add a serial number and magically display any related records.
 

Jordonjd

Member
Local time
Today, 17:54
Joined
Jun 18, 2020
Messages
96
Left Join the serial number in your table to your movement query.

It should lt you add a serial number and magically display any related records.

i've been playing around a bit but the recordset isnt updatable etc, tried separate queries etc to make the right side unique records..
 

Minty

AWF VIP
Local time
Today, 16:54
Joined
Jul 26, 2013
Messages
10,354
You could possibly get clever with a popup form to enter the scanned serial number - then refresh the underlying form?

Pretty sure with you should be able to create an updatable version though, can you post the SQL of the movement query?
 

Jordonjd

Member
Local time
Today, 17:54
Joined
Jun 18, 2020
Messages
96
You could possibly get clever with a popup form to enter the scanned serial number - then refresh the underlying form?

Pretty sure with you should be able to create an updatable version though, can you post the SQL of the movement query?
Well, because there could be multiple records on the movement table i had to "group max" for [move id], [Serial_Number] on an underlying query
so there would only be one result available for a serial number, (originally the Dlast solved that for me)

The idea is the Serial table stays empty until its used, then written over or emptied after.

I did the left join in the query

SQL:
SELECT Temp_Table_Serial_Search.Serial_Number, Qry_Temp_Serial_Search.MovementID, Qry_Temp_Serial_Search.StockID, Qry_Temp_Serial_Search.MovementDate, Qry_Temp_Serial_Search.[+/-], Qry_Temp_Serial_Search.Qty, Qry_Temp_Serial_Search.From_Location, Qry_Temp_Serial_Search.To_Location, Qry_Temp_Serial_Search.Move_Reason, Qry_Temp_Serial_Search.Ref, Qry_Temp_Serial_Search.SerialNumber
FROM Temp_Table_Serial_Search LEFT JOIN Qry_Temp_Serial_Search ON Temp_Table_Serial_Search.Serial_Number = Qry_Temp_Serial_Search.SerialNumber;
 

Jordonjd

Member
Local time
Today, 17:54
Joined
Jun 18, 2020
Messages
96
Otherwise, i think i will just create a form, using the Empty table, the user can scan the whole list of serials in there, then i'll run a query that populate a subform or something that is next to this list and sort both lists the same so they line up with one another
 

Minty

AWF VIP
Local time
Today, 16:54
Joined
Jul 26, 2013
Messages
10,354
If you do the last movement with a subquery rather than a DMax or DMin (DLast may not give you the results you want - be warned !) You may find that the query would let you join and the table field remain updateable?
 

Jordonjd

Member
Local time
Today, 17:54
Joined
Jun 18, 2020
Messages
96
If you do the last movement with a subquery rather than a DMax or DMin (DLast may not give you the results you want - be warned !) You may find that the query would let you join and the table field remain updateable?
I'm going to have another crack at it all tomorrow. I have some area's to look at now.

Thanks for the advice,

Thanks everyone else for their advice and input as well
 

AccessBlaster

Registered User.
Local time
Today, 09:54
Joined
May 22, 2010
Messages
5,824
FYI
It seems rare but the larger and older the data set becomes the more likely duplicates serials could creep in. Especially between competitors.
 

Users who are viewing this thread

Top Bottom