Consolidating Table Structures

[chemguy120]

I have been working the past several months on creating a 2.0 version of the database I use to keep track of the shipping information for equipment returns for various customers. At the moment I’m trying to consolidate what was initially a large number of tables. In the future I will be working on search and report features but for now I just need the data entry streamlined.

All but one of the return types flow as follows: The customer brings in the equipment (Customer table), a work order is started (Reference table) and is associated with a specific return program (Return Type table), a single shipment for their current return is created (Shipment table), and the items being returned are associated with that shipment (Return table).

For the above work flow, each specific program may have different information provided or not provided so there will be blank fields within each record. Each return type has it’s own data entry forms and sub-forms as to not needlessly confuse the person doing the data entry, but they all input into the above tables.



One return program is different and I’m having trouble figuring out how to integrate it into the main table sets. The flow proceeds as follows: The customer brings in the equipment (alt Customer table), a work order is started (alt Reference table), and the items being returned are associated with the work order (alt Return table). After the fact, a shipment is created that may contain devices from multiple work orders by initiating a new shipment (alt Shipment table). The shipment form for this contains a lookup of all device serial numbers not already associated with a shipment and links the shipment to each device in the return table it applies to.

I’ve attached a copy of the database with a few sample entries. The only way to get it down to the correct attachment size was to compress it as an .rar and then manually rename it to a .zip so it may need to be changed back depending on your software. The goal is to not have duplicate tables, but because of the different shipment processes I’m at a loss how to consolidate them.
Thanks in advance for any help!

[ranman256]

the zip file does not open.

[CJ_London]

The only way to get it down to the correct attachment size was to compress it as an .rar and then manually rename it to a .zip

remove all unrelated tables, queries, forms, reports and modules. Reduce table rows to a number sufficient as an example and change anything which is confidential - particularly names and addresses. Then compact the file and zip. then upload the zipped file

[chemguy120]

All of that has been done but it still remains about 250K above the 500K. I was able to download the attachment and unzip it without changing it back to an rar and it opened fine. I use WinRAR for zipping if that may help. I'm also using the Access 365 with the latest updates installed.

[June7]

I also cannot open the zip - invalid error.

I don't have WinRAR. Use Windows Compression to zip the file.

[chemguy120]

I found someone using a utility that should be more compatible with windows standard. Hopefully this works. I also forgot I hadn't removed the return specific logos from the entry forms so that helped reduce the overall size.

[davegri]

Test Database.zip
I was able to unravel the zip/rar.
Here is the accdb zipped and unchanged from OP.

[June7]

Yes, it worked but why not just use Windows Compression? You should have that utility as it is intrinsic to Windows.

Why use ATT instead of ALT as prefix in table names since your narrative uses alt?

Don't understand why there are two customer tables.

What is being returned? Items that were originally shipped?

[chemguy120]

Yes, it worked but why not just use Windows Compression?

Originally Windows Compression would not get the file small enough but as I mentioned I had forgotten to remove the program specific logos form the data entry forms at first. After I removed them the file size was small enough.

Why use ATT instead of ALT as prefix in table names since your narrative uses alt?

The alt in the message text is just indicating to the reader it is an alternate table set. The table names in the attached database are named to refer to the specific work flow it's being used for so I know what they are when I'm working on it.

Don't understand why there are two customer tables.

There are two table sets because the bulk of the work flows are customer>reference>shipment>return with a form to enter delivery information into the shipment table later on.

The "problem" work flow is customer>reference>return followed by an independent shipment>return to link the specific returned devices to the shipment that contained them. Again, shipment delivery info is added at a later date.

My problem is finding a way to integrate the two table sets without the required relationships causing a ton of different errors.

What is being returned? Items that were originally shipped?

Various equipment being returned to the appropriate companies. The purpose of the database is to provide a quick way to organize and retrieve information when needed.