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.

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.

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.


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 :)

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.

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. ;)

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)?

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).

Where to start... it was an incomplete db. I was starting to work a copy of yours, and some questions started popping into my head. So my brain sort of stopped working on the db, and started wondering about db design. Then I got frusterated, and posted the question that made me stop in the first place.

I guess my problem was that I could quite see how your design would allow for one department code to be applied to many line numbers. This raised an alarm for me, as this is often the case. Also, I wanted to populate some of the tables with some real data so you could see what I am working with. Unfortunately, I got sidetracked and posted it before I had added to anything except the department code table. If you look there you will notice that there are 167 possible department codes... I may not have explained how they work too well. They also act like open slots for part recovery information, similar to the line codes. Different combinations of final destination, recovery type, priority, and time frame dictate different department codes. So to sum it up, each single SETR will have one department code based on the aforementioned criteria, multiple models-parts combinations, and each model-part combo gets its own line number.

Another problem I came across was here with the department codes. All of them are unique, except DG, which we use for parts relating to the fuel system, or any other dangerous good that must me handled, packaged and shipped specially. I dont know of anyway to allow duplicates of DG, unless I change my table setup so that the actual department code is not the pk of tblDeptCode, and use an autonumber instead?

Ugh...
Tell you what... why don't I regroup, and finish what I was going to do to the db, properly formulate my questions, THEN post.

:o
Thanks

Let's break this down with the new information.

So, basically dept code is information that Warantee guy sends to the dealer->3rd party receiver that tells them not only where to ship something, but how urgently, and with special precautions for some of the parts that come from fuel systems?

So, my next question for you is best asked via an example.

Say dealer Bob gets a 2003 Corolla and removes the left,front shock absorber, the right, rear shock absorber, and the fuel pump. He files a claim with warantee guy. Warantee guy looks them up and finds that three line numbers match those combinations, all from the same setr.

Warantee guy sends him ONE dept code, or THREE dept codes? (two of which might be the same code)

If only one code is sent then the Dept_Code belongs in the SETR table (if it's stored at all since it is just a combination of three things that you are already storing {location, priority, and whether the parts include fuel-system parts} so it could be logically determined by the db from those three things).

If three codes are sent ('DG' for the fuel pump and another code for each of the shock absorbers) then the Dept_Code belongs in the SETRModelsParts table if you actually store it, and has to be defined for each combination of model/year/part within a setr individually {again, this could be logically deduced by the db based on the other information that has already been entered}.

If three codes are sent ('DG' for the fuel pump and another code for each of the shock absorbers) then the Dept_Code belongs in the SETRModelsParts table if you actually store it, and has to be defined for each combination of model/year/part within a setr individually {again, this could be logically deduced by the db based on the other information that has already been entered}.

Each warranty claim on a failed part is responded to with a department code that corresponds with an applicable SETR.

Something I just thought of... There will be cases when an SETR may have two dept codes (one being DG). The only time this will happen is when one shipping code is DG. Some blanket parts recoveries include fuel system parts, and while the majority of the parts might be under the dept code A5, and part number that starts with 7, (i.e. part # 7****-*****) is a fuel system related part, and must be assigned the DG dept code. This notifies the dealer and third party shipper to ensure it is packaged properly. However, the dealer is also instructed to write the SETR number on the box, so that the third party shipper can group it with the rest of the recovery, ensuring it ends up at the proper destination.

So does he file a separate claim for each part?

So does he file a separate claim for each part?

It depends... There is an OFP (original failed part-number) listed, as well as any associated parts. In the case of the shock absorbers, maybe only the left one failed, but if the recovery states we need both for comparison, they would both be filed under one claim.

NOTE: I just edited my previous post.

Ok...now we're getting to the meat of this.
_____________________________________
All SETR's, in reality, have only ONE dept_code.

DG is NOT a dept_code at all: in reality it is actually a dangerous goods warning.

Claims may have one or many parts.

One dept_Code is sent to the dealer/shipper by the warantee guy for EACH item in a claim that has a line number related to that SETR. (If all parts in a claim share the same SETR, then the warantee guy might only send the code once).

If some parts are required by one SETR, and other parts by a different SETR, Warantee guy will send an itemized list of parts vs dept_code.

Some parts included in a claim might be fuel system parts: if so, the code 'DG' is sent for those parts only instead of the true dept_code. (to inform the dealer and receiver to package the part carefully. Currently, the dealer has to write the SETR ID on the DG packages so that the receiver can lookm up where to ship the item when they receive it).

______________________________________

First up: this is a bad system. What the warantee guy ought to be doing is sending the correct dept_code for all parts AND an additional warning code to the dealer for those parts that are in the fuel system. This would avoid the dealer having to write the SETR number on the package and save the receiver from having to look up where to ship the item.

So, you should:
1) investigate whether warantee guy is able/willing to do this if you provide both pieces of information in 2 separate fields along with the line numbers that you send him etc.

2) If warantee guy can't, or won't, deal with two separate fields, can you export the dept_code to him as a 5 character string? Thus, you could supply the dept code for each line number as a concatenation of the proper dept_code and the conditional 'DG' code. For example, instead of sending only 'DG' for a fuel pump requested in a setr with a dept_code of A7, can you use "A7,DG" for the fuel pump's line number, and "A7" for the shock absorber's line numbers?

3) if you can't manage that for some reason, then I guess you're stuck with your status quo.

Regardless, YOUR db's data structure should reflect reality. Dept_Code and Dangerous Goods warnings are two separate things that should NOT be conflated in one field. You can choose to use one, or the other, or both, in your export field but they should be kept as separate pieces of information.

If you choose to store the dept_code then it belongs in the table SETR.
If you want to classify some parts as 'dangerous goods' then this information belongs in the parts table. A simple yes/no field would suffice to classify a part as a dangerous good or not.

Then, instead of simply exporting the dept_code from the setr table you might use an expression like ...

MyConcatenatedDept_Code: IIf([Parts]![IsDangerous]=-1,Nz([SETR]![Dept_Code],"CODE NOT ENTERED") & ",DG",Nz([SETR]![Dept_Code],"CODE NOT ENTERED") )
...to concatenate the two pices of information

Or

MyDept_Code_OR_DGWarning: IIf([Parts]![IsDangerous]=-1,"DG",Nz([SETR]![Dept_Code],"CODE NOT ENTERED") )

....if you must send one, or the other, but not both

OR

if the warantee guy can use two separate fields, then you just add the IsDangerous field to the export results along with the existing Dept_Code field.

Hey Craig,

It's been a while since I have had a chance to work on this, as there has been an awful lot going on. I have made some changes, but seem to be stuck here.

In subformSETRModels, embedded in fmSETR, we have decided we want to filter by the VDS (a five-digit code that represents a model's combination of trim level, and powertrain) instead of just year and model name. If you take a look at tblModels, you will see a column labeled VDS which contains each five digit code for every combination of model name and year. For example, a 2008 Tundra has 15 different possible VDSs, but there are 25 in the table because I needed some blanket type VDSs (the ones with asterisks) that would ignore variations in trim, bodystyle or powertrain.

Aaaanyways, what I am trying to do is to filter the VDS by model name, then year, instead of giving the user a list of over 700 possible VDSs. I know this is a fairly simple task, yet it eludes me. Maybe I am just too tired today...

Also, the next subform, subformSETRModelsParts, I would like to be able to enter part numbers in here directly, as opposed to putting them in through another form. Is this as simple as creating a text box, and setting its control source to the corresponding field in the Parts table? Or will I need a query to ensure the table also gets the ModelID (year and name) to record it?

When the Part # is recorded, it takes the ModelID as well, but there are cases where one part number might apply to several models. I can't estimate how often a part number might be duplicated within an SETR, but but do you think it would make more sense to allow duplicates in the part-number field? or create a junction table between the model and parts tables?

I was starting to create a new form for creating SETRs, its called frmSETR, if you want to take a look at the direction I was going. I realize there are some things that won't work out, like using list boxes for multiple selection (each line can only have one model, and one model year), etc.

I'm sure I had more questions, but I can't think of them just now. I appreciate any direction or suggestions you have. Thanks again!

Hi again Colin,

In subformSETRModels, embedded in fmSETR, we have decided we want to filter by the VDS (a five-digit code that represents a model's combination of trim level, and powertrain) instead of just year and model name. If you take a look at tblModels, you will see a column labeled VDS which contains each five digit code for every combination of model name and year. For example, a 2008 Tundra has 15 different possible VDSs, but there are 25 in the table because I needed some blanket type VDSs (the ones with asterisks) that would ignore variations in trim, bodystyle or powertrain

At a quick glance, it seems to me that you need to normalize this new information. Each VDS code may be linked to one, or many modelname -year combinations. Each model name-year combination may be linked to one or many VDS codes. To me that spells two more tables: a VDS code table and a junction table to list model name-year combinations vs VDS code id's. Once you have that, you simply set the row source for the VDS combo box to query the junction table using the model name-year combination (ModelID) that you have selected in the sbfmSETRModels form as a criteria.

Now, if more than one vds code needs to be assigned to a model name-year combination in the SETR, then you need to create a third table to handle the one to many nature of that relationship. This would further necessitate that you need to use a nested subform on sbfmSETRModels to contain the VDS codes for that model name-year combination in that SETR.

Also, the next subform, subformSETRModelsParts, I would like to be able to enter part numbers in here directly, as opposed to putting them in through another form. Is this as simple as creating a text box, and setting its control source to the corresponding field in the Parts table? Or will I need a query to ensure the table also gets the ModelID (year and name) to record it?

A form can only be bound one recordset (table or query) at a time so you cannot enter directly into the parts table (tblParts) from a form that is already bound to a different table (tblSETRModelParts).

However, you can use a variety of ways to add items to the Parts table from your subformSETRModelsParts form. One way would be to use the Not In List event of the combo box which is bound to your PartID field. Or, add a command button to open a popup form in data entry mode, which is bound to your tblParts, then requery the combo after the popup closes.

You will likely need two new forms to handle this task. One to add existing parts to tblModelParts for various model name-year combinations, another to add a totoally new part number to the db (tblParts).

In your case, once a user had a added a brand new part to the db, you will also want to consider prompting the user to also list all the model-year combinations that the new part is used in (to go in tblModelParts) so that the combo box knows to include this part number in the list (since it uses the information in the tblModelParts to filter out any parts that are not associated with the selected model name-year). This is one of those areas I told you about that still needed attending to when I gave you the example db (you did say you wanted to do some of this after all). I have already showed you how to open a popup form for adding new engineers and locations etc.

When the Part # is recorded, it takes the ModelID as well, but there are cases where one part number might apply to several models. I can't estimate how often a part number might be duplicated within an SETR, but but do you think it would make more sense to allow duplicates in the part-number field? or create a junction table between the model and parts tables?

There is already a junction table between the model and parts tables. It's called tblModelParts. ;)

You have to remember that there are three tables here. tblParts which lists individual components of vehicles. tblModelParts which stores information about which parts (PartID) belong with which model name-year combination (ModelID). And the third is tblSETRModelParts which your subform is bound to. This stores information about which parts (PartID) from which model name-year combination (ModelID) are requested by this SETR (SETRID).

No matter how you achieve the data entry, you must store the individual PartID for each ModelID in the SETR as a line in that table. You can add each one manually, or you can create a button to 'Add all Parts' for that ModelID
to run some SQL to append the data en-mass (using the information in tblModelParts to provide the appropriate list). Up to you. Regardless, the data structure should never be de-normalized like you're contemplating.

I was starting to create a new form for creating SETRs, its called frmSETR, if you want to take a look at the direction I was going. I realize there are some things that won't work out, like using list boxes for multiple selection (each line can only have one model, and one model year), etc.

List boxes are not my friend. Have never used one except on the menu of your db (for self education purposes). You can bind a multi-select listbox to a field because you can only store one piece of information in a table. You can, however, use an unbound mutliselect listbox to append rows to a table using vba to create the right SQL. However, I'm not going to be much use to you there since I'm not real familiar with them as I find subforms and command buttons more appropriate for that in most instances. In your case, each modelname-year combination has, potentially, a separate list of parts that might be requested within a SETR. That means the multi-select listbox approach is going to be highly problematic. Not saying someone couldn't make it work...but it's more work than I'd undertake.

As for the form, well, you have three 'boxes' for part numbers. What if there's 10 parts requested for your 2008 Tundra and 7 different parts requested for the 2005 Camry? Three boxes won't work. And three boxes on a form indicates three fields in the underlaying recordsource. You're forgetting the cardinal rule of one-to-many relationships and forms: you need a subform bound to a related table to handle that task.

I keep getting the impression you're looking for shortcuts for data entry. Something like wanting to select several models at once, and several parts at once. But the reality I got from your earlier posts is that, because the parts requested might differ for model name-year combinations within setr, you can't adopt such a simplistic data entry convention because the data model is far more complex. Now, you can automate some of that data entry as I suggested above, but the underlaying data model requires much more of you than your form design would allow. But instead of trying to force several items into one box, you should think about using command buttons to run SQL to append records using information in tblModelParts as a guide. Then allow the user to delete any appened records that they don't want.

Further, I'm not sure what the purpose of the line number command button is. Line numbers are assigned automatically during the export process from the main form. What are you trying to do with this button here?

Hey Craig,

I just read through your last post...

Would you like to get in touch with my publisher about it? :D

LOL

Thanks Adam. Needed a good laugh.

See? I CAN TOO post a short post. :)

And now to make a liar of myself from the last post I had an additional thought for Colin. :rolleyes:

If your SETR's are going to want to obtain parts based on the trim level (vds) of a vehicle, as well as on model name and year, then you will have a much larger problem as that will necessitate a whole new level of data grouping between tblSETR and tblParts.

If this is the case, then it is probably going to involve rebuilding much of the db to deal with this extra level of information (although the general principles will remain the same). If SETR's do not request parts using trim as a factor (like they do with model name and year) then you'll be ok using the information in the prior novel post.

I sure hope it's the latter ;)

If your SETR's are going to want to obtain parts based on the trim level (vds) of a vehicle, as well as on model name and year, then you will have a much larger problem as that will necessitate a whole new level of data grouping between tblSETR and tblParts.

The parts are not obtained based on trim level. The only reason I incuded VDS is because the warranty system uses it in the recovery of parts. In the text string that is the requirements for a part recovery, there is only a section for VDS, not model name. The warranty system also uses *'s as wild cards, which is why I have included some partially filled VDSs that will allow the warranty system to grab all parts from that model. There are also VDSs there to allow the collection of parts only from say an AWD model, as opposed to all models, and vice versa.

I am working on fixing a bunch of things, and will post a more relevent bd hopefully by the end of the week.

Craig, thanks for your help.
Adam, thanks for being a smart ass - and if there is any communication with your publisher, I want in. I helped write this novel too! :D

Cheers

Colin

The parts are not obtained based on trim level.

Well, thank goodness for small mercies :)

In the text string that is the requirements for a part recovery, there is only a section for VDS, not model name.

I'm not sure what text string you're referring to here.

But are you saying that your exports to the warantee guy needs to list parts by unique combinations of VDS and year, NOT combos of Model Name and year as you originally stated?

If so, unless you can guarantee no more than one VDS code per model name-year combination will EVER be assigned, then you will need to redo tables, queries, and some code too.

Instead of tblModelParts you need tblVDSParts to link specific parts to specific VDS codes. You'll have one line number for each unique combination of VDS and year instead of model name and year. That means things like tblSETRModelParts should really be SETR_VDS_Parts and in tblWebPrtalItems you ought to be storing the VDS codeID instead of the ModelID. You'll need to update all the queries etc to reflect these changes and, where necessary, rewrite the code to work with VDS instead of ModelID.

This would be almost as bad as the other scenario.

This VDS thing really should have been mentioned much earlier in the discussion.

I'm not sure what text string you're referring to here.

Sorry... I meant the info from the web portal.


But are you saying that your exports to the warantee guy needs to list parts by unique combinations of VDS and year, NOT combos of Model Name and year as you originally stated?

If so, unless you can guarantee no more than one VDS code per model name-year combination will EVER be assigned, then you will need to redo tables, queries, and some code too.

I hereby make the guarantee that no more than one VDS code from a uniqe model name/model year combo will ever be assigned to one SETR. For the most part, the VDSs being used will be the ones with *'s in them. It is a very rare scenario that might ask for a certain VDS, besides to differentiate from FWD and AWD models.

You'll have one line number for each unique combination of VDS and year instead of model name and year.

There's no need. Once these parts recoveries are setup, there will not be any overlap in requested parts. The SETRModelsPartsID is basically a placeholder for the duration of the recovery. This means it doesn't matter if say an AWD VDS is selected, and the part number is for an AWD component, but the partID is connected to all trims of that model (including non-AWD ones).

I hereby make the guarantee that no more than one VDS code from a uniqe model name/model year combo will ever be assigned to one SETR

Sworn and witnessed! :D

Just remember that the db doesn't know anything about VDS codes when counting up items received versus quantities requested for part numbers for model/year combinations. So FWD and AWD won't factor into those counts at all. If that's cool with you, then great.

(Now Adam has me counting words!)

(Now Adam has me counting words!)Why!? Have you ever read one of my responses? They are all freakin' novels in their own right! :)

That's probably why noone here wants to talk to me anymore! :( :D

Question:

If a form/subform can only be bound to a single recordset, what is my best option for using cascading combo boxes in subformSETRModels? I want to be able to select model in one, model year in the next, and VDS last. If (when) I normalize my VDS data, do I just include a field in tblSETRModels that will reference the VDS_ID? Or is there a way to do this by maybe changing the recordset of the subform to a query?

I'm just looking for advice on what the best way to handle this is.

Thanks!

Question:

If a form/subform can only be bound to a single recordset, what is my best option for using cascading combo boxes in subformSETRModels? I want to be able to select model in one, model year in the next, and VDS last.Colin, if you're looking for some general advice, here are some facts on the issue that may come in handy...



**You can create cascading combos on any form, be it a subform or not, as long as you write the correct (reference) syntax.

**They (cascades) don't have to pull from a single table. They can pull from as many tables as you want, as long as those tables are joined properly, so the records from each can be related (compared) when Access reads the SQL.

**Creating a query to bring all the fields you need (for the combos) together is not necessary, but it may provide some clarity for you. Remember that queried data is still just the source data, linked to its source object, and thus can still be manipulated.

Just to be clear:

A form is bound to one recordset (table or sql)
A subform is bound to a different recordset (table or sql) that is usually related to the recordset of the main form by a key field.
A combobox stores one piece of information into a field in the recordset of the form. However, the items in the dropdown list of the combo can be generated using completely different sql, and different source table(s), than the form uses as long as the value field that you want stored is in the mix.

So, your combo for model name will use sql that queries/shows all the different model names from the tblModelNames table and stores the ModelNameID.

Your combo for model year will look up 'years' from your tblModels table where ModelNameID = cmboModelNameID as a criteria, but stores the ModelID.

Your final combo sql will depend on how you ended up storing the VDS information. Essentially, I remember suggesting that there should be a table for VDS codes (VDS_ID = pk) and another junction table to match ModelID to VDS_ID (ModelVDS_ID = pk)

Your sql for the vds combo will be based primarily on the junction table, but also show the VDSCodeName field from the tblVDS table. You will lookup tblModelVDS!VDS_ID values from the junction table where tblModelVDS!ModelID = cmboModelID, and store the tblModelVDS!VDS_ID field while showing the related tblVDS!VDSCodeName.

So, your combo for model name will use sql that queries/shows all the different model names from the tblModelNames table and stores the ModelNameID.

Your combo for model year will look up 'years' from your tblModels table where ModelNameID = cmboModelNameID as a criteria, but stores the ModelID.

Your final combo sql will depend on how you ended up storing the VDS information. Essentially, I remember suggesting that there should be a table for VDS codes (VDS_ID = pk) and another junction table to match ModelID to VDS_ID (ModelVDS_ID = pk)

Your sql for the vds combo will be based primarily on the junction table, but also show the VDSCodeName field from the tblVDS table. You will lookup tblModelVDS!VDS_ID values from the junction table where tblModelVDS!ModelID = cmboModelID, and store the tblModelVDS!VDS_ID field while showing the related tblVDS!VDSCodeName.

I understand this in theory, I just cannot seem to make the SQL work. I know that in the first subform I need to include the SETRID to link to the parent form, as well as a link to the VDS junction table.

I also understand how to make the cascading combo boxes work. To help improve my understanding of it I created a temp db to try it out. I can get it to work on this one, but when I apply it to my SETR db it won't. Ideally, I would like to first select the ModelName, then the ModelYear (Since some vehicles like the Yaris have only been around for a couple years where other models go back to 2000), then finally have a filterer down selection of the VDSs that apply to that ModelYear of that ModelName.

Then, in the second subform I don't really need to change anything... I don't think... I still like the way a part number is associated with the ModelID (which is the combination of ModelName and ModelYear), since we are not tracking parts based on VDS (VDS is there simply as a reference for the warranty people).

Here is the most recent version... Once I get this part figured out, I just need to populate the db with the most current info about ongoing SETRs. (and a couple other little things... parts form, new VDS entry form, new engineer form, etc.)

Thank you so much Craig and Adam for all the help.

P.S.
Craig, I am working on the Prius for ya :rolleyes::p

P.S.
Craig, I am working on the Prius for ya :rolleyes::pWell that's wonderful, but what about mine!? :( :( :(

Well that's wonderful, but what about mine!? :( :( :(

You never asked!

Consider the previous post the request smarta**. :)

I think the part that is screwing me up is that the values I need to appear in the combo boxes aren't all from one table. I can't seem to logically work out how the SQL should be that must be included in the code for the AfterUpdate event.

I think the part that is screwing me up is that the values I need to appear in the combo boxes aren't all from one table. I can't seem to logically work out how the SQL should be that must be included in the code for the AfterUpdate event.Are you talking to me Colin? I haven't even looked at your database! If you're referring to my earlier points about SQL, those are just rules that can be used by anyone, in any situation (unless of course you've seriously screwed up your database structure!!).

Are you talking to me Colin? I haven't even looked at your database!

I'm talking to anyone who will listen!

Do you feel like taking a look at it? :D

Do you feel like taking a look at it? :DWhat do I get in return? :cool: :cool:

What do I get in return? :cool: :cool:

Mostly gratitude, with some thanks, and the knowledge that you are helping someone learn about a subject in which you are more proficient.

Colin,

here's your db with the form functional.

There were several problems to deal with but the root cause of your difficulties is that you were trying to use two controls to each fill in partial information needed to determine which modelID to store in the table without actually creating a control to hold the ModelID (as opposed to just the modelname or just the model year). So, if you look at the form in design view you'll not an additional text box control (txtModelID) that holds the actual modelID.

You also lacked a VDSID field in the tblSETRModels to store the vds code you wanted to associate with this setr/modelname/modelyear combination.

The two combos you were using before are now unbound. That means that you need to fill them each time you move to a new record (look in the on_current event, or whenever you change one of the values in the unbound controls). The value in txtModelId is determined in the after_update event of both of your unbound combos (but only when both have been populated).

If you try to enter the year when the model name is not entered, or if you try to enter the vds when either model name or year has not been entered, the form sets the focus back to the model name.

I also fixed the underlying problem in the setrmodelparts subform that caused the Error message to appear. This was due to a query field not being 'fixed' when you played about with the WebportalItems table.

Craig, I am working on the Prius for ya

Excellent :D I'll expect it about the time Adam receives my cheque (check for those who prefer not to spell things correctly) ;)

Oh...I forgot to link the VDSID field in tblSETRModels to the VDS_ID field in tblVDSs in the relationship view and enforce referential integrity etc. You should probably do that too.

Excellent :D I'll expect it about the time Adam recieves my cheque (check for those who prefer not to spell things correctly) ;)You've done more work than I have here Craig. For that, I "pre-deposit" my cheque in your account. :rolleyes: :rolleyes: :rolleyes:

Oh...I forgot to link the VDSID field in tblSETRModels to the VDS_ID field in tblVDSs in the relationship view and enforce referential integrity etc. You should probably do that too.

I just noticed haha

It didn't like it when I tried to change the model :cool:

I think the part that is screwing me up is that the values I need to appear in the combo boxes aren't all from one table.

Remember combo boxes have two functions.

One is that they store one piece of information in a field in the form's record source (ie the underyling table)

Two is that they display a list of possible values to be stored, and a bunch of associated information about those values. This associated information makes the data readable by humans. The list of values and information can be drawn from many related tables, but only one field from that list can ever be stored.

So, only one table (or updateable query) is bound to the form (this is where the value selected is stored) but several tables/queries can be referenced when helping the user decide which of the possible values to choose from.

It didn't like it when I tried to change the model

What do you mean? It should clear the slate for the other options because, by changing the model, the previously selected items might no longer be valid choices.

For that, I "pre-deposit" my cheque in your account.

Great. That, and $4.50 will buy me a triple-shot 20 oz Mocha :)

$4.50 will buy me a triple-shot 20 oz Mocha :)Well, it sure won't buy you a Prius, but coincidentally, it will buy you a tank of gas for it! :cool: :cool: :cool:The list of values and information for combo boxes can be drawn from many related tables, but only one field from that list can ever be stored.Very few people understand this phenomena, and I'm actually surprised you said it!

Two is that they display a list of possible values to be stored, and a bunch of associated information about those values. This associated information makes the data readable by humans. The list of values and information can be drawn from many related tables, but only one field from that list can ever be stored.
Not really 100% true. There is only one bound field for a combo, but it's quite possible to use, say, the AfterUpdate event to populate other fields from the combo.

Not really 100% true. There is only one bound field for a combo, but it's quite possible to use, say, the AfterUpdate event to populate other fields from the combo.
Of course I was meaning one field can be stored (bound) by that combo control. You are, of course, correct about being able to populate other controls/fields with information from the remainder of the row source sql fields. Thanks for catching that Neil. :)

Very few people understand this phenomena, and I'm actually surprised you said it!
Not quite sure why you're surprised. Surprised I understand it? Surprised I screwed it up? Surprised I can articulate a concept in less than three chapters of a novel? ;)

Craig,

There seems to be some sort of problem still with those cascading combos. When I select a model, only one year shows up in cboModelYear, then VDS shows no options.

I was going to try using some sample code for cascading controls that Adam posted a while ago, but I'm not sure about some of the SQL. It's something along the lines of:


Private Sub cboModelName_AfterUpdate()
Me.cboModelYear.RowSource = "SELECT tblSETRModels.ModelYearID FROM tblSETRModels WHERE " & _
"tblSETRModels.ModelNameID = [cboModelName]"
End Sub

Private Sub cboModelYear_AfterUpdate()
Me.cboVDS.RowSource = "SELECT tblSETRModels.VDS_ID FROM tblSETRModels WHERE " & _
"tblSETRModels.ModelYearID = [cboModelYear]"
End Sub

Now for a couple questions...

In the SQL there, do I want to be calling the ID fields, the text fields, or both? I noticed you have set the RowSource for each combo box to both related fields (for ModelName = ModelName, and ModelNameID; ModelYear = ModelYear, and ModelYearID, etc.) As I type this, it seems to make sense that both fields need to be referenced in the SQL... is this done with comma's?

Also, I was thinking about potential cases where it might not be necessary to select a single model... would it be best to simply add to tblModelNames a record that is called "Various" or "All Model" that is then linked to every year, and where the VDS = *****? Or would there be some way to code something like:

IF cboModelName IsNull, SELECT DISTINCT tblSETRModels.ModelYearID FROM tblSETRModels?

to accomplish the same thing?

Let me know if it seems like I am trying to make this more difficult than it needs to be.

Colin,

I'm not going to try dissecting Adam's solution. He'll have to earn his prius himself ;)

You're right though...there was an error in the sql for cboModelYear (was using the criteria for modelnameid in the modelid field accidentally.

Change it to the following:

SELECT tblModelYear.ModelYearID, tblModelYear.ModelYear
FROM tblModelYear INNER JOIN tblModels ON tblModelYear.ModelYearID = tblModels.ModelYearID
WHERE (((tblModels.ModelNameID)=[Forms]![fmSETR]![subformSETRModels].[Form]![cboModelID]))
GROUP BY tblModelYear.ModelYearID, tblModelYear.ModelYear
ORDER BY tblModelYear.ModelYear;


Now, you've only got VDS codes listed for 18 models of vehicle in the VDSModels table. Remember, a model is a unique combination of model name and model year (ie 2003 Corrolla). If you really intend for VDS codes to apply to model name (ie, corolla) then we need to do a little restructuring.

If you really intend for VDS codes to apply to model name (ie, corolla) then we need to do a little restructuring.

Sorry, apparently I was typing faster than I could read. Having a series of VDSs related to each combination of model name and year is fine.

Now, the year problem is fixed, but the VDS is still coming up blank...

[edit]

I suppose this is because I made the mistake of using the ModelNameID in tblVDSModels, when I should have been referencing ModelID. I will fix this now... Might take a little while

[edit edit]

OK, so it would seem that I truly have my off days. I just remembered looking at the tables, that I hadn't finished entering all the data in tblVDSModels. I have another 600 or so records to add.

Glad you have it sorted now. I had wondered if that was what had happened in your vdsmodels table.

Not quite sure why you're surprised. Surprised I understand it? Surprised I screwed it up? Surprised I can articulate a concept in less than three chapters of a novel? ;)Craig, I am SURPRISED because I have seen very few explanations that actually EXPLAIN something!!! Kudos to you for offering what many people don't have the guts to do (they certainly have the time, they're just lazy bums)!

I dunno Adam. I think many regulars here have patiently explained most things incredibly well in many of their posts. Some that leap to mind as exceptional examples of this over time include The_Doc_Man, Pat_Hartman, Rural_Guy, OldSoftBoss, BobLarson (not an exhaustive list). I owe much of my understanding to their patient explanations to various posters.

I think part of the reason many questions get the shortest possible response instead of the full explanation stems from the repetitive nature of many of the questions posted. After you've answered the same question dozens of times, and have taken the time to explain it, it gets frustrating to repeat yourself ad nauseum. The information has already been posted....new users have only to search and they can learn what they need to know relatively easily. It's difficult to stay motivated to write it all down again (it takes a lot of time and careful choice of words to get it right and also be understandable to someone lacking the relevant jargon).

I try to be patient and explain things, or at least give the questioner some key words or phrases to search for, because I remember how clueless I was originally about which words to search for. But the corollory to that is that I can't spread myself around as much as I could if I didn't take the time to explain (I'm also not a fast typist). I also do it, where possible, to try to reduce the load on some of the true forum gurus who have the ability to answer much more difficult questions when they arise if they're not burned out from answering the basics over and over.

I am constantly amazed and awed at the post counts of some of the most helpful folks on this forum. That they still take any time to respond to some of the questions they do is a testament to their good will and endurance.

Well, thank you, from those outstanding people to you (I am saying this on their behalf).

But, "professionalism" ignores "repetitiveness annoyance" and cuts to the chase most of the time. Good people take the sh** and don't complain about it, but rather, get used to it, and it some cases, make a good time out of it! I guess I should feel fortunate that I am still young enough not to get as annoyed as some people that have been in this racket for a million years?

But then again, I'm not that young...soooooo....how do you explain what I do around here??

Answer that genius boy! :D :D :D

But, "professionalism" ignores "repetitiveness annoyance"

Professionals gets paid to do that. People contributing to this forum are unpaid volunteers in this setting.

how do you explain what I do around here??

I wouldn't presume to try ;)

I wouldn't presume to try ;)Well Craig, I'll take that as a compliment. Thanks buddy! :)

Craig,

I am hoping to be able to access past SETR information in the future. This is including department code, line numbers, quantities, etc. What would you suggest as the best way for going about this?

In the previous system, the SETR table had a yes/no field for whether the SETR was active or not. I took a look in the global modules that you wrote, and I see where line numbers are assigned, but I couldn't find anywhere in the code where they become available again (after quantity or date conditions are met).

[Edit] I took out this part as I have decided to keep the invoicing db seperate.

Thanks for your help!

Colin,

Line numbers are not assigned until you 'export' the SETR to the Warantee folks. Once assigned, line numbers of SETRs are stored in tblSETRModelParts. They are not deleted when a SETR expires (but they do become 'available' again after the lag period). So, if you ever want to see line numbers of expired SETR's then just look them up from the table. QED.

Why, exactly, you'd want to do this escapes me since line numbers are reusable and have no meaning once the SETR has expired. But as long as I get that prius, what do I care? ;)


for assigning vendor invoices

I never designed this db with vendor invoices in mind. This is something you've added on so it's hard for me to comment on. Also, I note there's a lot of new queries in the db that do not line up with the table names in this db and reference some fields that do not exist in this db (eg, qryActive, qryExpenses etc). Why are you adding all this stuff when none of it is necessary to achieve the goals you stated the db was to achieve? In any case, seems to me like you've imported queries from your old db into this one. You need to go through each of these and fix them so that they reference what they're intended to. Otherwise delete them as they only make it harder to figure out what is going on.

I also note that some of the queries I originally built now have missing references because you seem to have changed some table names etc. This will foul things up as those queries were all used at various points along the way. For example, the qryLineNumbers_AvailableNumbersNow has such an error. These must be fixed for the code to work.

To get at the question of 'active' setrs you first need to define what you mean by 'active'.
Active could mean all SETRs that have not reached their expiration date.
Active could mean all SETRs that have not reached their expiration date AND have been provided to the warantee folks.
Active could mean all SETRs that have not reached their expiration date AND have been provided to the warantee folks AND have not had all their line numbers filled yet.

To get at the expiration date, make a query of tblSETR that shows SETRs where the expiration date field is greater that the current date.
To get at the question as to whether it has been given to the warantee folks, add another where clause to the query so that only SETRs with -1 in the tblSETR!SETRExported field are shown.

To get at the Line number issue make a query of tblSETRModelParts and limit the results to records where LineNumberExported = 0. Join this to your query that finds unexpired, exported SETRs etc such that only SETRs common to both are shown. That would be the list of active SETRs that would meet the third option.

Also, Colin, I note that you've placed the dangerous goods yes/no field in tblSETRModelParts instead of tblParts as I recommended. Just to be clear, this decision denormalizes your database and requires you to assign a dangerous goods value during data entry each time you add a part to a setr rather than just once when you add a new part to the db. Seems like a lot of make-work to me.

Before you do anything more please go through and fix/delete the queries that you have added, or fix the ones you have broken, or all the code that was written for you will not function.

And remember, the more you tinker with this db the further it moves away from something I understand and can provide advice on. If you insist of changing names of tables then you must make damned sure that any objects that reference those tables are updated to reflect those changes. That includes CODE and QUERIES. I do not feel inclined to debugging or fixing such problems as they are self-inflicted at this point.

Craig,

I almost have your book finished. I just need a couple of more posts from you, and that will do it, OK??? :D :D :D :D :D

Somehow I don't think it's gonna be a best seller ;)

Why, exactly, you'd want to do this escapes me since line numbers are reusable and have no meaning once the SETR has expired.

I was asked to make sure they would be there, just in case warranty misses one of the lines when they are shutting off a completed recovery. (We noticed this last week, as we were still receiving parts we didn't want.) I think it is more a problem with our current system (the Excel file) because we couldnt go back to check which line number it was attached to.

I never designed this db with vendor invoices in mind.

Originally I didn't either... Then because our invoicing db is associated to the SETRs in this db, I thought it would be easy to combine the two. Turns out this isn't going to work anyways, as this one we (you) built is only applicable to warranty part recoveries, where as the invoicing one is compatible for other SETRs (random recoveries, go 'n' see activities, etc.)

Why are you adding all this stuff when none of it is necessary to achieve the goals you stated the db was to achieve?

I have deleted them all. The db is back to the way it was when you originally put it together.

I also note that some of the queries I originally built now have missing references because you seem to have changed some table names etc. This will foul things up as those queries were all used at various points along the way. For example, the qryLineNumbers_AvailableNumbersNow has such an error. These must be fixed for the code to work.

I have just run all the queries, and don't seem to be getting any error messages... For example in qryLineNumbers_AvailableNumbersNow, I ran it, and it showed all line numbers from 1-999, since the db is empty right now. I don't remember changing all the table name references in the queries, but it has been a busy week...

I do not feel inclined to debugging or fixing such problems as they are self-inflicted at this point.

I would never ask you to!

Hmm. Perhaps I typed in the wrong query name then. Do me a favor and check qryLineNumbers_CurrentExpirationDates and qryLineNumbers_Expired. One of them was referencing LineNumbers!LineNumberID when the table is actually called tblLineNumbers. I had already fixed it in my version so I'm not sure which one it was now.

as this one we (you) built is only applicable to warranty part recoveries, where as the invoicing one is compatible for other SETRs (random recoveries, go 'n' see activities, etc.)


I'm curious. Why is this not applicable to these other recovery types?

qryLineNumbers_CurrentExpirationDates

It is listing all line numbers from 1-999 and expiration date as 1/1/1900.

qryLineNumbers_Expired

This one is empty. Would I be correct in assuming it didn't show any line numbers becasue 1/1/1900 is a default date, and not an "actual" value? Or is this a problem?

I'm curious. Why is this not applicable to these other recovery types?

Because they don't use the warranty system... Car might be out of warranty, or we are doing a follow-up on an old campaign or repair... etc.

The majority of recoveries we do ARE warranty based. This db will save us lots of time.

On another note, I happened to click on the Email SETR details to Warranty button on the Menu form, and I got Error 2427 (variable not defined). For some reason it doesn't like the reference Me.Check18 = -1

This is still the name of the checkbox, and I can see in the code you wrote it is supposed to have a msgbox that tells the user there is no new SETR info to be sent. Weird.

This one is empty. Would I be correct in assuming it didn't show any line numbers becasue 1/1/1900 is a default date, and not an "actual" value? Or is this a problem?

No...not a problem. It uses that date as a default when no data exists. I have assumed that you won't be dealing with any SETRs expiring prior to that date ;) The reason for this is that a data must be present for sorting purposes.

Odd. I wonder why the version I have had that error and yours, presumably derived from my copy, does not.


On another note, I happened to click on the Email SETR details to Warranty button on the Menu form, and I got Error 2427 (variable not defined). For some reason it doesn't like the reference ...

Weird. My version doesn;t exhibit this error.

Maybe if you post your current version of the db I can see what's happened.

Because they don't use the warranty system... Car might be out of warranty, or we are doing a follow-up on an old campaign or repair... etc

If they don't use the warantee system then how does the dealer know to keep/send the parts to the third-party shipper. How does the thirdparty shipper know where to send th parts. Does the third party shipper enter those parts into the web portal system?

I'm asking because, depending on the real world situation, it might yet be possible to incorporate such SETRs with some re-jigging of the db if that would make it more useful.

Odd. I wonder why the version I have had that error and yours, presumably derived from my copy, does not.

Weird. My version doesn't exhibit this error.

I may done a compact & repair... this wouldn't go so far as to update changes in the nomenclature within the db would it?!?!

Here is the most recent version.

If they don't use the warantee system then how does the dealer know to keep/send the parts to the third-party shipper. How does the thirdparty shipper know where to send th parts. Does the third party shipper enter those parts into the web portal system?

I'm asking because, depending on the real world situation, it might yet be possible to incorporate such SETRs with some re-jigging of the db if that would make it more useful.

There is no real need. We have maybe 20 or so non-warranty parts recoveries in a 12 month period. As it stands now, we send our preferred dealers a letter asking them to hold on to the parts. They send them to our shipper with the SETR # written on the box. When they arrive at the shippers, they contact us for further instruction.

It would be nice to expand the db to cover these, but as I mentioned a looong time ago, I have only until the end of December to finish this project, and since the end of December is this Friday (good ol' Christmas vacation), I don't want to add anything I don't need.

Also, I don't know if I ever mentioned that I am only a co-op student at Toyota. I will think about adding it when (if) I get hired on in September when I graduate (or plan to at least :p).

I already told my boss that I am going to implant a section of code that will render the db useless the day before I graduate! :D:D

I already told my boss that I am going to implant a section of code that will render the db useless the day before I graduate! :D:DYou might need an expert like me for that one kiddo! :)

You might need an expert like me for that one kiddo! :)

can you come up with something that will melt the computer, or something equally dramatic?

Colin,

honestly I'm not sure why you're getting that error. I think maybe there's an object somewhere in your db that is becoming corrupted. I've uploaded the version I have that works, and I've added to it the data that you had populated in the Models, VDS, and VDSmodels tables.

One thing I had noticed was that you had deleted the placeholder record that I had originally placed in the SETR table and with the comment DO NOT DELETE in the SETRNumber field. This is actually necessary to retain and won't show up in any of the forms/queries that I've built.

At some point you must have renamed the tables because I down