How to Display Values from Reference Table Based On User Input Table

[lsiftar]

Hello,

I am working on an electric cable voltage drop database that currently contains two tables. One table contains fields that describe electrical equipment, equipment voltage, cable size, length, etc. The second table contains information from the National Electric Code that I want to grab values from depending on the information that is entered into the first table. The second table is essentially my reference table.

My goal is to display "Resistance" and "Reactance" values from the second table for "Cable Sizes" that are stated in the first table. My result should display a number of rows equivalent to the number of rows in the table "tblVoltage_Drop". My final goal is to take "Resistance" and "Reactance" values and use these to calculate voltage drop.

[ranman256]

join the 2 tables on Material. (grab the field in 1 tbl , drag it to the other table and drop it onto the same field)
checkmark the fields you want to see in the grid.

[Gicu]

You probably need to add a join between [Cable Size] and [Wire Size] too.

Cheers,

[lsiftar]

I appreciate the replies. I have joined the two tables on Material as well as a join between [Cable Size] and [Wire Size]. I ran the query and it returned [Resistance] values that for Cable Sizes that have a material affiliated with the row. However, Cable Sizes with null [Raceway Material] values do not show up in the query. Is there any way I can fix this?
The only way I'm thinking of is splitting the [Voltage] column into two different columns, [Voltage] and [Phase], and then joining the [Phase] column of both tables.

[Gicu]

Try to change your joins to outer joins - right chick on both joins and change them (either option 2 or 3) to point from the NEC table towards the Voltage table.

Cheers,

[lsiftar]

Okay so I have done this, and I've also added the option of selecting "N/A" under the material field, as some cables will not need Aluminum/Steel/PVC. I am finally seeing results. Seeing as I am fairly new to Access, I don't know an expression to put into the criteria of "Resistance" that will display all resistance values that I want.
If I try the method of expression building that I have been using, I will run out of characters in the expression builder. My expression is something like this:

Switch([Cable Size]="14 AWG" And [Raceway Material]="Aluminum",[Resistance],[Cable Size]="14 AWG" And [Raceway Material]="Steel",[Resistance],[Cable Size]="14 AWG" And [Raceway Material]="PVC",[Resistance])

And so forth, but I have 21 cable sizes to do this for and I don't think it will fit inside the expression builder. Is there a more efficient way I can do this? Thanks

[orange]

Can you post a copy of the database with only a few records to highlight the issue/requirement? You might be able to create another table to deal with the criteria.
Here's a link to a technique that may be applicable with some creativity.

[Gicu]

Okay so I have done this, and I've also added the option of selecting "N/A" under the material field, as some cables will not need Aluminum/Steel/PVC. I am finally seeing results. Seeing as I am fairly new to Access, I don't know an expression to put into the criteria of "Resistance" that will display all resistance values that I want.
If I try the method of expression building that I have been using, I will run out of characters in the expression builder. My expression is something like this:

Switch([Cable Size]="14 AWG" And [Raceway Material]="Aluminum",[Resistance],[Cable Size]="14 AWG" And [Raceway Material]="Steel",[Resistance],[Cable Size]="14 AWG" And [Raceway Material]="PVC",[Resistance])

And so forth, but I have 21 cable sizes to do this for and I don't think it will fit inside the expression builder. Is there a more efficient way I can do this? Thanks

You lost me here, where/why do you need that expression? Can you upload a small sample db with what you have to date along with a mock-up on how you want your query results to look?

Cheers,
.

[lsiftar]

I am trying to use that switch expression to say "if this cable is inputted by the user, this is the resistance/reactance that are displayed according to the reference table from the National Electric Code depending on what raceway material is being used".

I've attached a word file that explains the purpose behind each table and query that I have so far in my database. Right now, my primary focus is on the query "qryResistance_Reactance" which is the query I want to display these values in. My final goal is to pass these values to my other queries (assuming this is possible) so that I can update the user table "tblVoltage_Drop" with calculated columns "Voltage_Drop (V)", "Voltage_Drop (%)", and "Voltage_At_Equipment".

I'm hoping that I can create a form and report out of my results so that a user can easily enter electrical cable data and then print out a formal report for a project. Sorry if this is a lot to read, I'm just trying to get the hang of Access.

[orange]

"I'm just trying to get the hang of Access."

It might be easier to start with a simple database, learn some basics, then proceed with your proposed project.

There are a number of articles at the link in my signature for Database Planning and Design.
I recommend you work through 1 or 2 of the tutorials from RogersAccessLibrary identified in that link.
Each tutorial will take 45-60 minutes, but you will learn some database concepts(Normalization) and you will experience a process for designing a database to support your project.

Good luck with your project.

[ssanfu]

For naming objects (fields, tables, forms, queries, reports), here are some naming suggestions:

Use only letters and numbers (exception is the underscore) for object names.
*** Do not use spaces, punctuation or special characters in object names. ****
Do not begin an object name with a number.
Do not use Look up FIELDS, multi-Value fields or Calculated fields in tables.

Calculations should be done in a query.



Good Name:
"Voltage_At_Equipment"

Bad names:
"Voltage_Drop (V)" <<-- has spaces and parentheses. Better would be "VoltageDropV" or "Voltage_Drop_V"
"Voltage_Drop (%)" <<-- has space, Percent sign and parentheses. Better would be "VoltageDropPCT" or "Voltage_Drop_PCT"



Good luck with your project.....

[CarlettoFed]

I am making an example for how to automate what you would like to accomplish but you explained how you calculate the power.
If you give me the formula, the program is ready.