New Access Project - It's been a few years! Data Capture @ 2 PC's Merge to One Report

[MtDew4243]

Hello and Thank You in advance.



I was always a newbie but did manage to complete several DB projects in the past, but its been a few years. I just could use some direction on how to complete this one. Not afraid of slogging thru google, text books and the like to relearn but would appreciate how someone experienced might set up this project so I design it effectively.

A Distribution Kitting Project.

Overview Step One: Customer has Units it now wants to sell as kits of two. Each kit will have a New Serial Number that needs to be associated to the two individual old serial numbers. (I can use a barcode scanner to collect these three pieces of data.) My issue is to assure the correct data collection and to NOT allow Duplicate Kit Serial Numbers nor Duplicate Unit serial numbers at the time of data collection. If the associate accidentally scans the same serial number twice I need to have the Access Form stop and require them to rescan the correct one. (I believe the answer here is two tables: One for the Kit Serial Number and one for the Unit Serial Number and use a form to update both. But, I'm not sure I can have two data entry fields for the same field in one form.)

Overview Step Two: (At a separate work station, located away from first work station.) Now, I need to capture Two of the Kit Serial numbers and have them associated to a unique Master Carton Identifier Code and a the Master Carton Identifier Code to a unique Master Skid Code. (I already know that 100 Master Cartons make a Skid so, my Master Carton Identifier will already be associated to a Unique Skid ID. The Skid ID will be prominently displayed so the associate stacking the skids will know which skid the master carton needs to be placed on.) So, very similar to above but, at a case level. Again, One Unique Carton ID to Two Unique Kit Id's. And of course need to make sure that if an associate scans the same Serial twice it doesn't get entered.

Overview Step Three: If, I can get the above to process correctly, I'm working with BarTender a labeling software to Print the Carton Label since, it has to print some of the data just collected. (The serial numbers of the two kits inside that carton. This label software can link to an Access DB.)

Overview Last Step: I need to compile a report from the data collect at both PC Stations to a Master Data report to send to the customer which contains: (I haven't done it yet but believe I remember reading that multiple devices can update the same DB.)

• Kit Serial to Two Unit Serials
• Master Carton Identifier to Two Kit Serials
• Skid Identifier to 100 Master Carton Identifiers.

Well, I hope someone can assist. I don't want anyone to do the work... I'm fine as I said with relearning what I need to do in Access. But some helpful hints in the correct direction would be GREATLY appreciated! Dave

[Micron]

this isn't clear to me

Each kit will have a New Serial Number that needs to be associated to the two individual old serial numbers

A associated to B and C? or since there is "each kit" (implying there's more than one),
then A1 and A2 associated with B and C somehow, like A1 with B and A2 with C? I think you'll agree that all those are possible interpretations of your statement.

I need to capture

Another multiple meaning. Put into a table? Get from a table and put on a form? Calculate in a control?

Better suggestions are realized when the need is clearly expressed as it increases our understanding. I'm afraid the needs and concepts ideas here are not clearly communicated. You have to take the approach that we know nothing of your db, and sometimes, of the business. Don't use jargon specific to your business, and don't use different words to name the same thing (not that you are, but it's so prevalent in forums). You are going too deep too fast (IMHO) with "Master" this and that. I might be the only one who doesn't grasp this, but since you haven't received a reply yet, I decided to weigh in. Plus, often I've seen that something usually gets left out and is never properly dealt with when multiple complex requests are made.

On the simpler side, you can prevent duplication of entries by setting the table field to not allow dupes.
What you may need is a student/course type of database structure. If you think about it, one student can take several courses, but one course can also be taken by several students. You might have that sort of situation with pallets, cartons, kits and the like. Maybe there is something pertinent to your needs here http://www.databaseanswers.org/data_models/

P.S. will be on the road over the next few days. Further responses from me may not be very swift in coming.

[MtDew4243]

New to this forum. I typed a reply to your questions and attached a PDF of my logic and I don't see it here. Do you know if replies with attachments need to be verified before added? Or, did i take to long to get my reply keyed and maybe I was auto logged out before I press send. Oh well bedtime now. I will re-post tomorrow if it isn't here by then.

[Micron]

Best I can do this morning is, no vetting of attachments
Go to Forum > Community > Forum Suggestions and there is a "sticky" on how to attach. My pc is packed away for today.

[MtDew4243]

My client currently has an item it is selling as a single unit. Each unit has a unique serial number.


My client wants to make a new product by putting two of the single units together as a Kit.
This Kit will have its own unique serial number. A larger retail package that can hold two of the
Single units will be used to create the new Kit.


My client needs to keep track of which serial numbered single units it is removing from inventory to create this new Kit.


One step of this process is for the single unit serial number to be covered with a new label, “Not for resale.” Before that label is applied, I need to record the serial number so that item can be removed from their inventory.


The unique Kit serial number also needs to be recorded and related to each of the single unit serial numbers that are in it.


Next, two Kits are placed into a Master Shipping Carton. This Master Carton also has its own unique Carton ID.


My Client wants to keep track of the two Kit serial numbers that are related to the Unique Master Carton ID.


Finally, 100 Master Cartons are stacked onto a Shipping Skid that also has a unique Skid ID.


So, the four pieces of related data I need to keep track of are:

• Single Selling Unit Serial Number – Unique serial number for each unit.
• Kit Serial Number – Unique serial number for each kit and needs to be related to the two enclosed single selling unit serial numbers.
• Master Carton ID – Unique ID for every master carton and also needs to be related to the two kits serial numbers inside. (This ID number may be able to be auto generated as each new carton is created.)
• Skid ID – Unique ID for every skid created and also needs to be related to all the master carton ID’s included on the skid. (This ID number may be able to be auto generated as a new skid build is started.)

I believe I need four tables of two fields:

• tblSingleSellingUnit
  
  • SingleSellingUnitSerNum
  • KitSerNum
  
  
• tblKitSellingUnit
  
  • KitSellingUnitSerNum
  • MasterCartonNum
  
  
• tblMasterCarton
  
  • MasterCartonNum
  • SkidNum
  
  
• tblSkid
  
  • SkidNum

[MtDew4243]

Cont.

All, I believe I can create all the tables. That I can also link all the relational data.

Here is how I want my my "Step One" data collection process to be done using a Form that has only three fields:

1. First Field: Kit Serial Number - Entering the Kit Serial Number (via Scanning the serial number barcode)
2. Field Two and Three, same field from my table - Enter both of the Single Selling Unit Serial Numbers (via scanning both of those serial number barcodes)
3. After the second Single Selling Unit barcode is scanned, update record and open new record.

What I don't know how to do is to create a Form that allows me to enter the data for both the single selling units using the same field. I believe both Units need to be in the same field so I can validate that they weren't accidentally duplicated.

I could set it up that the Kit serial number is scanned for each single selling unit serial number but, I have 800,000 units to repackage. That is 800,000 additional scans I am trying to eliminate from the process.

If, I can get the "Step One" process to work above, I can use the same process for my "Step Two" process.

Again, Thanks for any help. Dave

[Micron]

Well, it is 11:00 and I'm in a hotel after a long day's drive. Just letting all know what's going on in case anyone wants to jump in. Probably won't be home tomorrow night either - sorry if that feels like I'm leaving you hanging, but you might know how long trips can be (I'm going just over 1,300 miles by car).

In the meantime, this isn't a school course assignment is it? The concepts seem very familiar here. It would be unusual if more than once person had the same problem. Or maybe you asked the question before but didn't get a satisfactory solution? If not, this might trigger someone else's memory, or you might try searching this forum with some of your keywords.

[MtDew4243]

Micron, Sorry for the delay in my reply. No, this isn't for a class project. lol

I am currently running two consulting projects. The first is opening a Distribution Center, which is taking most of my time. The second is this Access Project. Let's strip all the above down to what I'm really have difficulties with. I need to validate that two fields doesn't contain duplicates. I know how to turn that on for a single field of data entry. I know I'm probably thinking this wrong but can't seem to shake how I want to enter the data. I keep wanting to have each record be three fields:

• CityZipCode (Unique Number)
• HouseAddress (This comes from the same data type as the below field)
• HouseAddress (This comes from the same data type as the above field)

I know you are not supposed to have the same data type in two different fields of the same record. I also know that fields names can't be the same. But, the way the data is being given to me, it seems the best way to collect it. I'm trying to use terms you may understand this is not the actual data being collected.

My data is such that I HAVE two house address' with only one City Zip Code.

The house address' are always unique and can't be duplicated. (I don't know how to validate duplicate data between fields of the same record.)

The same City Zip Code can't have more than two House Address' associated to it.

I know I could set up the table to be two fields:

• CityZipCode
• HouseAddress (Unique Number)

But, that would require me to enter the City Zip Code twice. And I have over 800,000 units to capture. That would require me to take the time to enter this field twice.

If you have a better suggestion, I'm all ears. Thanks Again!

[June7]

On a form, set up so the first HouseAddress is enabled and the second is not. Can use Conditional Formatting. The rule for the second can be like: Not IsNull([tbxFirst]).

Then ValidationRule for the second can be like: <>[tbxFirst].

So far, no VBA used.

Of course, complication arises if user deletes or changes value in first textbox after entering value in second textbox.

Now might want to use VBA code to validate data, perhaps in the form BeforeUpdate event.

[Micron]

I need to validate that two fields doesn't contain duplicates.

One way is to create a composite primary key, or composite index in your table. Either way, you will not be able to put the same combination of data in one record within that key or index. So, you across field1 and field2 can have
A,B
A,C
B,C
B,A but no two combinations can be the same as in AC then AC in another record somewhere else in the same table. The advantage of doing this at the table level is that you let Access handle the validation. If anyone but the developer would be using the db, then it may be best to trap for the error and present an instruction that is less cryptic.
FWIW, some here have dissed composite pk's but no one has ever said why in spite of being asked. Probably just personal preference.

the way the data is being given to me, it seems the best way to collect it.

Maybe not. Storing it in rows in the usual (proper?) fashion should be an option.
90120 | 123 Main St
90120 | 123 Main St
is better than
90120 | 123 Main St | 123 Main St
At least with the latter, something else in the record could be the value(s) that differentiate one record from the other. If not, then I fail to see the need for identical values regardless of how they're positioned. Maybe what you're saying is that you have, or will have
90120 | 123 Main St
90120 | 127 Main St
90120 | 129 Main St
90120 | 131 Main St
but this I don't get why not

The same City Zip Code can't have more than two House Address' associated to it.

My neighbour and I have the same code.
If you're simply wanting to prevent a repeat of any two combined values, then the above suggestion can apply. I'd lean that way over coding the validation. If this is really about address data (as opposed to being pseudo data so that the real issue isn't being described) then if the answers so far don't help, you should post sample data and desired result, plus explain where you're getting the data from. Surely you have a data source for 800K addresses?

[MtDew4243]

Micron / June7,

Due to a contract, I can't tell you the company my client is doing this for but let say it is Apple and their iPhones (It's not). Apple has individual iPhones in their warehouse. Sales have slumped so they decide to sell two iphones in one package for less than the price of two individual phones. All of these iPhones have a unique serial numbers. My clients task is to capture the serial number of all of the phones and relate them to which 2 Pack's Carton Number they are packaged in. For ease of data entry, thankfully, all the data is also in barcode format. So, my client can use a barcode scanner to enter the data into the access DB. They want to make the data entry as fast (and accurately) as possible. Hence why they only want to scan the 2 pack carton number once.

So, they want to scan the 2 pack carton number. Then, they want to scan each of the iphone serial number going into that 2 pack. That would be the first record. Rinse and repeat.

2PackCartonData

CN00001
CN00002
CN00003
CN00004
CN00005

UnitID (Serial Number), two of these go into one of the above.
S123456
S234567
S345678
S456789
S567890
S678901
S789012

[MtDew4243]

June7, I think your tips have me on the right track. I believe my form is doing what I want but, it doesn't validate after each field. It validates at record update and also doesn't tell me which field is in error.

I know there is the ability to have access perform functions when changing fields (After Update) but, I don't know what to tell it to do on that function.

Thanks

[June7]

So code in the second textbox BeforeUpdate event can check value against value of first textbox. If they match then cancel the textbox update and notify user they must enter something else. Fairly common question. Should be many examples.

[MtDew4243]

Can the before update also work on the first 2 data boxes as well? Meaning can they see if that data was already used in a previous record?

THANKS A TON!

[MtDew4243]

OK, so it looks like I get it to error on any field that has a duplicate using the Before Update function. The error message is still a bit vague but OK.

Is it possible that when they acknowledge the error that the duplicate data entered gets Selected (highlighted??) so when they enter the corrected data it replaces what's there vs having to press backspace to delete before re-entering?

So Close!!!