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

[Minty]

You still need the competitor data stored in a normalised fashion, this is where the cross-reference table is incorrect.

Currently, you would need to add a field to your table if competitor 4 came along and redesign all your queries, forms, etc.

You need the Junction table described by MajP which allows for as many competitors and products as you need to match.
If you already have the data like your existing table you can query it to create a normalised structure.

 

[MajP]

you need to normalize the tblCross_Ref

tblCross_Ref
---RegalEDP
---CompetitorEDP

Instead of a row with 1 Regal and 3 manual you will have 3 rows with the Regal EDP repeated. Now easy to search by either, related through the regal.

 

[BoatCapn]

you need to normalize the tblCross_Ref

tblCross_Ref
---RegalEDP
---CompetitorEDP

Instead of a row with 1 Regal and 3 manual you will have 3 rows with the Regal EDP repeated. Now easy to search by either, related through the regal.

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... ;-)

 

[BoatCapn]

Currently, you would need to add a field to your table if competitor 4 came along and redesign all your queries, forms, etc.

I really wouldn't need to add fields. Currently there are 12 competitors in the table but at least 2 of them are Out of Business and their info could be replaced without adding additional fields and screwing up the queries.

I understand now about the normalized.

 

[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,

 

[Pat Hartman]

1. It is not cool or efficient to name all PKs with the same name. Use a name that reflects the table it is the PK for.
2. You show four tables but only two relationships AND RI is not being enforced.
3. It is poor practice to use embedded spaces or other special characters in ANY object names.
4. Both relationships are incorrect.
5. I do not understand why attributes and prices are separate tables.

Although it is recommended to use autonumbers as the PK for a table, it is not required. You can use any piece of data that is unique. However, when you use an autonumber, it MUST be the PK and it MUST be used in the relationships. You are drawing relationship lines between data fields. The relationships are always FK to PK. Don't confuse relationships with joins. Although joins in queries almost always mimic the defined relationships, there are occasions where they might not. That is too confusing a concept to bother with at this point. If the universe of the EDP numbers NEVER includes duplicates over the entire range of companies, you could use the EDP number as the PK for the tblItemList.

It is time to take off your spreadsheet hat and put on your relational database hat. One thing that will help with the transition is understanding a basic difference between a spreadsheet and a relational database (ANY RDBMS, not just Access). And that difference is that in a spreadsheet, the data and presentation layers are combined. That is why you always use names with embedded spaces and even special characters because these are the names tha users see. In a relational database, the users NEVER see the underlying tables or queries. They see and interact with forms and reports (which have captions that are used to name the data fields rather than their actual field names) because in a relational database application, the presentation and data layers are separate. Because of this, the data layer - Tables, is always separate from the presentation layer - Forms and Reports. That means that tables are organized for efficiency. They eliminate redundancy. You can always go from a properly normalized schema back to your original spreadsheet by using a query that takes the normalized tables and joins them and then presents them using a crosstab query as the matrix you love so much in Excel. But we NEVER work with a table that looks like a matrix. Why? Because each new data value across, requires modification to all the forms, reports, and queries that have to interact with that table. Columns that are just instances of the same piece of data with different values belong as rows in a table. Rows are free. Columns are expensive. It makes absolutely no difference at all to anything how many rows a table contains (within the size limits of an Access database). However, columns are limited to 255 per table and 99% of your tables will be < 50 columns. In all the years I have been designing database applications (> 50), with the exception of the one warehouse application I built, I have never had to create a table with more than 100 columns. Warehouse applications are different because they are never updated by users. They are built on a schedule, weekly, monthly, whatever makes sense. The tables are denormalized to help the user to make queries. They are not optimized for efficiency or to eliminate duplication.

Think of the spreadsheets you've made that deal with monthly data. You just add a month's worth of columns for each new month you want to add and you update all the formulas (except those you forget to update). After a couple of years, the spreadsheet becomes unwieldy. That doesn't happen in a relational database because new months mean new rows and we don't care at all how many rows are in the table. We just use criteria that select the rows we want. So, it is very easy to make a query with a rolling 12 months of data for example. All the old data is still there so we can look at ANY 12 month period by simply specifying the start date or end date. If we want a variable range,we specify both a start and end date. With your spreadsheet, what you see is what you get.

 

[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.

 

[Pat Hartman]

I have ACE (the .accdb database engine) BE's with millions of rows of data. The BE isn't even alarmingly large because there aren't any text fields in the big table. I have SQL Server and DB2 BE's with tens of millions of rows. As long as you understand how Access interacts with RDBMS' and don't try to bring back recordsets containing millions of rows, Access works just fine as the FE.

Computers are dumb as rocks but they are faster than a speeding bullet and they can hold tons more data than humans can process manually. So, 75k is trivial in the world of relational databases.

 

[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

 

[Pat Hartman]

@BoatCapn 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.

When you are building a database application, the first step is defining your data and organizing it into properly designed tables. Imagine how hard it is to fix a foundation when you are building the 10th floor. It's fine to play around and get your bearings learning how to create a form but you are not even close to where you can build a form unless you are OK with building it over and over and over again. Each time you discover a flaw in the schema, you have to rebuild all the objects that depend on those tables. If you want to try stuff, create a test database and play there. If you do your experimenting in your development database, you will be tempted to not validate the process at each step because you don't want to find out that you missed something important. If you ever rise to the level of competency, you will recognize that experts sometimes jump the gun also because they want to get to the fun part. the difference is that experts don't make rookie mistakes like trying to use a spreadsheet as their main table, but if they do miss something, they also toss the bad solution and go back as far as they need to to build on a proper foundation. Novices never do that. They keep asking for Band-Aids because they don't understand what they have done so far and so are terrified that they can't recreate it, let alone fix it.

We have seen people try to use a spreadsheet and know that ultimately it doesn't work. Many of us have even done it ourselves and have the scars to prove it.j It becomes way too difficult to change so more than likely, the app just gets scrapped when the developer can no longer work around the problems caused by trying to use a spreadsheet as a relational table. An they rant and rail about how bad Access is in the process.

You haven't been here very long. There are lots of "experts" who think we keep score (we don't) and are always ready to jump right in and help you load those bullets into your gun and even help you to point it at your head. Others will try to talk you off the ledge.

If you can upload the spreadsheet, we'll have a better idea of what you need for a schema. And we can tell you how to get from here to there

I would hold off on telling people what you need for an input file when you don't have the database even designed yet. You are getting way ahead of your self.

 

[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