I'm trying to create 3 like fields on a form that show the last 5 field records for "casenumber" in a specific table. I'm trying to get this accomplished without having to create new "sub queries". I am able to accomplish this with some sql but i'm trying to apply this to three different form data controls, each with a different "where" criteria.
SELECT TOP 5 MainDataTbl.CaseNumber
FROM MainDataTbl
WHERE (((MainDataTbl.CaseNumber) Like "*bs*"))
ORDER BY MainDataTbl.CaseNumber DESC;
Can anyone tell me how I can accomplish this in 3 different form field controls without having to create extra queries.
You have three different controls; you want each to show a different list of top 5 records based on different criteria, but don't want to create 3 different queries? The query structure can be consistent (variations in the WHERE clause), but three queries are needed I think. (instead of like *bs*) - an appropriate selection!
If you are looking for the top three entries matching the FROM/WHERE clauses of your SQL, your BEST bet would be for the three different form controls to be unbound. Then in the form's _Current routine, open a recordset for that query and do a .MoveFirst, then load a control. Do a .MoveNext and load a control. Do another .MoveNext and load the last of the three controls. One query, one recordset, three records, done - if you have at least 3 records returned by the query.
You can combine multiple subqueries into one query. That would then be a query as a data source for a continuous form.
But you could also place subforms or list boxes next to each other and display individual queries. It depends on.
Code:
CaseNumber Like "*bs*"
Maybe there are missing normalization steps.
When filtering for given atomic information, things might look different.
Thought game: The data origin of a list box or a form could look something like this, where the TIOP 5 values from several characteristics are displayed.
SQL:
SELECT
N.*
FROM
NormalizedTable AS N
WHERE
N.ID IN
(
SELECT TOP 5
X.ID
FROM
NormalizedTable AS N
WHERE
X.Attribute1 = N.Attribute1
AND
X.Attribute2 IN ("bs", "xy", "ok")
ORDER BY
X.Attribute3 DESC
)
ok so I should have better explained my origional post resulting all of the clarifications; however that is OK because this is all part of my learning adventure.
I have a single table I'm pulling from that has a field called "CaseNumber". A case number will start with one of four acronyms based on the location as to where the case occurred at followed by a series of 2 numbers indicating the year followed by a four digit sequential number. For sake of conversation lets use Washington, Oregon, California, and Arizona, as "properties". Based on that logic.
The table might have WA23-0001 - WA23-500 while at the same time the same table will have records for OR23-0001 - OR23-500, OR23-0001 - OR23-500, CA23-0001 - CA23-500, or AZ23-0001 - AZ23-500.
What i'm trying to do on an unbound form is have a text box represent the last 5 records of each location that provides feedback to data entry users of what the last 5 cases were issued for each site. Each site would have its own text box representing the last 5 records. This is for data entry reference that tells one the last 5 records to illustrate where the last person completing data entry left off.
I would never mash data together in a single field like this: WA23-0001 At some point in the past you must gave had to do the mashing, now you have to unmash. Store your data raw. Store each single data point in a single field.
Some of those identifiers have 3-digit sequence although you state should be 4-digit. If you want to use this data for sorting, need consistent structure - mashed together or not.
I was not responding to your post Pat, sorry if that was not clear. I would not mash data together like this: WA23-0001, WA23-500, OR23-0001, OR23-500, OR23-0001, OR23-500, CA23-0001, CA23-500, AZ23-0001, AZ23-500.
<state><year>-<sequence>
In fact, a great many consider such composite information to be great and intelligent, and imitation is very high. But very few can handle it.
For the requested TOP 5 per location, you would have to disassemble the intelligent key with your own intelligence, and then the field that shakes it out of the sleeve is significantly smaller.
Disconnecting is more or less easy, but still solvable. In any case, you then have calculated values for which index use is securely switched off. I call something like this in a database with an expected amount of data a gross error or catastrophe.
A case number will start with one of four acronyms based on the location as to where the case occurred at followed by a series of 2 numbers indicating the year followed by a four digit sequential number.
These are clearly three atomic pieces of information that would also be stored in three separate table fields and provided with a composite unique index.
SQL:
SELECT
Location,
CaseYear,
SeqNum,
Location & CaseYear mod 2000 & Format(SeqNum, "-0000") AS CaseNumber
FROM
AnyTable
The user has no place in tables and my queries. The composite value is displayed in forms, the user is happy. I can work and calculate with the atomic information underground, much more easily and quickly.
The rules of normalization also make sense in daily practice.
I don't have to show the user anything for a new CaseNumber, I can offer him MAX(SeqNum)+1 for the selected Loction and CaseYear immediately.
Using atomic information enables me to be variable, future-ready and easy to scale.
I can filter, count, sort, aggregate as I like, without any further preparatory work.
If the users get busy and need 6 digits for SeqNum instead of 4, if the year should be used with four digits, if there are extensions to the locations (number of locations, number and type of characters used), no problem: the reaction in extreme cases, this occurs in exactly one place - the composition and formatting of the expression for CaseNumber.
If I present such arguments to the user, he will very rarely insist on his own ideas.
I have a single table I'm pulling from that has a field called "CaseNumber". A case number will start with one of four acronyms based on the location as to where the case occurred at followed by a series of 2 numbers indicating the year followed by a four digit sequential number. For sake of conversation lets use Washington, Oregon, California, and Arizona, as "properties". Based on that logic.
The table might have WA23-0001 - WA23-500 while at the same time the same table will have records for OR23-0001 - OR23-500, OR23-0001 - OR23-500, CA23-0001 - CA23-500, or AZ23-0001 - AZ23-500.
What i'm trying to do on an unbound form is have a text box represent the last 5 records of each location that provides feedback to data entry users of what the last 5 cases were issued for each site. Each site would have its own text box representing the last 5 records. This is for data entry reference that tells one the last 5 records to illustrate where the last person completing data entry left off.
If you don't already have it in your single table, you should add a column for Site or something to that effect. This should be an easy one time update to the table and will simplify the whole process of getting your desired records per site info.
What is the primary key for the table that contains all of the CaseNumber fields? I hope it's not that CaseNumber field. If so, seriously reconsider going to a simple auto number primary key field.
Will those four locations ever change? Could more locations be added? This is important. I'll assume they are staying the same for now.
So five recent CaseNumber records copied into one text box on the form? How would you propose to format the individual bits of information? By a comma separated list or just spaces or what? Just explain exactly how the data is supposed to look like inside one of the four controls and what type of control we are talking about. If this is just for reference use only why not use subforms instead?
Potential Solution #1: Use an unbound main form that has three subforms that have as their recordsource each of the appropriate queries. This assumes that the user is just looking at the data and does nothing with it and does not intend on adding a new CaseNumber record from this form.
Potential Solution #2: Use a bound form that has three subforms that have as their recordsource each of the appropriate queries. This could allow the user to make a new CaseNumber entry while referring to the required last five cases at all four locations.
These four subforms can be placed on a Tab Control to make more efficient use of space. The user only has to select the appropriate tab to show the desired location data while they look at the data. If they want to see all locations at the same time, then ignore this suggestion. It's just an option.
This is the part I do not understand. Three different form data controls? There is no such thing as a data control. There is a text box control, a combo box control, a subform control, etc... precision matters when describing what your requirement is. You also stated that there are FOUR locations. Why only show three then? Maybe you are referring to three fields from the table that contains the CaseNumber, please confirm.
There are important reasons not to save calculated values. There are risks of data anomalies by creating, changing and deleting the basic data for calculation, and there are redundancies. Some also call this the 0th normal form.
Code:
' approach
Public Function GetCaseNumber(ByVal CaseDate As Date, ByVal Location As String, _
ByVal CaseYear As Long, ByVal SeqNum As Long) As String
Dim sRes As String
Select Case CaseDate
Case Is < #1/1/2023#
sRes = Location & CaseYear Mod 2000 & Format(SeqNum, "-0000")
Case Else
sRes = Location & CaseYear & Format(SeqNum, "-000000")
End Select
GetCaseNumber = sRes
End Function
Cases come with a date and possibly other attributes. From this one can derive the calculation of the composite value precisely as tried in the example, at the same time one would have an obvious history of changes.
A composite value like CaseNumber is only there for viewing, exports and label printing.
I wouldn't limit myself to that, in my world, in addition to manual entries in a form, there are also imports and transfers (for example from other stocks), which will then take place as a mass action and code-controlled.
It would take very serious reasons for me to agree with you in this case and to open Pandora's box.
What makes you think that? Of course, the calculation approach is based on the fact that the basic data remain constant, as do the attribute-related calculation methods.
If you change around with your eyes closed, you will experience a fiasco, in all variants.
If the intelligent number becomes a piece of information of its own, detached from the original derivation and only in nostalgic memory of its original meaning, then it must of course be stored independently in a table field.
But you can wait that long.
Whether this specific situation occurs for an application remains to be seen. Of course, you can also stand at the bus stop (and tie up resources), because there may be a bus in 5 years.
Thanks for the clarification. The many words allowed a different interpretation.
Incidentally, one should also orientate oneself to the specific case of the thread.
