Simple but not simple project... (1 Viewer)

[BoatCapn]

I am a relatively newbie to Access. Familiar with relational databases and did some DB programming 20-25 years ago... but can't remember certain things...

I have a pretty basic project I am trying to create. Basically want to create a form to cross reference competitive EDP numbers with my companies EDP numbers. Stuck trying to get the Unbound Text box data into a variable that be referenced in the query.

Simple table: My EDP, Comp#1 EDP, Comp #2 EDP... etc. with about 10,000 records.

Eventually I will add a check box system that will direct the input to correct query field. IE: Check Box #1 will allow Comp#1 input. Check Box #2 will allow Comp#2 input. There are maybe 8-10 competitors...

I can create the query.

So the form would be:

EDP Input [12345]

Query results: Field 1 (My EDP 54321), Field 2 (Comp #1 EDP 12345), Field 3 (Comp#3 EDP 78956)

Eventually I will add the relationship between my EDP and the Item Description (I have this down...)

Just need to get the variable input figured out.

Then I want this to be locked out so the form is the only access and the table cannot be changed.

Thanks!

 

[Pat Hartman]

You might want to step back and rethink your table design. It is not normalized and that will lead to extra code and more complications throughout the entire application. Best to get out of the weeds and normalize first.

Let's start with
1. Where is the data coming from? Do you already have a cross reference built or is that what you are trying to do?
2. Have you built a schema? Can you post it please?
3. I understand why you might not want random people updating the cross reference but how can you be sure you won't need to do business with a new company next week?

 

[BoatCapn]

You might want to step back and rethink your table design. It is not normalized and that will lead to extra code and more complications throughout the entire application. Best to get out of the weeds and normalize first.

Let's start with
1. Where is the data coming from? Do you already have a cross reference built or is that what you are trying to do?
2. Have you built a schema? Can you post it please?
3. I understand why you might not want random people updating the cross reference but how can you be sure you won't need to do business with a new company next week?

Hi Pat,

Table was already built originally as an Excel spreadsheet and imported. It actually lists Brand A EDP, Brand A List #, Brand B EDP, Brand B List #.... Currently there are 12 competitive companies in the table. Not sure what you mean by normalized. The data is clean. I simply want to query a brand and have it show me our corresponding part number. Not worried about the Style or List Data. I may simply remove those fields leaving only the EDP fields. In each EDP column, there should be no duplicate items (Except for the "Style" or "Product List" column. The EDP cross reference is the important data.

We are an established manufacturing business that makes a very specific product. There are only a very few additional competitors that can be added later to expand the table.

The idea is to empower our sales force with this as a tool to convert business.

 

[Pat Hartman]

A spreadsheet that you import into a database is NOT a relational table. It is still a spreadsheet. Why even bother to use a tool like Access if your data is a spreadsheet? You get no benefit. Just stick with Excel.

At a minimum, you need three tables.
1. Companies (including yours)
2. Products (I can't tell what this is actually called. Seems like EDP whatever that means. (includes Company and Style)
3. Styles

This gives you no limit on any of the three pieces of data. You can add products or companies or styles as needed. Each row in the spreadsheet will translate to 10 rows (one for each company) for each row from the spreadsheet.

You can use a crosstab of Products to produce the "spreadsheet" view you are showing.

 

[BoatCapn]

Pat,

This is simply one piece of a larger project that I am working on.

EDP # is a unique (to each manufacturer) item code for a specific technical product that we (and others) manufacture. I believe EDP was originally "Electronic Data Processing #".

There are variations of each item based on the usage in my industry. If you are familiar with machining, an example could be drills. There are short drills (referred to as "Screw machine"), medium length (referred to as "Jobber") and long length (referred to as Taper). Each with many diameters available. Additionally, there are a number of surface treatments or coatings also available. This creates thousands of unique part numbers (EDP #).

Once complete, this package will also have a related table that gives the dimensional figures, a pricing table, a generic image table. Additionally, a way to create a report allowing a "wish" or "order" list. And this is only one portion of my product portfolio. There are 3 other products that will also be integrated.

So there is much more than a simple table. I am up to speed enough to relate tables and setup those queries. It is the input into the form that has me stumped as I am not very proficient in the VBA/Macro area.

Steve

 

[Pat Hartman]

Sounds like a fourth table is necessary. You have product with descriptive data. Then you have ProductCompany which relates a product with each company that supplies it. This is called a junction table and you also have ancillary data in this table such as price or any special attributes of the product as delivered by that vendor.

Just need to get the variable input figured out.

- is not informative.
There is no point to creating forms until the schema is correct. Also, I don't know what data you are trying to enter. Are you saying that you want to select x companies from the list and have only those records show on the form? Why would you not want to see data from all companies that supply the product?

 

[bastanu]

Steve, what exactly is the problem with your form? Are you trying to search\load a specific record by inputting an EDP # and look for it in one of the 10-12 competitor EDP?

With your current model you can make the input a combo box with a union query as its row source:
SELECT [Our EDP], [Comp 1 EDP] As COMP_EDP FROM tblEDP
UNION
SELECT [Our EDP], [Comp 2 EDP] As COMP_EDP FROM tblEDP
UNION
SELECT [Our EDP], [Comp 3 EDP] As COMP_EDP FROM tblEDP
UNION
........

Then in the AfterUpdate of the combo you load the record based on the first column (your unique EDP code). For that you can simply use Docmd.FindRecord (make sure you set the focus first to the textbox bound to [Our EDP] field):

DoCmd.FindRecord method (Access)

Office VBA reference topic

learn.microsoft.com

or use the more elaborate form Bookmark method:

Form.Bookmark property (Access)

Office VBA reference topic

learn.microsoft.com

Cheers,

 

[Pat Hartman]

Except that union queries are not updateable so we rarely use them as the RecordSource for a form.

 

[bastanu]

And I am not saying it should be used as the form recordsource but the search combo's row source.....

 

[LarryE]

Pat,

This is simply one piece of a larger project that I am working on.

EDP # is a unique (to each manufacturer) item code for a specific technical product that we (and others) manufacture. I believe EDP was originally "Electronic Data Processing #".

There are variations of each item based on the usage in my industry. If you are familiar with machining, an example could be drills. There are short drills (referred to as "Screw machine"), medium length (referred to as "Jobber") and long length (referred to as Taper). Each with many diameters available. Additionally, there are a number of surface treatments or coatings also available. This creates thousands of unique part numbers (EDP #).

Once complete, this package will also have a related table that gives the dimensional figures, a pricing table, a generic image table. Additionally, a way to create a report allowing a "wish" or "order" list. And this is only one portion of my product portfolio. There are 3 other products that will also be integrated.

So there is much more than a simple table. I am up to speed enough to relate tables and setup those queries. It is the input into the form that has me stumped as I am not very proficient in the VBA/Macro area.

Steve

Please post a screenshot or picture of your table and field relationship design. Without that, no one here even knows how to help you. As a boat captain, you surely know what happens when you pilot a boat into deep waters with serious flaws in its hull. Please start with the design first, then we can go from there.

If you are going to compare your companies data with other companies data and then calculate the differences on a form or report, you will need to:

1. Create a query that includes all of that data from tables with Primary keys and Foreign keys defined and linked so that you know which STYLES each COMPANY has to compare to yours.
2. Create a form or report with that query BOUND as a record source and add all the fields.
3. Create an unbound textbox control on that form or report that has a control source comparing the difference between EACH companies EDP field and your own EDP field for each product STYLE.

Do you know how to do that? Because you don't need to know much VBA code to do that (none actually).
That is why we need to see your design (schema as Pat defined it). Because without it, we don't even know which fields are related in which tables. So we cannot determine which fields names to compare to get the differences.

Please do NOTt attempt to build forms or reports before you get the tables and fields properly designed and related first. They call it a RELATIONAL database for a very good reason.

Open the Relationships tab under Database Tools tab and take a screenshot picture of your table and field design. Then attach the picture in a post so we can see what you have.

Thank you for your kind cooperation

 

[BoatCapn]

OK. Let me try to explain this. I have a table (Data compiled from Excel throughout many years in my industry. This table is a basic cross reference matrix for my current company and other competitors. So the data is as such, note that the description is for the example. Only the actual part numbers exist in the table:

<Screwdriver> My Co - #123; Competitor #1 - #456; Competitor #2 - 789... etc. Note - these numbers are item part numbers.

The initial project is to enter into a text box (EG: 789) and have the result be 123. I would like the result 123 to show into a separate text box on the form.

I currently have the form basically working but for only one competitor, not all. I did this by entering this formula in the criteria field in the query design for the Brubaker competitor - [forms]![Tap_Cross_Ref]![XRef_Input]. Where XRef_Input is the name of the input text box on the form. The query works fine but the flow goes from the input text box <Enter>to the Regal_EDP Text Box<Enter>, then performs the query and the query results table appears.

Once the Regal part number is selected, that is what the relational tables will use as the project grows. Sorry to be such trouble. I haven't worked in Access or with databases in decades. So I'm sort of lost here right now.

Thanks

 

[bastanu]

So look again at my initial post (#7). Replace the textbox Input with a combo box as it easier to use because of the autocomplete feature and not prone to misspells like a textbox. Also you want the Input to be in the form header, not the detail.

Once you set up the union query and use it for the row source you simply add in its AfterUpdate event:

Me.Regal_EDP.SetFocus
Docmd.FindRecord Me.cboSearchEDP 'this is the input combo

The code above assumes the form is bound to the full EDP table; Once the input is found it will navigate to that record. You could alternatively reset the form's recordsource to only include that searched record, again this would be in the AfterUpdate event of the combo:

If Not IsNull(Me.cboSearchEDP) Then
    Me.RecordSource="SELECT * FROM tblYourEDPTable WHERE REGAL_EDP = " & Me.cboSearchEDP 'assumes EDP is numeric,if text you need to wrap it in single quotes
Else
    Me.RecordSource="SELECT * FROM tblYourEDPTable;" 'show all records
End If

Cheers,

 

[GPGeorge]

OK. Let me try to explain this. I have a table (Data compiled from Excel throughout many years in my industry. This table is a basic cross reference matrix for my current company and other competitors. So the data is as such, note that the description is for the example. Only the actual part numbers exist in the table:

<Screwdriver> My Co - #123; Competitor #1 - #456; Competitor #2 - 789... etc. Note - these numbers are item part numbers.

The initial project is to enter into a text box (EG: 789) and have the result be 123. I would like the result 123 to show into a separate text box on the form.

I currently have the form basically working but for only one competitor, not all. I did this by entering this formula in the criteria field in the query design for the Brubaker competitor - [forms]![Tap_Cross_Ref]![XRef_Input]. Where XRef_Input is the name of the input text box on the form. The query works fine but the flow goes from the input text box <Enter>to the Regal_EDP Text Box<Enter>, then performs the query and the query results table appears.
View attachment 104666

Once the Regal part number is selected, that is what the relational tables will use as the project grows. Sorry to be such trouble. I haven't worked in Access or with databases in decades. So I'm sort of lost here right now.

Thanks

A while back I wrote a blog article that seems highly relevant to this discussion, especially the third point entitled "I Did It My Way".

 

[LarryE]

You are attempting to use a single table and an unbound form and then create query criteria based on the input from that form? After 13 posts, is that correct? You are not actually building a relational database, you are using an ACCESS table like you would an EXCEL spreadsheet? I guess I am still not sure. You wish to use ACCESS for some of your new analysis work but not all of it?

I asked that because you state, "I currently have the form basically working but for only one competitor, not all." In ACCESS, the forms and reports you create work for all records or no records. So a form should allow input for all records or none (but you can certainly filter forms to show only certain data). I really think you should do what Pat and I suggested and design it properly from the beginning. If you wish to see an example, I would be happy to help you by providing an example and attaching it for you to study and use as a template. But a word of warning. It will not operate like a spreadsheet. Just let me know. And I am sure others can contribute as well. Just let us know.

 

[LarryE]

After reading through everything again, it sounds like you have:

1. Multiple companies (including yours)
2. Which manufacture multiple products
3. Each product has an EDP number, part description, part style, individual company part number, price etc.

So you want to create a system that will allow someone to search for any competitors EDP number in order to compare your own EDP information, (such as price).

I seems to me there is only one common field for each part and that is its description, because no other common information exists between competing companies. So you could search for "Screwdriver", for example, and the search could return all records with "screwdriver" in the description field or part of the description field. Then, you could compare your screwdriver EDP information with other companies.

Does this sound correct?

 

[BoatCapn]

After reading through everything again, it sounds like you have:

1. Multiple companies (including yours)
2. Which manufacture multiple products
3. Each product has an EDP number, part description, part style, individual company part number, price etc.

So you want to create a system that will allow someone to search for any competitors EDP number in order to compare your own EDP information, (such as price).

I seems to me there is only one common field for each part and that is its description, because no other common information exists between competing companies. So you could search for "Screwdriver", for example, and the search could return all records with "screwdriver" in the description field or part of the description field. Then, you could compare your screwdriver EDP information with other companies.

Does this sound correct?

Larry,

1. Multiple companies (including yours) (Currently in fields)
2. Which manufacture multiple products (currently in records)
3. Each product has an EDP number, part style, (for each manufacturer)

This is all the data in my current table.

You are on the right track but the initial reference of "Screwdriver" is incorrect. The difference being the operator would know a certain competitors EDP number. I simply want them to enter it and then have our EDP number as the result. This is the cross reference part - crossing an EDP from a competitors EDP to my EDP. I know that this operation is similar to a spreadsheet operation. I am not concerned with Competitor A's EDP 1234 equals Competitor B's EDP 3456. What I am concerned with is that Competitor A's EDP 1234 equals our EDP 08332

I am in the middle of assembling an attribute data file of my items. Once complete it will become a linked table by our EDP number. So the idea is once an item has been crossed to our EDP, then the linked EDP dimensional attribute data would appear. (Length, diameter, shank length...)

An additional table, also linked by the EDP, would be a list pricing table.

 

[Minty]

There is a glaring problem here you are storing your companies data in fields, not in records.
You are still in spreadsheet mode, and have been told that won't work.

You need your data vertically stored not horizontally :

PRODUCT TABLE					COMPANY TABLE
Product_Name	Company_IDFK	EDP_Code			CompanyID	Company_Name
Screwdriver	1​	ABC123			1​	My Company
Screwdriver	2​	99NHT			2​	Acme Ltd
Screwdriver	3​	3-4N2			3​	Ford Ltd
Hammer	1​	ABC234
Hammer	3​	8-5D1

Now you can easily query the table to return all Screwdrivers

 

[LarryE]

Larry,

1. Multiple companies (including yours) (Currently in fields)
2. Which manufacture multiple products (currently in records)
3. Each product has an EDP number, part style, (for each manufacturer)

This is all the data in my current table.

You are on the right track but the initial reference of "Screwdriver" is incorrect. The difference being the operator would know a certain competitors EDP number. I simply want them to enter it and then have our EDP number as the result. This is the cross reference part - crossing an EDP from a competitors EDP to my EDP. I know that this operation is similar to a spreadsheet operation. I am not concerned with Competitor A's EDP 1234 equals Competitor B's EDP 3456. What I am concerned with is that Competitor A's EDP 1234 equals our EDP 08332

I am in the middle of assembling an attribute data file of my items. Once complete it will become a linked table by our EDP number. So the idea is once an item has been crossed to our EDP, then the linked EDP dimensional attribute data would appear. (Length, diameter, shank length...)

An additional table, also linked by the EDP, would be a list pricing table.

OK. To start then, I would suggest a table and field structure something like this:

TblEDP is your companies information only. Your EDP is linked to each competitors EDP with a Foreign Key EDP_ID

 

[MajP]

Without knowing the data I am guessing I would modify @LarryE design. I would think this is not a one to many but a many to many. I may make screw driver A and B. A has competitor X, Y, Z, and B has Competitors V, W, X, Y. Thus a many to many
So in between TblEDP and TblCompetitor I need a junction table

Tbl_EDP_Competitor
--- OurEDP_ID_FK
---Competitor_ID_FK

 

[BoatCapn]

OK. To start then, I would suggest a table and field structure something like this:
View attachment 104689
TblEDP is your companies information only. Your EDP is linked to each competitors EDP with a Foreign Key EDP_ID

Hi Larry,

This isn't the actual DB but this is what I am looking at. I have the top row of related tables. I simply want to enter via a form, a Manuf EDP have it return the Regal EDP which then links to other tables so the resultant data is viewable but not changeable. (Bottom table for the input trigger).

I can enter a Regal EDP and have it link to the upper tables. I am trying to add the functionality of using a competitors EDP to trigger it.

Does this make sense? Sorry for any confusion in my previous explanations.