Simple but not simple project...

[LarryE]

That's not a relational database design because you are not using Primary keys and Foreign keys to relate your fields. A primary key is assigned at the time records are created and identifies that unique record. They cannot be duplicated in that table. That Primary key is then used in other tables as a Foreign key which CAN be duplicated in other related table fields so that fields can be related. In your case, your own EDP_ID can be used to reference EACH competitors EDP number. THAT is what you refer to as a "cross-reference"

In my design, the Primary key EDP_ID is used as a Foreign key in the TblCompetetor, so YOUR EDP number can be related to other competitors EDP numbers, but the Primary keys and Foreign keys are REFERENCES ONLY to establish the realationship. Your design is attempting to use ACCESS as you would a spreadsheet. That will not work as I and others have told you. In ACCESS, you enter record data only once and then use Foreign keys to relate that data to other tables. So you enter the EDP data in your own companies TblEDP for each EDP you have. Then you enter a new record in the TblCompetetor for EACH competitor company EDP referencing YOUR EDP_ID. That is what others have called "normalization". Using ONE ID as a reference in other tables. That also means, in your case, you don't need any kind of separate pricing or cross-referencing table. That information is already in each table.

 

[bastanu]

BoatCapn,

I agree with others regarding the optimal design of a relational database; in your case to simply look over your EDP across 12 related fields in the same record using your old Excel table the answer is in post#7 and now we are at #26.

Cheers,

 

[MajP]

Ahh but I actually would have each Regal item repeated 12 times, not 3 times. so my small table becomes a 75,000 line behemoth... ;-)

A table that is 75k rows by 2 columns is much more efficient then a table of 6,250 rows and 12 columns. Orders of magnitude faster to query, and search. So the answer is YES!
In databases rows are cheap and columns are expensive.

 

[BoatCapn]

ALL - Thanks for all the guidance.

As I stated initially... I'm a relative newbie just trying to learn. I worked with a package called SMART about 3 decades ago. From what I remember it was relatively easy to work with forms and fields so I thought this would have a simple answer. I also had a brief stint with Access about 2 decades ago but I wasn't the primary guy as we were working together on a project.

I am not stuck in any one position or programming path, but was asking all the questions so I can learn. I had a mental idea of how I wanted it to work and that is what I was trying to express.

Some of the answers came out pretty harsh and I can understand that. I am on a certain boating forum where people ask the same questions over and over without looking at the forum and the regulars sometimes get irritated and snippy. Sorry if i tried anyone's patience - it was unintended.

I had what I thought was a pretty simple question about a form, input to a variable. This digressed into tearing apart the database structure, which I wasn't expecting. The example DB relationships that I showed earlier today was just that, an example of how I was thinking in regards to the setup and flow. It was by no means complete - I wanted to use the basic relationship diagram since a similar one was posted earlier. I thought that would be more clear and concise to my thought processes. I slapped it together quickly this morning.

This initial question was just part of my trying to get up to speed and refamiliarize myself with Access. Playing with tables and forms. This project is in it's infancy. I will go back and look at my data and start the layout process over.

In reference to the one of the questions... my team has already created an attribute file that is sent to clients using a vending machine solution. This file lists all the attributes of a product, many more than the 3 or 4 that I described. Our product is fairly intricate and there are about 20 plus possible attribute variables along with a description .
They also have a basic price file that lists the EDP, List Price and an internal style (used for manufacturing purposes only). This is generated by our sales / manufacturing system and output as an Excel Sheet.
The attributes will not change, while pricing can change. That's why it is a separate table. easy to update when there is a price change

And thanks for the tips on the field names, embedded spaces etc. I knew that but as I said, I quickly threw that together to generally show the structure and flow that I was thinking of. I will try to make a mental note for the future as I build my project out.

As I mentioned... I am in a learning mode. My initial question was on a "test" DB that was simply the one table. I was practicing to use a form and generate queries. Everyone's questions took me explain my total project, which is only in my mind - layout/creation stage.

Thanks again for all the help and guidance.

-Steve

 

[Gasman]

What we are trying to do is to stop you from leaving the dock when your bilge is filled with water and it is refilling as fast as the pumps can get rid of it.

Love it. roflmao