Access Design and Form - Search, Add, Edit

[kwarden13]

I am looking to figure out how to best organize the data. We currently use an Excel spreadsheet with about 75 plus columns. We have unique records by supplier, address, spend, revenue, and other information. I want to put this in a database so it will be easier to update.

In addition, I am trying to find the best way to search, add, and edit within a database. Attached is an example of what I am doing. I am wondering if I should combine all the forms in one for the user so it is more seamless. Also, not sure the best way to add a record. Basically, I need the user to check to see if a supplier is in the database and if not add the supplier. In addition though the supplier might not be in the database but the ultimate supplier name is. In that case the individual needs to filter to find the correct ultimate id and name.
Example1v2mod.zip


For example, supplier1 is in the database rolling up to supplier1. Supplier2 is not in the database but rolls up to supplier1. A new record needs to be created for supplier2 with the ultimate box filled in automatically (or where the user selects it) as supplier 1 id and name.

Make sense?

[orange]

In 4 or 5 lines, what exactly is the purpose of the database?
Do you have an overview for readers to follow when looking at your posted database?

[kwarden13]

In 4 or 5 lines, what exactly is the purpose of the database?
Do you have an overview for readers to follow when looking at you posted database?

Hi Thank you for responding. The intent is to track supplier data and have an add, search, and edit form for my team to update the supplier records as necessary. We are trying to normalize the supplier names. When they update currently in Excel, they may add a record with inconsistent spelling, we want to eliminate that by having the add form search for the supplier and if it is found add it, if not add another record automatically. We have about 100k suppliers we are maintaining.

[orange]

You have currently 100K suppliers and you have inconsistent names?
And is your plan to use both Access and Excel in future?
Do these Suppliers supply products and/or services? Are these also stored and processed with Excel?

You might want to step back and review your business, identify the processes and information and determine which parts are suited to database.

[kwarden13]

As you can see in the attachment in the original question, there may be spaces or no spaces after some of the supplier names. I am cleaning them up so we can start a fresh process. The plan is to use access for all updates, however, I will need to extract the data (using a query) to Excel monthly for other teams to use. They will not be changing the data in Excel. This list is completely manual. They supplier both products and services.

[orange]

Trailing spaces may not be an issue. You can use a Trim() function to remove leading and trailing spaces.
Where and How will you record info about products and services and related suppliers?
Supplier names (and unique identifiers) is just the start, as I see things. But maybe there is more that you haven't yet told readers.

[kwarden13]

oh we have other systems for that. My team does a small fraction (fortune 100 company). I do not need to record product and services. We categorize suppliers into 8 levels, we do not process invoices or involve ap.

[kwarden13]

I am looking to do something like the link below, but with a separate dialog/form to add and one to edit the record.

http://www.access-programmers.co.uk/...d.php?t=188663

Anyone know how to do this?

Kelly

[kwarden13]

I am looking to do something like the link below, but with a separate dialog/form to add and one to edit the record.

http://www.access-programmers.co.uk/...d.php?t=188663

Anyone know how to do this?

Kelly

Anyone have suggestions? I have been working on the following.

[orange]

I'd like to help, but I do not understand the basic problem you are trying to solve.
I'm sure you understand your table, however you may get focused responses if
- you explain the issue independent of your database
- lead readers through a scenario that explains your table, the fields, and the values
- an explicit example with data highlighting the issue
- a specific result with data showing the expected/desired solution.


Also, the link you mentioned is a multi-field search.

Here is a different search that may be useful/helpful.


Good luck

[kwarden13]

I'd like to help, but I do not understand the basic problem you are trying to solve.
I'm sure you understand your table, however you may get focused responses if
- you explain the issue independent of your database
- lead readers through a scenario that explains your table, the fields, and the values
- an explicit example with data highlighting the issue
- a specific result with data showing the expected/desired solution.


Also, the link you mentioned is a multi-field search.

Here is a different search that may be useful/helpful.


Good luck

Hi Thank you for pointing me in the right direction. I am new to this forum.
1) The main issue is I have a table of Suppliers that roll up to Immediate Suppliers, and eventually to Ultimate Parent Suppliers.
Suppliers --> Immediate Supplier --> Ultimate Parent Supplier
I need to create a form to add new suppliers to a table. However, the user needs to first search in the supplier, immediate, or ultimate supplier fields to see if it exists. If not, we need to add a new record. When adding the new record, the vendor number will be a new one, however, the immediate or ultimate may already exist or may need to be a new number.

For example, if I have the following:

Supplier Immediate Parent
co1 co1 co1
co2 co1 co1
co3 co2 co4

If I add a co5 record, the immediate and parent name could be co1 or it could be a new supplier. If it is a new supplier, I need to create vendor ids for fields. If it is co1, I need to select/search co1 from the combo box.

I am looking for a form to search for suppliers and then add if the supplier is not found.

[orange]

Still not clear to me. I'm not sure what differentiates a Supplier from an immediate and/or parent. Perhaps you could clarify - real world example may be best.

My interpretation of your set up is:

You have a Supplier S. That supplier (S) may be the same company as XX where XX is an affiliated Company (part of the XX Family). I'm thinking here along the line of a franchise --say McDonalds ---McDonalds at 2100 Apple Street, MyCity; McDonalds at 300 Dixie Hiway, Your City.... they are all related in that they are part of McDonalds.

We had a similar situation where we dealt with companies. Each company that we recorded had a physical street address or mailing address, main phone... We were not responsible for recording individual company hierarchies.

As example, PepsiCo (parent company of Pepsi, Frito-Lay and Tropicana). We would have held these as 3 separate entities, and even more if the subsidiary had multiple stores in different locations.

Our rationale: We did not store nor attempt to store explicit company hierarchies. Companies could be bought, sold, amalgamated, disbanded outside of the control of our system. So our scope was dealing with companies (or divisions thereof) that had addresses, contact info, staff identified, products/service....

[kwarden13]

Still not clear to me. I'm not sure what differentiates a Supplier from an immediate and/or parent. Perhaps you could clarify - real world example may be best.

My interpretation of your set up is:

You have a Supplier S. That supplier (S) may be the same company as XX where XX is an affiliated Company (part of the XX Family). I'm thinking here along the line of a franchise --say McDonalds ---McDonalds at 2100 Apple Street, MyCity; McDonalds at 300 Dixie Hiway, Your City.... they are all related in that they are part of McDonalds.

We had a similar situation where we dealt with companies. Each company that we recorded had a physical street address or mailing address, main phone... We were not responsible for recording individual company hierarchies.

As example, PepsiCo (parent company of Pepsi, Frito-Lay and Tropicana). We would have held these as 3 separate entities, and even more if the subsidiary had multiple stores in different locations.

Our rationale: We did not store nor attempt to store explicit company hierarchies. Companies could be bought, sold, amalgamated, disbanded outside of the control of our system. So our scope was dealing with companies (or divisions thereof) that had addresses, contact info, staff identified, products/service....

You are on the right track. So I have a team that maintains a master file of all supplier hierarchys. I realize there are services we can buy that do this however we have reasons for not. Looking at your Pepsi example, we could have the following.

Supplier Immediate Ultimate
Frito Pepsi Pepsi
Pepsi Pepsi Pepsi

Another example would be Marriott

Supplier Immediate Ultimate
Doubletree Hilton Blackstone
Hilton Garden Inn Hilton Blackstone

[orange]

If you are storing info about related entities, then I think you need to use more than 1 table.

Your Hilton example is interesting, I just googled to this

.....at one of our eleven distinct brands. And with over 4,200 locations in 93 countries, now you have even more places to make unforgettable memories....

You might consider (off the cuff comment here)

ParentCompany --->RelatedCompany

Blackstone  Hilton  
                 WaldorfAstoria
                 Conrad
                 Canopy
Curio
                 Doubletree
                 Embassy Suites
                 HiltonGarden
                 Hampton Inn
                 Homewood
                 Home2
                 HiltonGrandVacations

with a 1 to Many relationship. The RelatedCompanies would include a FK of the Parent.

If you search for a Company in the Parent table and don't get a match, you then search in the relatedCompanies table. If you get a match, you know the company and the parent. If you don't get a match, how do you know where to Insert the new record?

How many levels of hierarchy do you need/plan to maintain?
How do you verify/validate/vet your hierarchical structure?


Similar set up with Choice Hotels
(family contains)
Comfort Inn
Comfort Suites
Quality
Sleep Inn
Clarion
Cambria hotels & suites
MainStay Suites
Suburban
Econo Lodge
Rodeway Inn

[kwarden13]

If you are storing info about related entities, then I think you need to use more than 1 table.

Your Hilton example is interesting, I just googled to this


You might consider (off the cuff comment here)

ParentCompany --->RelatedCompany

Blackstone  Hilton  
                 WaldorfAstoria
                 Conrad
                 Canopy
Curio
                 Doubletree
                 Embassy Suites
                 HiltonGarden
                 Hampton Inn
                 Homewood
                 Home2
                 HiltonGrandVacations

with a 1 to Many relationship. The RelatedCompanies would include a FK of the Parent.

If you search for a Company in the Parent table and don't get a match, you then search in the relatedCompanies table. If you get a match, you know the company and the parent. If you don't get a match, how do you know where to Insert the new record?

How many levels of hierarchy do you need/plan to maintain?
How do you verify/validate/vet your hierarchical structure?


Similar set up with Choice Hotels
(family contains)
Comfort Inn
Comfort Suites
Quality
Sleep Inn
Clarion
Cambria hotels & suites
MainStay Suites
Suburban
Econo Lodge
Rodeway Inn

My team manually googles and uses third party software. I cannot hard code the rollup. It changes daily. I need to have an add form to add new companies and I need an edit form to edit existing relationships.