Limiting the number of available records in a table, and recycling/reusing them...?

Colin@Toyota

What's an Access?
Local time
Today, 03:39
Joined
May 2, 2006
Messages
203
Well boys and girls, its about that time in the week where I have once again come up with a brilliant application for my little Access project, but have no idea how to make it happen.

I will start with a little background info... In the course of the parts recoveries we have different reasons for recovering parts, different people (locations) that request them, and different priorities for recoveries. For example, when a new model comes out, we will recover 100% of the parts for investigation by the factory... however, we may want to take a look at failed water pumps first. In this case, our recovery, until it is complete, will have higher priority than the 100%, otherwise we would not receive any parts.

This is because of the way our warranty system works... It assignes a three digit number to each part number of a recovery (between 1-999). The lower the number, the higher the priority.

So if I am setting up a 100% recovery, I would want to put it in the, say, 200's. This would leave room for an overlapping recovery to be inserted in the 100's where it would divert a certain number of parts away from the normal recovery stream. Then, once a user-defined date or quantity of parts has been reached, the corresponding number goes dead, and any of those part numbers are reunited with the original recovery stream.

Sticking to the example above, typically 100% recoveries would go for say... 2-3 years, where as an in-house recovery might be for 20 parts... Another difference is that the longer term recoveries are managed by the end date, where shorter term recoveries are by number of parts recieved.

What I want to do is be able to auto assign these numbers based on criteria derived from user-inputted data... (i.e. destination, start/end date, recovery type, etc.). Then, when the recovery is complete, whether based on date or quantity recieved, to delete the information associated with the 3 digit number, block its use for 45 days, and then be able to reuse the number for a new recovery.

Whew.

Thanks to anyone who can be bothered to read past the first paragraph, let alone offer any advice.
 
Um...maybe it's me but....what is a 'parts recovery' when it's at home, and what 'completes' one? Why do you talk about some recoveries in terms of years and others in terms of quantities?

If all you're wanting to do is make a db assign a priority number based on various user-entered variables that should be fairly simple as long as you can clearly articulate a formula which you'd like to use.

I'd start with the assumption that each has the lowest priority (999) then deduct a certain number of points if certain conditions are true.

deduct 100 if requestor is a high priority requestor
deduct 500 if model is new
deduct 200 if recovery Type = Whatever Or deduct 100 if Recovery Type = SomethingElse

Thus priority = 999 - Condition1 - Condition2 - Condition 3 etc

Or something like that. Just make sure that the maximum amount of all possible deductions doesn't exceed your base number or you'll end up with a negative value.

As for the blocking of the part number for 'recovery' for 45 days after completion...well, I'd probably have a date field in your part number table. When a recovery is 'completed' populate that field with the date, and then limit your available part recoveries to parts where the date field is null or is less than Dateadd("d",-45,Date).
 
Jeez... and I thought for sure this time I had explained myself well...

haha

Ok,
This application is going to be linked to the db a posted here a couple weeks ago... This is basically the other side of that db.

In the first one we were working with invoices associated with projects or SETRs (as we call them). Each different part recovery is a different project. Or, you can look at it like each SETR is recovering a different set of parts from a different set (or specific model) of vehicles. Each SETR also has a destination where the parts are to be sent, and the name of the person requesting the parts.

Some SETRs are designed as long-term, mass-collection campaigns, where some others are intended to be small-scale, isolated incedent investigations. The reason some SETRs completion criteria is a date is due to the sheer number of parts we might recover... that, and we don't need exactly 1000, or 10000, we need to recover all of them. This allows us to look back after the project's completion to evaluate the severity of the part failure (i.e. if we only recover 25 parts in a 3 year period, its not that big of a deal).

An SETR might request 10 water-pumps (one part number) from one or two models, while another might request 20 of each of 10 parts (i.e. right rear shock, left rear shock, right rear control arm, left rear control arm, etc.) from a single vehicle model, and on the very largest scale, a new model 100% recovery wants every part of every part number from the specified vehicle (This is accomplished by setting the part number as *****-***** or *).

I liked your idea about starting with 999, and subtracting a value for each criteria that is met/not met... In fact, I was thinking along the same lines... Only problem is that each part number of each model of each year needs its own unique line number. An example would be a recovery on water pumps from say 2007 and 2008 Corollas. There would have to be one line for 2007 Corolla water pump, and one for the 2008 Corolla water pump, even though the part number is the same...

I hope that clears it up some.

:o
 
Well, I vaguely remember your db (had four days off and tried to forget everything :)). I think I helped you with fixing a combo box notinlist issue and then a mismatched datatype issue in the keyfield link between two tables.

In any case, if this is the same db, I think you need to really consider your table structure and relationships.

It sounds like you need to track (have tables for) Parts, Models, Makes, Projects(SETR), Locations, Invoices, and then you need to have some junction tables as well.

For example, since you want to many models per SETR you will need a junction table to link SETR with various Models. Let's call it 'SETRModels'. This links to the SETR table using the SETRID field, and to the models table using the ModelID field.

And, since each SETRModel may list many parts, you also need a junction table to link a SETRModel record with each part number. Thus your SETRModel table links to your SETRModelParts (junction)table via the SETRModelID field, and the Parts table is linked to the SETRModelParts (junction) table via the PartID field.

If you have a target amount for each individual part involved in a SETR, you need to include a quantity field in the SETRModelParts table. If you just want one quantity to apply to all parts involved in a SETR, then that quantity field would be better in the SETR table. If you want the quantity to be specific to each model in a SETR then you would go with the STRModel table. Similarly, if priority is common to all part/model pairs linked to a SETR then the priority field belongs in the SETR table. If, however, priority might differ within a SETR for certain combinations of model and part number then that priority field belongs in the relevant junction table.

If you want to track parts by model then that suggests another junction table between parts and models.

I think the problem you have is that your current db table structure does not come close to what you need. You should search and learn about table normalization. Then identify all the entities you wish to track. Have a look at the relationships in the example db I threw together for you if my post is unclear.
 

Attachments

I have been reading up on table normalization all afternoon... I will take a look at that sample tomorrow, since its quitting time. I have also come to terms with the fact that v2.0 of my db should probably be started from scratch if i want it to do all this and more.

Thanks for the direction!
 
surely you can do this quite simply

have a table with keys limited by validation rules to be no bigger than 999, with some data indicating what the key represents, when it expires etc etc

link your actual data to these. later when you dont want the old data for a given key, say no 245, simply throw it away or archive it somewhere, then you can reuse the key.

i don't really quite understand how you are using the keys though
 
These line numbers are to be used like locations in a warehouse. Each number represents (to our warranty system) each part, from a vehicle, under a specific SETR (project).

I have laid the groundwork for most of my table and set up the relationships (thanks to CraigDolphin for the help with that), but now I am trying to figure out how to best setup a table or tables for my line numbers. The table that contains the line number needs to act like a collector, as there will be a piece of information from almost every table in it, and each line as a whole will be emailed to a warranty administrator (containing line number, SETR, model, year, part-number, etc.).

So, as I said before, we can only have between 001-999 as a valid line number. To further break this down, recovery types have different priorities (which I mentioned) that go something like this:

CDM = 001-100
QA investigation = 101-300
CQE after C/M = 301-450
CQE investigation = 451-600
100%/Blanket = 601-999

I am thinking maybe the best way would be to set up a table with a pre-defined number of rows for each priority section? So a CDM table with 100 rows pre-numbered, then a QA investigation table with 200 rows pre-numbered, etc. Each table would also have the fields that need to be sent to warranty, and correspond to existing ones in the other tables. In the line number tables, the fields would remain blank for each record until populated using an update query (or append?), containing information based on conditions taken from the data users will be inputting.

Or is there some "next available record meeting the following criteria" function in Access that could be manipulated, and allow this data to be contained in one table?

I'm not looking for a quick answer here... I have a little over 2 months to deveop this project, and I really don't want the answer handed to me. I can be pretty stubborn, and will find a way to make this work, but I appreciate any and all directions, suggestions and advice I can get.

Thanks to all who read this!
 
Colin,

I'm not very sure that I really understand how you intend to use this db so it's hard to be certain but after re-reading and thinking about your posts I think what you're talking about here is a variation of an inventory tracking database.

Let me see if the following is correct:

1.An engineer requests a 'SETR', or a recall, of certain parts, from certain models and years, with a requested quantity for each part.

2.Automotive dealers fix cars that come into their dealerships. The items (parts) that are removed from these vehicles are listed on an invoice and some may be required by a SETR, or competing SETR's with differing priorities.

3.You need a way to automatically assign the specific items removed from a car to a SETR, and get it sent to the requesting engineer. Once the total number of a specific part that was requested for a SETR has been met, then you don't want any more of that part to be sent to that SETR's engineer.

If the foregoing is correct, then the attached db should get you started on the right path. Here, I'm envisaging that you have some kind of central warehouse or data center, where invoices for a model of car with lists of parts (items) are supplied. In the Invoices form, when you add an item to the invoice it uses queries to determine which SETR's quotas have not yet been met for the part and assigns that item to the highest priority SETR.

You can set-up or edit SETR's using the SETR form.

The key to this approach is that we actually track specific items (parts) and have a table that assigns an item to a SETR. The queries count the items in that table from each model/year that are associated with each SETR and compares it to the requested quantities. So, as you add or delete items from that table the queries automatically re-calculate the total on the fly.

As you'll see, this requires a few more tables than I had previously suggested. The db is far from complete, and, if you plan on making this a multi-user db situation then you'll need to rework it to avoid the use of global variables etc.

Have a look and let me know if I'm even on the right track here.
 

Attachments

Last edited:
1.An engineer requests a 'SETR', or a recall, of certain parts, from certain models and years, with a requested quantity for each part.
Correct.

2.Automotive dealers fix cars that come into their dealerships. The items (parts) that are removed from these vehicles are listed on an invoice and some may be required by a SETR, or competing SETR's with differing priorities.
Also correct.

3.You need a way to automatically assign the specific items removed from a car to a SETR, and get it sent to the requesting engineer. Once the total number of a specific part that was requested for a SETR has been met, then you don't want any more of that part to be sent to that SETR's engineer.
Still correct.

However, after a discussion with the keepers of our ancient warranty system, I have been informed that with the current warranty tracking system, there is no way (or no one willing, I'm not sure which), to set up a counting system that would be able to inform my access db of the current status of on-going parts recoveries.

This means that for now, I will have to give up on the tracking system, and resort to having a pop-up when the EndDate = Today that asks the user to check if the requested quantity of parts has been received. Then have it set so that when EndDate + 45 = Today, the SETR is automatically set to inactive (for billing purposes we need extra time after the recovery is complete - mostly waiting for dealer work orders).

So the biggest part I need help with right now is deciding what to do about these line numbers... I still need to be able to have them emailed to the warranty admin with all the pertinent information, so that the same info can be put into the warranty system.

If you have any more ideas to share, they are more than welcome!

Thanks Craig
 
Hmm. A shame...thought I'd done well with that last one. :( So, back to the drawing board. If you're no longer tracking parts, what is the point of the priority field?

...and resort to having a pop-up when the EndDate = Today that asks the user to check if the requested quantity of parts has been received

What happens when the enddate is a weekend? You'll need to modify this to deal with weekends and holidays when, presumably, no one will be using the db to do the check. How will the user know if the requested quantity of parts has been received if you're not tracking this information? If you get the information from somewhere, what format will it take and what exactly will it look like?

I still need to be able to have them emailed to the warranty admin with all the pertinent information, so that the same info can be put into the warranty system

I'm still hazy on what you mean by the 'line numbers' you refer to. So, let me paraphrase and see if I'm, interpreting correctly. Is it something along the lines of :

"I want to send the warrantee people a flatfile/spreadsheet listing the details of each SETR that is currently expiring (based on date) with one line for each unique combination of SETR, Model, and Part Number showing the quantity required."

If so, then that should be no problem to do using the structure I created. The only addition required is an ExpirationDate field in the SETR table. Of course, there are now some redundant tables like Items, ItemInvoices, Invoices, SETRItemAllocations. You'll need to remove queries that depend on these, and update forms to reflect these changes (like adding the ExpirationDate field to the SETR form etc)

Assuming you would export the list weekly, you could create such a query with the following SQL to generate a list for all SETR's that expire this week, showing setr number, vehicle model, part number, and SETR requestor and address.

Code:
SELECT SETR.SETRNumber, SETR.ExpirationDate, [ModelYear] & " " & 
   [ModelName] AS Vehicle, Parts.PartNumber, [EngineerName] & ": " &
   [LocationName] & ", " & [LocationAddress1] & (", "+[LocationAddress2]) 
   & ", " & [LocationCity] & ", " & [LocationState] & " " & [LocationZip] AS 
   SendTo
FROM (Locations INNER JOIN (Engineers INNER JOIN SETR ON 
   Engineers.EngineerID = SETR.EngineerID) ON Locations.LocationID = 
   Engineers.LocationID) INNER JOIN (((ModelNames INNER JOIN Models ON 
   ModelNames.ModelNameID = Models.ModelNameID) INNER JOIN SETRModels 
   ON Models.ModelID = SETRModels.ModelID) INNER JOIN (Parts INNER JOIN 
   SETRModelParts ON Parts.PartID = SETRModelParts.PartID) ON 
   SETRModels.SETRModelID = SETRModelParts.SETRModelID) ON 
   SETR.SETRID = SETRModels.SETRID
WHERE (((SETR.ExpirationDate) Between DateAdd("d",-Weekday(CDate
   (Format(Date(),"Short Date"))),CDate(Format(Date(),"Short Date")))+1 And 
   DateAdd("d",-Weekday(CDate(Format(Date(),"Short Date"))),CDate(Format
   (Date(),"Short Date")))+7));

Once you have tweaked the query, export it to whatever format you want using the docmd.transferspreadsheet function. Or you could create a report based on the query and send them a printout or rtf file of that.

I'd be tempted to send a paper printout if you really think there's an element of unhelpfulness with the warrantee people. Make them enter it manually as punishment for attitude ;) Maybe I'm just petty like that :D
 
Hmm. A shame...thought I'd done well with that last one. :( So, back to the drawing board. If you're no longer tracking parts, what is the point of the priority field?
You did very well sir! We are still tracking part numbers and quantities with an information portal we have setup online with our third party shipper. So the priority and the need to check if the correct quantity has been recieved are still applicable.

Basically, our warranty system can only track up to 999 part recoveries at any given time. These are where the line number comes in... Every claim that a dealer makes against the warranty system, the part number is compared to each line of the warranty system to see if it matches... Starting with line 001. So if we have a 100% recovery in, say, line 087, and a random parts recovery for water pumps in line 502, the part will always be allocated to the 100% recovery. The system finds the earliest match, stops comparing, and assigns the part.

This means that we need a way to format the list of recoveries in such a way that the smaller recoveries will be looked at first. They are short term recoveries... usually about 3-6 months, where a 100% will be two years. We want to satisfy the conditions of the smaller recoveries before the bigger ones.

I realize this is not the best, most straight-forward way to go about this, but I have to work within the constraints of the current system... at least until the warranty system gets updated.

What happens when the enddate is a weekend? You'll need to modify this to deal with weekends and holidays when, presumably, no one will be using the db to do the check. How will the user know if the requested quantity of parts has been received if you're not tracking this information?

Good question! Any ideas on how I might go about that?

If you get the information from somewhere, what format will it take and what exactly will it look like?

It will be from the information portal (read: website), that I mentioned above. Entering the data will simply be typing the number of parts recieved into a text box or something in the Access file.

Is it something along the lines of :

"I want to send the warrantee people a flatfile/spreadsheet listing the details of each SETR that is currently expiring (based on date) with one line for each unique combination of SETR, Model, and Part Number showing the quantity required."

Yes, and I want to send the warranty people similar file when new SETRs are created (including SETR #, part number, model, quantity req'd, etc.)

Actually... the warranty people we have are amazing... its more the system I am frusterated with. Its simply so old, that there really is no point in trying to fix it or patch it up any more. A new system is being developed, but wont be operational for 2-3 years.
 
Ok...I think the issue here is that you're wanting to building a db that has to interface at some level with at least two pre-existing data systems: the website db and the warrantee system. It also has to fit within an existing real-world process. There's no point in me trying to help you further without understanding the whole system since it can't stand-alone.

I know nothing about either of the two data sources except that they exist. I still have very little idea of the process itself either.

Can you explain the process so I can understand it better.

Let's say, a 2003 corolla has to have the catalytic converter replaced. What is the chain of events that needs to happen, and what role do you want this db to play in that chain of events? Think about information flow. What information is transmitted, by who, and when in the process.

These are the bits of the process I can piece together from what you've said so far.
________________________________________________________
1.Engineers requests a parts recovery (SETR#1) for 2003 (Year) Corollas (Model). Requested parts include 100 (Quantity Requested) catalytic converters (Part Number). They send this request to you.

2.This database receives all the information about SETR#1 (user input) and generates a flatfile /spreadsheet which is exported to the 'Warantee System'. Information exported includes SETRNumber,Requesting Engineer and Address,Model,Year,PartNumber,Quantity Requested, SETR_Priority, SETR_ExpirationDate.

3.At an unspecified frequency, this database checks for SETR's reaching their expiration date, and exports information about those SETR's to the Warantee system. This information includes SETRNumber,Requesting Engineer and Address,Model,Year,PartNumber,Quantity Requested.

4.A dealership replaces the catalytic converter. Dealer knows: Model, Year, Part Number. Broken catalytic converter is located at dealership.

5.Dealer inputs information about model, year, part number to the website data source.

6.A user looks up information from the Website, and adds to this db (daily?). Information obtained from website = Model, Year, Part Number

7.Dealer also sends an invoice to the warantee system. Invoice includes model, year, part number.

8.The Warantee system looks up this combination of model (Corolla), year (2003), and part number (CatConverter) versus some table of information. Can I assume that this table includes a line for each combination of
SETRNumber,vehicle Model and Year, and Part Number. Other information in that table will include the priority of the SETR and the ExpirationDate. This table also has a key field called 'LineNumber' which, by itself, is meaningless to your db or to the website.

They discover that SETR#1, which has not yet expired, has the highest priority request for that same combination of model, year, and part number. (Note: no test has yet been made regarding quantities)

After this is a mystery to me

............................................................................................

Assuming all that is correct, here's what I'm still trying to understand:

A>What does the Warantee system do with the information resulting from 8?

B>How does the warantee system (or you) ascertain whether the requested quantity has been delivered? At what point in the process is this check made?

C>If warantee people send you information at any point, what does it look like and what do they want you to do with it?

D>Why do you re-export the SETR information when it has expired?

E1>If you are counting model-year-partnumber combinations without linking them to SETR per my last example, what do you do with that information?

E2>If you provide this information to the warantee department, why only do this once the SETR has expired, rather than daily so that they have up-to-date information to use?

E3>If they use the invoice information to keep their own counts, why are you duplicating their tracking effort at all?

F>Who tells the dealer where to send the broken catalytic converter?

G>Who tallies the part against the Quantity Requested of the SETR? Or who provides the 'number received' information?

H>Specify the frequency in part 3 :)
 
Last edited:
You have a remarkable grasp of how things work here for an outsider, kinda makes me think maybe my explanaitions haven't been terrible... I will clarify a few things though.

When I receive the SETR from an engineer, I input the information into an excel file that allows me to keep track of what ongoing projects I have. I start by assigning the SETR a department code, which is a two digit code based on the recovery type, and the parts' final destination. From here, we have a spreadsheet for each of the "priorities" I mentioned before... One for CDM, one for CQE-LA, one for QA, etc. each of these contain the line numbers I keep talking. So, the CDM sheet will have the numbers 001-100 in column A, the CQE-LA will have 101-200, etc. There are also fields for Department Code, Start Date, SETR, Analyst, Part Number, End Date.

Then, I use this information to fill out a form in a seperate system called PRMS (Parts Recovery Management System). All this system does is collect information, and send it to the warranty administrator when a new SETR has been started, and when the End Date = Today.

The warranty admin then inputs the data sent by the PRMS into his warranty system (an old Legend based system, if anyone knows what that is anymore!) Basically here is where the line numbers come into play. They are not used by anyone except the warranty administrator; they are merely a reference number by which he can go back into the system and delete the information contained in the line when the SETR (part recovery) is completed (either because the desired quantity was obtained, or the End Date = Today). Think of the line number as a location for each individual part number contained in an SETR.

Basically, these three systems are totally seperate, which is extremely inefficient, as the same data is entered three times...

Now, at the dealer end, when the dealer performs warranty work on a vehicle, they electronically submit a claim. Each night, these claims are run against the warranty system's db containing the line numbers, SETR info, etc. If a match is made (by part-number + model + year, etc.), the claim is flagged, and the dealer is sent a request that assigns the relevent Department Code, and asks them to send the part to our third party shipper. Our third party shipper then acknowledges the claims if the parts are received by them. At this location, parts are sorted by their Department Codes, grouped together, and shipped en-masse to the desired destination.

This list of matched claims is then uploaded to this information portal I spoke of. However, there is no automated count of parts received for each part of each SETR. Here is where we can perform a manual check.

Currently, warranty does not send me any information, unless I specifically request it. However, this same text string that is sent to our information portal could easily have my access program added as another recipient.

Ok, I think the above covered your questions A, B, C, E2, F...

D)What do you mean by re-export the SETR information? Once an SETR is complete, all I need to do is to archive the related information just in case it is ever asked for...

E1)Model-year-part combos ARE linked to SETRs.

E3)Invoice information is stored, but like I said... there is no automated counting, or SETR stop in the current set up. The info is there...

G)Right now, it is based on the PRMS sending warranty and email telling them to stop the recovery, based on End Date = Today.

H)This is the PRMS, and it runs the check daily.

Whew... I hope I managed to answer everything. We should get prizes for this thread having the most words in the fewest replies! This is turning into a novel! Hopefully once it gets figured out, it will be of some use to other people!!!

Thanks!
 
A novel? Yep. Helpful to others? Unlikely...it's too specific to your situation. :D

Do you create the line number and the warantee administrator just takes the line number you provide, stores it, and later uses it to find that record again in his system, or does the warantee adminstrator create the line number?

At this location (3rd party shipper), parts are sorted by their Department Codes, grouped together, and shipped en-masse to the desired destination. This list of matched claims is then uploaded to this information portal I spoke of. However, there is no automated count of parts received for each part of each SETR. Here is where we can perform a manual check.

Specifically, what information is in this list?

As I see it your process is this:

You create a SETR from a request, and give this to 'Warantee guy' along with various details like a 'line number' that uniquely identifies each combination of SETRnumber, Model, year, and part number as well as a department code and expiration date.

Dealer contacts warantee guy with combination of model, year, part. Warantee guy finds that combination matches one or more 'line numbers' in his tracking system and sends dealer the Department Code associated with the SETR identified by that line number. Dealer sends part to third party shipper along with department code.

Important: Is the line number also provided to the dealer and 3rd party shipper?

If the parts are received by the 3rd part shipper, they input the model-year-part number combination into a web portal, (or are they just uploading a claim number provided by warantee guy?). What about line number and department code? Do they upload that info to the portal too?

If all you can download from the web portal is part number, model and year and even department code, you can tally amounts received for those combinations for a given time period, but you can never know if the quota is filled for any particular SETR. You can only know if the combined total requested for that combination (by all SETR's that have not expired by the end of that period) is more or less than the total number received.

Department code might help you refine that a little so that your total aggregate received for a department is compared to the total aggregate requested by all non-expired SETRs for that department.

Maybe that level is adequate for your needs, I dunno. But it seems to me that the only hope you have of tracking efficiently is if line number is transmitted from warantee guy to dealer to 3rdparty to information portal, along with the combination information of model-year-partnumber.

In any case, from what I can tell so far, your db needs to serve three primary functions.
__________________________________________
1. Create new SETRS and export information about new setr's to warantee guy: including a unique line-number key field to identify each unique combination of SETR, model, year, and partnumber, along with the quantity requested and SETR expiration date.

AND

2. On the day a SETR expires, tell warantee guy to cancel all line numbers associated with that SETR.

AND

3a. Receive combination-only data from web portal that allows tallies of part-model-year combinations to be made for a given time period. If total received for that combination exceeds the total requested by all non-expired SETR's, inform warantee guy that line-numbers x,y, and z (etc) have been filled (i.e., all line numbers for that combination of model-year-partnumber for all active SETR's ).

OR

3b. Receive combination and department code data from web portal that allows tallies of part-model-year combinations to be made for a given time period for each department. If the total received for a combination for a department exceeds the total requested by all SETR's from that department, inform warantee guy that line-numbers x,y, and z (etc) have been filled (i.e., all line numbers for that combination of model-year-partnumber for all active SETR's from that department).

OR [assuming you created the line number in the first place, not warantee guy]

3c. Receive combination and line number data from web portal that allows tallies of part-model-year combinations to be made for a given time period for that line number (and therefore can be identified to SETR within your db ).
If the total received for that line number exceeds the total requested by the SETR associated with that Line Number, inform the warantee guy that the specific line-number has been filled.
_____________________________________________________

In terms of preferentially serving one SETR/Line number over another using a priority field, that really has to happen at warantee guy's end of things. You could provide a priority field they could use to pick one line number over another, but they will have to modify their system to check for that when comparing the dealer-supplied information against their database.

Your description of the spreadsheets and line numbers confuses me, but I'm hoping the preceding cuts past that.
 
Last edited:
Specifically, what information is in this list?

All information concerning the warranty claim - some of which is applicable to us, some not. It contains model year, model, trim level, VIN, in service date, r.o. number, mileage, r.o. date, part number, etc.

1. Create new SETRS and export information about new setr's to warantee guy: including a unique line-number key field to identify each unique combination of SETR, model, year, and partnumber, along with the quantity requested and SETR expiration date.

AND

2. On the day a SETR expires, tell warantee guy to cancel all line numbers associated with that SETR.

AND

3c. Receive combination and line number data from web portal that allows tallies of part-model-year combinations to be made for a given time period for that line number (and therefore can be identified to SETR within your db ).
If the total received for that line number exceeds the total requested by the SETR associated with that Line Number, inform the warantee guy that the specific line-number has been filled.

YES! This is what I want it to do!

For number 2 there, I would rather it send an email to me, so that I can make the final decision to cancel an SETR. Or at least set it up to send an email if end date is reached, but no parts have been sent.

In terms of preferentially serving one SETR/Line number over another using a priority field, that really has to happen at warantee guy's end of things.

This is done through the line numbers... because the claims are compared to the warranty db records, starting with line 001, then 002, etc. So the smaller the line number I assign an SETR, the higher the priority.
 
Ok. So a light has just turned on here. The line number does NOT just act as an identifier for a unique combination of SETR, Model, Year, and PartNumber, but it ALSO acts as an indicator of priority: lower line numbers get served before higher line number. And the line number therefore must always be a number between 1 and 999. Line numbers will be recycled over time.

?

If so, then we're talking about a very different situation wherein the line numbers themselves are an entity that we must track to ensure that a given line number is only assigned if it is not already in use.

Now I begin to better understand your original question about how to set the 'priority' number. When I suggested you calculate it, I did not understand all the roles that this number had to fulfil. I'm afraid, calculating this is quite out of the question. You're going to have to deal with vba to loop through recordsets and assign the first available number based on a series of rules that you will have to define.

Off the top of my head, the code will have to be able to answer these questions:

1.We need to determine whether a line number can be re-used immediately after it has been expired or filled, or if there should be a lag period/ If so, how long?

2.If all line numbers in a recovery type's preferred range are already in use, should the db assign a line number in a higher priority range or a lower priority range.

3.If all line numbers from 1 to 999 are already in use, what should the db do?

Also, we need a table of preferred ranges for line numbers vs recovery types. This must include recover type, minimum preferred value to use, and maximum preferred value to use.

This side of things is complex but doable.

However, regarding your last post. You said you wanted to be able to do option 3c. 3c requires that line number be includied in the information that you can download from the information portal.
3c. Receive combination and line number data from web portal
Yet line number was not listed in the information that the 3rd party receiver uploads to the web portal.

All information concerning the warranty claim - some of which is applicable to us, some not. It contains model year, model, trim level, VIN, in service date, r.o. number, mileage, r.o. date, part number, etc.

So, something has to give here because it appears that your stated goal is incompatible with the available information at that stage in the process. Either the line number information is available from the web portal (then 3c is possible), or it isn't, and you have to settle for options 3a or 3b. And again, department code was not in that list, so really, your only option appears to be 3a.

No database in the world can tell you something that you haven't already told it. So this isn't a db problem, this is an information flow problem.

Now, there is still another option here. Let's call it 3d.

3d. Only the model,year, part number combination is available from the web portal. This is entered into your db and the db assigns the item to the highest priority line number with a matching combination at the point of entry. (Exactly the same process that your warantee guy does when he's processing the claim and deciding which department code to send to the dealer). THEN the db counts up assigned items by line number and checks to see if the quantities are filled.

Now, there is potential here for a serious misalignment between your db system and the warantee guy's system if the logical process to select the highest priority line number do not match exactly. However, if they simply take the lowest value line number with a matching combination to decide which department code, then you should be ok.

So, your life will be considerably easier if you can download the line number along with the rest of the combination from the web portal (3c). Your life gets much more hairy if you need to go with 3d.

That said, the last example I built you provides much of the table structure you will need to achieve this side of the equation. You will need some more tables to track line numbers, however. Unfortunately, I'm short on time today.

I will say, this is not a trivial db situation. I'm prepared to help further: might even be able to post a working example eventually. But this will take a considerable amount of time. Just to clarify: you need to create an inventory tracking system for items, you need to code a vba module to make the assignments between specific items and line number, you need to create an inventory tracking system for line numbers, and you calso need to build forms/queries etc to faciliatate data entry and all the rest. Plus you want to automate emailing etc. Big job!
 
Ok. So a light has just turned on here. The line number does NOT just act as an identifier for a unique combination of SETR, Model, Year, and PartNumber, but it ALSO acts as an indicator of priority: lower line numbers get served before higher line number. And the line number therefore must always be a number between 1 and 999. Line numbers will be recycled over time.

YES!! Exactly!

1.We need to determine whether a line number can be re-used immediately after it has been expired or filled, or if there should be a lag period/ If so, how long?

It would have to have a lag period of 45 days.

2.If all line numbers in a recovery type's preferred range are already in use, should the db assign a line number in a higher priority range or a lower priority range.

This scenario should not occur, but if somehow it did, we would have to assign a higher priority.

3.If all line numbers from 1 to 999 are already in use, what should the db do?

If it ever comes about that we have more than 999 individual part recoveries ongoing, this db will be the least of our problems!

Also, we need a table of preferred ranges for line numbers vs recovery types. This must include recover type, minimum preferred value to use, and maximum preferred value to use.

CDM = 001-100
QA investigation = 101-300
CQE after C/M = 301-450
CQE investigation = 451-600
100%/Blanket = 601-999

However, regarding your last post. You said you wanted to be able to do option 3c. 3c requires that line number be includied in the information that you can download from the information portal.

Apparently, I deleted the wrong option. I was going for 3b.

So, something has to give here because it appears that your stated goal is incompatible with the available information at that stage in the process. Either the line number information is available from the web portal (then 3c is possible), or it isn't, and you have to settle for options 3a or 3b. And again, department code was not in that list, so really, your only option appears to be 3a.

The line number is not included in the web portal. As I mentioned before, it is used in the warranty system as a placeholder, that (by design or fluke) also acts as a priotising number set. The department code is included - it lets our third party shipper know where to send the parts.

3d. Only the model,year, part number combination is available from the web portal. This is entered into your db and the db assigns the item to the highest priority line number with a matching combination at the point of entry. (Exactly the same process that your warantee guy does when he's processing the claim and deciding which department code to send to the dealer). THEN the db counts up assigned items by line number and checks to see if the quantities are filled.

Currently, the line numbers are assigned as a new SETR's information is being input. I choose the line numbers to apply to SETRs based on the recovery type AND my knowledge that the warranty db is read from 001-999.

I will say, this is not a trivial db situation. I'm prepared to help further: might even be able to post a working example eventually. But this will take a considerable amount of time. Just to clarify: you need to create an inventory tracking system for items, you need to code a vba module to make the assignments between specific items and line number, you need to create an inventory tracking system for line numbers, and you calso need to build forms/queries etc to faciliatate data entry and all the rest. Plus you want to automate emailing etc. Big job!

Craig, I truly appreciate all the help and advice you have given so far. Instead of spending a bunch of your time making this db for me, I wonder if you might be willing to guide me in its construction. I am very interested in learning more about coding and VBA in general... I would rather learn than have it handed to me, but as I've said before, I will take all the help I can get!!

THANKS!
 
Handing a man a fish: one minute
Teaching a man to fish: an hour
Teaching a man to fish successfully: a lifetime ;)

See, this forum-thingie is really geared towards someone asking a specific question on a very narrow topic and getting a specific answer to that question, not so much for one-on-one mentoring.

Your situation requires a custom db application that has to integrate with pre-two existing data systems. From table design, to forms and queries to underpin everything, tracking two sets of inventory, to coding vba, to learning to email from access.

You've got a big learning curve ahead of you. It took me a year from asking my first questions here to get to the point where I felt I could answer more questions than I ask about access. I still use the search function all the time and ask questions if I am struggling with something. There's a LOT I still don't know. You have a 2 month timeframe to build an app like this. The good news is that this app will force you to learn a lot about access. The bad news is that this app will force you to learn a lot about access. Two months is doable for this app for someone who already knows what they're doing and fully understands your real world systems and constraints. It's going to be quite challenging for someone who's just beginning with access to do in that kind of timeframe.

So, it's just quicker for me to give you the db and you can dissect it to figure out how it works. Since you understand the real world model, seeing how I resolved the issues should be educational if you take the time to do it.

And of course, read tutorials and forums discussions to learn more about a particular topic. So, here's the app I'm fairly sure that you need. If you really want to learn, try creating a complete copy of the db from scratch. You'll soon learn there's a lot more going on than you thought. :)

However, you're not completely off the hook yet. You still have to populate the parts table, the modelnames table, the models table, and the modelparts table. I haven't created any mechanism for adding to these tables via forms. You will need to do that. You can look at how I enabled you to add engineers and departs for one method. Another method to lookup is using the notinlist event of a combo box and using vba to insert records into the relevant tables.

The modelparts table lists every part associated with a modelname and year. Information in this table limits the parts you see in the part number dropdown boxes on the forms to just those parts for a particular model that you've picked in an adjacent combo box (topic: cascading combo boxes). This saves searching through hundreds of irrelevant part numbers each time you start looking.

Now, if you really can't populate that ModelParts table up front, then you're going to have to re-do the sql of the part number combo boxes on the forms to just look up parts from the parts table and forgo having any filtering of the available part options. Up to you.

Anyways, Once you populate those tables, you should be able to start adding setrs. However, there's going to be a period where the line numbers used in your warantee admin's system are those that you manually selected where the db will assign those, potentially quite different, numbers without your intervention.

So, to transition to the new system, you need to start completely fresh with the Warantee admin's system completely blank of any line numbers at the same time you start using your db system to generate line numbers and input data from the web portal. Obviously, a great time to do this would be when you have no part recoveries on-going. Not sure whether you can swing that or not.

[edit]
Some other things to consider, since you want to do some of this yourself....

Currently, the 45 day lag period applies only to the expiration date of the SETR. If a line number is filled early, the line number still won't be 'available' again until 45 days after the overall SETR expiration date. You could do this by saving the date that each SETRModelsParts record is exported to the warantee guy (currently there's no field to do this in that table) and modifying the qryLineNumbers_CurrentExpirationDates so that it shows, in order of preference, LineNumberExportedDate, setrexpiration date, or if neither date exists then some arbitrary old date like #1/1/1900#.

You'll also need to modify the qryLineNumbers_MarkExported query to add the current date to the LineNumberExportedDate field when it marks off records as being exported.


Also, another problem to consider. Once you create a SETR and export it to the warantee guy, he will have a line number associated with acertain combination of part, model, year etc. He will use that combination and the priority of the line number to determine which dept codes to send the dealers who file a claim. Your db emulates that process in a vba function called AssignSETRModelsPartsID in a public module.

Now, if after you've exported the SETR to the warantee guy, you go back into that setr and change the model, year, or part number of a records that has already been assigned a line number, then the db will now have a different combination for that line number than the previously exported version that the warantee guy has. You need to decide what to do here. Options include:

1. Do not allow edits to previously exported combinations with line numbers

2. Allow edits but develop a tracking system that will let you inform the warantee guy of the change so he can change the data his system to reflect the new situation.

A third thing to consider, on the forms for adding a new engineer or department I included a delete button without thinking. If you delete and engineer, or a department who has been listed in a SETR, then everything to do with SETR's that reference the deleted engineer or department will be cascade deleted. This would be a BAD thing. So, get rid of the delete buttons on those forms. ;)
 

Attachments

Last edited:
Craig,

I took your advice about dissecting your table, and trying to recreate a new one from scratch... and I have a couple questions about the relationships/table setups.

I have posted my work in progress... It is a little different, as you will notice tblInvoices and tblVendors, which are separate from the rest, attached only to tblSETRs - this is my existing db, and how it would fit in to the new one.

The way I have setup tblLineNumbers: each Department Code will have at least one Line Number associated with it, and each Line Number has one SETR/Model/Part combination. I have a many-to-one relationship between tblLineNumber and tblDeptCode, but now I need a one-to-one relationship between tblSETRModelParts and tblLineNumber. What I was thinking was that maybe it would be a little more straight-forward to just use the SETRModelParts_ID as the line number? Setup the table so that it acts like a storage box for the specific combinations (basically starting with 999 records with blank fields)?
 

Attachments

There's an old scientific axiom: Seek simplicity but distrust it.

What I was thinking was that maybe it would be a little more straight-forward to just use the SETRModelParts_ID as the line number?

More straight forward in terms of table design, yes. But not in terms of actually using those tables. For starters, you will eventually exceed 999 entries in the SETRModelParts table. (According to your earlier posts, your warantee guy can only handle 999 unique numbers). When you reach the limit of 999, you then must start deleting data from old SETR's (which is going to be another whole nightmare to automate) or you won't be able to add new records. You're going to have to create code modules, new queries etc to track, assign, add and delete those id numbers each time you add records etc.

That's bad enough in it's own right: but the question is why would you want to do all this when you already have a functioning system that meets pretty much all your needs (some minor tweaking notwithstanding). And the system I proposed means you also get to keep all your historical information wheras your prposal doesn't.

Also, just a quick glance at your new structure has raised some alarms for me.

For example, you've switched dept code to be in the line numbers table instead of an attribute about an engineer. This is wrong. A line number could be assigned to a setr from one depart, then later to a setr from another department. Your model does not allow for this. A dept code is not information about a line number therefore it does not belong it that table.
Likewise, normalization dictates that you not place it in the SETR table since it will be a repeating group in that table.
Department code is information about an engineer's location. So, since you've added a locations table and assigned an engineer a location ID, you could put dept code in that locations table instead. Not really sure what you're gaining by it, over the original, but it's definitely not wrong to do it that way. Just means you now need to allow for that difference in the queries etc that I built.

Now for your invoices and vendors table: they are entirely irrelevant to the stated purpose of the database. At no point do you need to enter invoice/vendor information to create new SETR's, assign line numbers to part/model/setr combiunations, or track whether requested quantities in SETR's have been filled etc. So why include this information and all the attendant data entry that it will require?

If you really do insist on including that information then I assume that you receive this information from the web portal at the same time you get the part/model/year information? If so, then strictly speaking, you'd store the InvoiceID as a foreign key in the WebPortalItems table NOT in the SETR table. You'll also need to create new forms and modify the data entry form for webportal items to allow you to input that information.

I notice also that you've stripped out the WebPortalItems table from my design which is absolutely critical to determining whether quantities of parts in setrs have been received by your third party shipper.

In short: you've added unnecessary tables (unless you have additional roles for this db that you have not previously stated), moved fields to the wrong table and duplicated the same information in another wrong table, and deleted an absolutely critical table for achieving one of the two main goals of the db. Plus proposed a much harder to implement solution to a problem I already gave you an answer for! :D

About the only change that seems ok to me was the addition of the location table (even though it just provides the same information as the original dept_code field).
 

Users who are viewing this thread

Back
Top Bottom