Query result based upon two field conditions

[diane802]

It does not - table #1 shows City and Function separately. hence why I was thinking potentially creating a query to combine the two into one field. And then linking it off of that into a second query.



but then this second query becomes read only.

[MAF4Fam6]

My recommendation as another option would be to build a separate table which holds all combinations of Business Types:
Table name: tblBusinessTypes
Table fields:
chrBusinessType---Primary Key
chrCity
chrFunction
All fields need to be set to Required: Yes, for mandatory fill.
Then you can use a combo box on an Employee's form to insert the Business Type from the tblBusinessTypes table into let's say a chrBusinessType field name. This way, you can do a lookup from the tblBusinessTypes table using both PK/FKs.

-RC

[diane802]

Business can't be a primary key - multiple city/location combinations can have the same business.

[MAF4Fam6]

Thanks.
Corrected last post. Field name with primary key would be chrBusinessType which is the combination of other two fields.
Thanks.

-RC

[diane802]

i am new to combo boxes - so just want to make sure I understand

Table #1: main data table that is being filled out for all individuals (through a Form)

Table #2: Referential table called tblBusiness Types
includes three fields:
- City
- Function
- combo of City Function (so say NewYorkSales)

In the Form, I do a combo box showing City, Function, and the combo?

[MAF4Fam6]

In Table#1, you will need to add a field name called something like chrBusinessType which relates to a record by primary key in Table#2.
In your member's form, you will create a combo box on your chrBusinessType field which displays only the combo..Please ensure your combo box requires a value only from the list of values from Table#2.

-RC

[diane802]

This works! It also makes my form not read only now and I can edit it.

So here's another follow up question (apologies i am new to this)...

What if I also need City and another field (Category), and combine that to bring in information from a third Table.

Is my only option to do the same combo box but to have City and Category together? This then makes it repetitive to the user to have to select City again, instead of just the category.

[diane802]

RC - i have another troubleshoot for you ..

In my form query, i bring in another query, that basically converts a currency from Table #1, into USD using a separate table that has Currency and the FX Rate. I then bring in this USD number into my form query.

When I do that, this form query becomes read-only.

Should I be doing this currency conversion in another manner?

[MAF4Fam6]

In response to your 1131am post...
I could recommend the following option:
Add the following field names to Table#2:
chrCategory (Required Entry: Yes), chrCityCategory (Required Entry: Yes)
Then add the following field to Table#1:
chrCityCategory
Then add the following field to member's data entry form:
chrCityCategory
This way, you can have Access dynamically update the chrCityCategory field in your form immediately after updating the BusinessType field via your first combo box.

-RC

[MAF4Fam6]

I highly recommend for you to achieve the conversion in a different manner.
You can accomplish the conversion in your original query by adding a customized field name along with FX. Then add the customized field name to your data entry form.

-RC

[Rainlover]

Diane,

It would appear that the structure of your Tables may not be correct for what you wish to achieve.

Could you post your structure here and then we could help a little better.

[diane802]

I wrote the below message before trying to attach the db, since the db is access 2007 - the extension is accdb and it says this is an invalid file type for upload - what should I do?

first off, i just want to thank everyone for posting on this forum and helping me with this database - its really appreciated and I am learning so much!

i built a dummy database using all of the guidance everyone has provided, so I now have the below working in it:
- the lookups are working dynamically to find the Business, and separately the Function using combo boxes.

What I don't have working yet, is I am not sure how to:
- convert the "Revenue" field from Local currency over to USD, without creating a query - bc the query causes the form not to be dynamic once it is added in (as you will see from what I have attached).
- how to I break out Dept, City, and Function so that I can report on it as separate fields.
- how do I show Dept, City, and Function in the form. Right now, the combo box only shows Dept as its the first column of data.
- is it possible for Dept to only be selected once, instead of twice right now - as that makes the user interface a bit repetitive then?

Thanks!

[RuralGuy]

Hmm...may need to brush up on those attachment skills.

[RuralGuy]

Diane,
Compact and Repair the db first, then ZIP it up, then attach it.

[diane802]

Thanks RG - db attached with original note below:

first off, i just want to thank everyone for posting on this forum and helping me with this database - its really appreciated and I am learning so much!

i built a dummy database using all of the guidance everyone has provided, so I now have the below working in it:
- the lookups are working dynamically to find the Business, and separately the Function using combo boxes.

What I don't have working yet, is I am not sure how to:
- convert the "Revenue" field from Local currency over to USD, without creating a query - bc the query causes the form not to be dynamic once it is added in (as you will see from what I have attached).
- how to I break out Dept, City, and Function so that I can report on it as separate fields.
- how do I show Dept, City, and Function in the form. Right now, the combo box only shows Dept as its the first column of data.
- is it possible for Dept to only be selected once, instead of twice right now - as that makes the user interface a bit repetitive then?

Thanks!