How would I build the following table in Access?

[jonathanchye]

Hi all,

Basically I am building an Access application based on an Excel Spreadsheet. I am trying to figure how to build the following table in Access.

I have a table to track machine run speeds called "tblRunSpeed". This table is from Excel and contains 6 columns. The first column is the Feeds. The next 5 columns are the Flute profiles (EB, BE, B, E, F). Basically a runspeed is determined from combination of Flute profiles vs Feeds. So the table is used a a lookup table to determine run speed when feeds are 500 and flute profile is EB for example.

Certain calculations would involve looking up values from this table. At the moment I have built a single table with 6 columns and it works fine.

There are no problems if users want to add more Feeds to the table but the problem occurs when they want to add a new flute profile. This involves going into the backend and manually adding the new column (field) to tblRunspeed. A codes also have to be modified to reflect this.

I wonder if there is a better/proper way of building such a table?

 

[Beetle]

You need (at a minimum) two tables. One for the Feeds and another for the Flute Profiles. For example;

tblFeeds
******
FeedID (Primary Key)
FeedDescription
other fields for Feed attributes

tblFluteProfiles
***********
ProfileID (Primary Key)
FeedID (Foreign Key to tblFeeds)
FluteProfile
other fields for FluteProfile attributes

That way a given Feed can have as many, or as few, profiles as are necessary.

You can then use a query to combine the two tables if needed for other purposes.

 

[jonathanchye]

You need (at a minimum) two tables. One for the Feeds and another for the Flute Profiles. For example;

tblFeeds
******
FeedID (Primary Key)
FeedDescription
other fields for Feed attributes

tblFluteProfiles
***********
ProfileID (Primary Key)
FeedID (Foreign Key to tblFeeds)
FluteProfile
other fields for FluteProfile attributes

That way a given Feed can have as many, or as few, profiles as are necessary.

You can then use a query to combine the two tables if needed for other purposes.

Hi,

thanks for the reply! Can you please elaborate more on combining the two tables? do you mean I run a Make Table query? This table would be used as a subtable in a main form/table called "tblSettings".

 

[Beetle]

Well you might combine the two tables in a query, or you might not, depending on the situation.

If we're talking about data entry for Feeds and/or Flute Profiles, then you would not combine them. You would have a main form based on tblFeeds with a subform based on tblFluteProfiles and the Master/Child link of the subform control would would handle the relationship based on FeedID (assuming the previous table examples).

If we're talking about a report, or some other situation where you need a single combined data set, then you would combine them, but you would do so using a simple Select query like;

Select tblFeeds.FeedDescription, tblFluteProfiles.FluteProfile
From tblFeeds Inner Join tblFluteProfiles On tblFeed.FeedID = tblFluteProfiles.FeedID;

There would be no need to create another table with a Make Table query. You would just use the Select query as the record source wherever needed (in place of a table).