Parameter Query Help

[nightmaregenerator]

I have list of about 20 or so users that keeps growing I have implemented a parameter query to successfully isolate the users activity between two dates. But have been unsuccessful at two things.

I can't seem to figure out how to get the parameter query to isolate single names. I have tried:

Like [What is their First Name?]

but it does not bring up any results.

And is there any way for the Parameter query to run and put the results into a default report, that will look the same for all the users, as it is quite tedious having to update 20 or so queries every week then open all these reports adjust them and then print them.

For now it's running like a normal query but I want to have a drop down box that allows you to select the first name and dates and hit OK so it runs

 

[jzwp22]

I would use an unbound form to supply the name (via a combo box) and the dates (using 2 textboxes). On that form you would have a command button to open the 1 report. In the code of the command button, you can filter what data is fed to the report based on what is on the form. In order to help you further, I would need to see your table structure. I assume that you have a table of user names and a related table of user activity, so you will need to base your 1 report on a query that joins those two tables.

 

[nightmaregenerator]

Here is the database I am currently using.

Otherwise it makes perfect sense.

 

[jzwp22]

You have put a lot of effort into creating the separate queries and reports for each person, but you only really need 1 query and 1 report along with a form to accomplish what you want. I've attached a little example database that I put together to illustrate that approach. I use mm/dd/yyyy format for my dates; sometimes there are issues with dates depending on what you have for your local settings.

More importantly, however, I see that you have some normalization issues that need to be addressed before you go any further with your database. I recommend that you take a look at this site to learn more about normalization. After you have had some time to digest the info in the link I provided, please feel free to come back with any questions.

 

[nightmaregenerator]

From looking at the page you supplied and the database you provided I think I understand the problem with the normalization, just to be sure I know what it the problem is.

Is it the fact that the reference instead of being the primary key from the employee's database it is instead the employees name which can have duplicate values?

 

[jzwp22]

Repeating the driver's name and other details in the weekly payroll table is one example, you only need to bring in the driver's ID as a foreign key.

The other thing that I noticed after my last post is that you have lookups at the table level. Although Access has this capability, it is generally not recommended to use them at the table level buy rather at the form level. See this site for more info. Just some other things that I saw, it is generally not recommended to have special characters or spaces in your field and table names. This may force you to enclose those names in square brackets for some of Access' functions to work correctly. Also, adding square brackets is a lot of extra typing.

To get a better understanding of what your application is designed to do, could you provide a narrative of what you are trying to model?

From what I can tell, you have drivers that make runs and you base their pay on the hours they work and/or the miles they drive? The route can go from/to many places. If a route consists of many locations, that describes a one-to-many relationship which is handled with 2 related tables according to normalization rules, but you have it in 1 table. For example, if a route goes from Sydney to Melbourne and then from Melbourne to Canberra, that actually describes two actions. In your destination table, you have it as 1 record with 3 fields. (from, to, return).

If I were to apply normalization rules, I would have a table that lists just the cities. Then I would join them in a junction table along with the distance between them.

tblCity
-pkCityID primary key, autonumber
-txtCityName

tblCityDistances
-pkCityDistancesID primary key, autonumber
-fkCityID1 foreign key to tblCity
-fkCityID2 foreign key to tblCity
-distance

As already mentioned above, a driver can go from one city then to another etc. on his run. In the scenario of going from Sydney to Melbourne and then Canberra there would be 3 records in the following table to describe that activity:

tblDriverRuns
-pkDriverRunID primary key, autonumber
-fkDriverID foreign key to your driver table
-fkCityID foreign key to tblCity
-longSequence (a field to tell which city was first, second, third etc.)
-dteRun (date of run)

You would then use a query using the tblCityDistances and the tblDriverRuns above to find the total distance traveled.



Another note, calculated values are generally not stored in tables, they are calculated on the fly in queries, forms and reports (but there are some exceptions). I'm not sure if yours maybe one without some additional information about your application.

 

[nightmaregenerator]

I'll try to answer and give an explanation for everything you mentioned.

I knew and do try to avoid the space problem but I do have a bad habit of naming tables and queries with spaces. But I will be more aware of that!

Basically the database does exactly what you explained; we then create a report for that pay week which is Tuesday to Tuesday.

But there is a reason there is only three fields for the destination tables as when I started with the company they had the destination and kms already worked out and work off that.

As for the calculations I use a VB code that when you hit the Calculate Gross and Calculate NETT buttons adds them up and then stores them in the table. The only problem here being that the the tax because it changes depending on circumstances for each driver has to be entered into the first record which runs a formula in the report.

Really I understand all of this can probably done a lot easier in a financial program, but they just don't want one.

Hopefully I addressed everything.

 

[jzwp22]

I don't doubt that there is other software available that can do the job, but Access can do it also and can be changed as the organization changes whereas with purchased software you can be limited.

As for the calculations I use a VB code that when you hit the Calculate Gross and Calculate NETT buttons adds them up and then stores them in the table. The only problem here being that the the tax because it changes depending on circumstances for each driver has to be entered into the first record which runs a formula in the report.

The main issue here is storing the calculated values. I would store the relevant tax info for the driver and bring that and hourly rate etc. into the report and calculate the the Gross and Nett there. If that tax info for a driver can change over time just like hourly rates, the tables will have to be structured accordingly. For example, if you want to track a driver's hourly rate over time, this structure would do it:

tblDriver
-pkDriverID primary key, autonumber
-txtFName
-txtLName

tblDriverRates
-pkDriverRateID primary key, autonumber
-fkDriverID foreign key to tblDriver
-dteEffective (effective date of the rate)
-currHourlyRate

You would use Max(dteEffective) in a query to pull the current rate for each driver. You could do the same for the tax info you mentioned.

 

[nightmaregenerator]

Sorry for no reply, I'm back at school and never have time to post anymore but I have been working on the database to fix relationships and normalization, just on that for the drivers you mentioned currHourlyRate, for this because drivers can take a single, double and road train, for the kms rate would I include that as well in the tblDriversRates under separate headings? Because some drivers get special rates.

 

[jzwp22]

If I understand you correctly, it sounds like a driver can have multiple rates depending on the transport type (single, double, road train). Please correct me if I am wrong. If that is the case, then I would structure the tables as follows:


tblDriver
-pkDriverID primary key, autonumber
-txtFName
-txtLName

tblTransportTypes (single, double, special, etc. 1 record for each type)
-pkTransportTypeID primary key, autonumber
-txtTransportType

tblDriverTransport
-pkDriverTransID primary key, autonumber
-fkDriverID foreign key to tblDriver
-fkTransportTypeID foreign key to tblTransportType
-CurrTransRate



What I had proposed in my earlier post was if you wanted to track a driver's hourly rate over time which would be equivalent to tracking someone getting raises in pay over time. For example, Driver A has an hourly rate of 25 effective Jan 1, 2009 but then he gets a raise in pay to 26 on Jan 1, 2010. Sorry for the confusion. Of course, you can track the hourly rate based on transport type in addition to any raises that the driver may get. That would be handled as follows:

tblDriver
-pkDriverID primary key, autonumber
-txtFName
-txtLName

tblTransportTypes (single, double, special, etc. 1 record for each type)
-pkTransportTypeID primary key, autonumber
-txtTransportType

tblDriverTransport
-pkDriverTransID primary key, autonumber
-fkDriverID foreign key to tblDriver
-fkTransportTypeID foreign key to tblTransportType

tblDriverTransportRates
-pkDriverTransRateID primary key, autonumber
-fkDriverTransID foreign key to tblDriverTransport
-currRate
-dteEffective