subform update

[helpaccess]

When in the form I have the code box highlighted and then I cliked on change to and changed to a combo box. Now I cliked on the properites where do I go from here.

 

[Scooterbug]

You need to give it a row source. (It would be the table that stores the data you want to display. TBL_Travel Team code i believe). I would also STRONGLY suggest that you add a primary key to that table. You include the rows that you need. (Code (I would change that name, CODE is a reserved word),subcode and location). Once a Code is selected, you can then reference the columns. Since the form is a subform, you would do it this way:

forms!FormName.Form.ControlName.Column(x)

 

[helpaccess]

Ok here is what I have for the row source: SELECT [TBL_Travel Team code].[Main Code], [TBL_Travel Team code].[TT Name (Location)] FROM [TBL_Travel Team code];

But when I go back to the form only the Main code is there. I change the code to main code.

Here is what I have for the subcode. ELECT [TBL_Travel Team code].Subcode FROM [TBL_Travel Team code]; But when I go to the subcode I see every entry and I only won't to see it once.

Plus when I go to make the Main code my prim key it won't let mw save it. View attachment combind travel team1.zip

I have attached the data base with no links.

Again thanks for your help.

 

[Scooterbug]

Ok here is what I have for the row source: SELECT [TBL_Travel Team code].[Main Code], [TBL_Travel Team code].[TT Name (Location)] FROM [TBL_Travel Team code];

But when I go back to the form only the Main code is there. I change the code to main code.

Here is what I have for the subcode. ELECT [TBL_Travel Team code].Subcode FROM [TBL_Travel Team code]; But when I go to the subcode I see every entry and I only won't to see it once.

Plus when I go to make the Main code my prim key it won't let mw save it. View attachment 29373

I have attached the data base with no links.

Again thanks for your help.

*takes a deep breath* The reason why you cant make Code the primary key is because it's not UNIQUE. A primary Key has to be unique. Add another field, call it TeamCodeID and make it an autonumber.

Things you should REALLY change:
1. You have used RESERVED words as field names (Code for example).
2. You have special characters in some field names. You should take out any spaces as well.

Also, you should fix your naming conventions. Naming a form the same name as a query can cause problems for example.

What I attempted to do:
1. Added TeamCodeID (Autonumber, Primary Key) field to TBL_Travel Team code. (Successful)
2. Added a TeamCodeID field to TBL_Expense Tracking detail. (Successful)
3. Added the TeamCodeID number to TBL_Expense Tracking detail. I had to run a select query where I counted up the instances of Code and Subcode and then run an update query based on Code and Subcode (Successful)
4. Changed the control source for code to TeamCodeID. Created a combo box based off of that. (Sucessful)
5. Added two text boxes with a control source equal to the columns of the combo box from above.

 

[helpaccess]

query question.

How do I take two queries (QRY_Incentive Bouns Pay) and (QRY_Expense tracking) combind into one query to show only the expense. will need to to see on line for each person per travel team.

 

[Scooterbug]

I think you need to rethink your table design. It will make things much, much easier when it comes to getting the data that you want. From what I saw, let me see if my assumptions are correct.

You are trying to track the expenses of an Employee. These expenses are generated per trip. Each trip has the following: StartDate, EndDate, MilesTravelled, OtHours, HourlyRate. Each trip can have one or more modes of travel that have a cost associated with it. Air Travel or Vehicle Travel. Based off of that, I would have the following tables:

tblEmployee
EmployeeID (Primary Key)
TeamCodeID (Foreign Key - Links to Team Data)
EmployeeName
Other Employee Data

tblTripDetails
TripID (Primary Key)
StartDate
EndDate
OtHours

tblTripParticipants
TripParticipantID (Primary Key)
TripID (Foreign Key)
EmployeeID (Foreign Key)
(This table allows it so multiple employees can be assigned to the same trip)

tblTravelDetails
TravelDetailID (Primary Key)
TripID (Foreign Key)
TravelMode (Air or Ground)
DistanceTravelled
TravelCost (Airfare Cost or Vehicle Rental)

tblExpenseDetail
ExpenseDetailID (Primary Key)
TripID (Foreign Key)
ExpenseDesc (Description of Expense - Meals, Entertainment, etc)
ExpenseCost (Cost of Expense)

Having the tables setup this way, it would be a snap to setup forms to enter data based on Employee and Trip info. Generating Expense reports would also be a snap to design.


as for this question:

How do I take two queries (QRY_Incentive Bouns Pay) and (QRY_Expense tracking) combind into one query to show only the expense. will need to to see on line for each person per travel team.

I dont see any field in the Incentive table that relates to an expense. What field are you trying to include as an expense?

 

[helpaccess]

Will I have to start over? On some of my queries I have formulas. How do I get them too a table? Yes I am tracking expense by adjuster by travel team. They could have multipal teams per year.

Here are the ones that are formulas in the two queries

Expense:

# days
# weeks
Meals/day
weekly salaries
ot cost
hotel cost
mileage rate

on the incentive pay

OT hours
bouns amount
OT adjustment owed
final TT pay

I would like to see all the expense and the final TT pay in one

 

[Scooterbug]

Will I have to start over?

I dunno about having to start from scratch, but restructuring your tables should be the first priority. Me, personally, I would create a new database, construct the tables properly, then link the tables from your old db into the new one. I would then use Append queries to get the data into their proper tables.

On some of my queries I have formulas. How do I get them too a table?

As long as you know what the forumulas are (and if you save a copy of the current db) you can recreate them in the new database.

Yes I am tracking expense by adjuster by travel team. They could have multipal teams per year.

What can have multiple teams per year?

Here are the ones that are formulas in the two queries

Expense:

# days
# weeks
Meals/day
weekly salaries
ot cost
hotel cost
mileage rate

on the incentive pay

OT hours
bouns amount
OT adjustment owed
final TT pay

I would like to see all the expense and the final TT pay in one

All of the calculations you are looking for can be calculated with the different table structures I mentioned before. A good database has a good table structure as a foundation. With a good foundation, you can pull the data you are looking for with ease.

 

[helpaccess]

Question,

I am creating the new data base with the tables you suggested. I have some question. On the employee table. All the information is stored in a master table that is linked. Should I create my own? Also how do I create the foreign key and link it to the team data? Do I need to create a team data table also.

 

[Scooterbug]

Question,

I am creating the new data base with the tables you suggested. I have some question. On the employee table. All the information is stored in a master table that is linked. Should I create my own?

I wouldn't. You would have to continueously update it if you did that. As long as there is a primary key so that you can identify each employee you should be good. If you only need certain data from those linked table(s), you can always just create a query to get that data.

Also how do I create the foreign key and link it to the team data? Do I need to create a team data table also.

You dont really "Create" a foreign key. The foreign key is actually the primary key for the row of data you wish to reference. So, for example, if I had a order and wanted to store who placed the order, I would record the primary key of the customer in the same record as the order.
As for creating a team data table, it depends. From the sound of things, I would. But, you also have to consider this: Can the Team Members change over time and will you want to save the history of who has been on what team? If the answer is yes, then you need a table to store team information and another one to record who and when they were on what team.