Access :: Field Totals, Spining My Head

Still Can't Get My Head Around Many-to-many

Mar 7, 2005

Database: Invoices....

need a billing address, and a delivery address.

I have a table of addresses (of both kinds, of course), a table of invoices, and a linking table for the many-to-many, because each invoice has a delivery address, but may have a separate billing address.

in my linking table i have PKs from each table, but i also need to categorize each relationship as either a billing or a delivery address, so i added another number (1 = delivery, 2 = billing).

i've got a main query that populates general invoice Detail from just the main table (data source for main form)

the main form has two subforms, each one being populated by its own query with AddressType set to either 1 or 2, and form/subform relationships on the main table's PK.

i can't get my forms to update the linking table directly.

is this possible?

am i doing something wrong?

am i missing something obvious?

it would be nice to be able to search for the word "many".......

View 2 Replies View Related

This One Really Breaking My Head!

Mar 23, 2006

Hello all,
I need your help! Iam breaking my head for a day almost to solve this problem.

This is a timesheet application in MS Access in which I have a MS Access table with the following columns:

1. PersonID
2. SkillID
3. EntryDate
4. MON
5. TUE
6. WED
7. THU
8. FRI

The work hours for a person is entered once a week, at the end of the week. The person's SkillID can change but not in the same week. For ex a person scheduled as a Welder has to work as Welder for that week.

So, I need to find now all the PersonIDs with more than one SkillID in a week and flag them as errors.

Pls check the attached image. The first entry with EntryDate as "06-May-05" and Person_Code as "MK0259" repeating with 3 different Skill Codes. Then this is a problem which I want to hight light. Hope I explained clearly.

Can someone help please? Desparate!

Thanks in adv.
sgmuser!

View 2 Replies View Related

Head Fudge Over Combo Box

Oct 25, 2006

Hi, I think there is a very simple answer to this but it is giving me brain ache!!

I have a form which displays or edits 'projects', this form is bound to the projects table

each project record is assigned a location, that location is selected from a combo box.

When the Location is selected in the combo box it stores the 'Numerical' value to the [location] field in the projects table.

my question is this:
how do i create a query as a base for a report that will list projects by location but give the location name (as it appears in the combo) rather than its numerical value as stored in the table?

I keep getting a 'type mismatch' error

I just cant get my head around it!!

View 5 Replies View Related

Complex Query- Over My Head

Sep 4, 2007

ok guys-
I am struggling here. I've been trying to figure this out, got a small portion of it completed- run the query and I run out of Temp disk space- so it never gives results.

Basically- I have all the property data in one table. The goal is to find the how a property listed for sale compares to all those properties similar to it have sold for in the past x amount of months. I want to compare it to all the other houses in the same area (these are Sold status). The Sold houses have to have the same number of levels (stories), be in the same area (Area) of town, have sold in the last x amount of months (eventually I will compare the houses that have sold int eh past 3, 6, 9, and 12 month time frames), and have a square footage range of +/- 15%.

For example-
There's a house for sale (Active status) for $200,000. It is 2,000 square feet, built in 1995, has 1 level, and is in Area 510. I want to compare it to all the other houses in the same area, similar to it- which means, +/- 15% of the Square Footage (1700-2300), in Areas 510, has only 1 level, and was built in +/- 10 years (1985-2005). I then need to take the average $/sqft of THOSE houses, and compare it to my one active house. Let's say there's 10 houses matching that description, and the Avg $/sf comes out to $240,000 on average for those 10 houses.

So- the final result will be:
123 Main St Active 2,000sf 1995 1 (level) 510 (Area) $200,000 $240,000
And then a calculation of Asking Price divided by the Avg Sold Price of Similar houses which, = 83.3%

All the data/fields are in one table. I have been trying to work nested queries- but, I've only got 2 or 3 specs in, and it runs out of space on my temp disc. So, I believe I'm building it wrong. Can someone please help me out to figure out how to accomplish this? I've got more to add onto it- but- I think if I can get this part figured out, I can do the rest on my own...
Thanks a ton guys!

View 5 Replies View Related

Report/Query Question. In Over My Head :-(

Dec 20, 2006

Hi all, i am new to the forum and i hope somebody can help me..

Apologises in advance if this post is out of place..but i really do need help!

I have created a database in work that will be used to track training history for employees. It consists of two main tables:

Employees: Name, Start date, Department, Manager
Class: Class name, Class Date, Duration, Attendee1, Attendee2, Attendee3 upto Attendee 24 (Max amount of trainees in a class)

All is working ok, and i can populate the tables fine using forms. I can now view class information, and see who attended what class.

However i need to be able to view an individual employees training history. ie

Joe bloggs has attended the following classes:

Class 1, 12/01/06, 8 hours
Class 2, 14/01/06 8 hours

I think i need to create a form which will allow me to enter an employee name. This name will then be checked against attendee1, attendee2 etc in the class table and it will then flag back any classes that the employee has attended

I hope i have explained this ok, but i really do need some advice on how i can go about doing this..:confused:

I have very quickly realised that there is a wealth of knowledge on this site, so i am hoping that you guys may be able to help..

I have been working on this for a few weeks now, and books dont seem to have the answers!!!

Many thanks in advance

Noel

View 11 Replies View Related

A Head Scratcher: Output Into A Current File

Sep 8, 2005

Hi
At the moment I am trying to send data ( a query) to a CURRENT Excel file, I do not want it to replace the current file but instead just update the cells of that file to the new values calculated in Access.
Is it possible to do this?

Cheers all
Bikeboardsurf
:confused: :eek: :( :mad: :confused:

View 2 Replies View Related

Changing Listbox Column Head Titles?

Mar 23, 2006

Hi there

I have a listbox and all is well - the only problem is that the column heads are a little 'ugly' - my column names are usually something like 'catagoryName', 'productType' and 'price' instead of looking pretty, like 'Catagory Name' and 'Product Type' and 'Price' respectively.

Is there any way to edit/format the title/column heads without having to change all the tables and queries etc, so they look a littler prettier?

Many thanks

Edd

View 9 Replies View Related

Calculate Totals In A Field

Dec 6, 2004

I have 4 fields in my table and form (Towing Charges) (Storage Charges) (Other Charges) and (Total Charges). I need to have Towing Charges, Storage Charges & Other Charges to calculate and populate in my Total Charges field. I tried a script in the control source of the field, but its not working. I probably have the script wrong.

Thanks Everbody

View 3 Replies View Related

Arrg! Totals Work In One Field And Not Another

Feb 2, 2006

Hi all. I have three tables in my dbase related by user ID. All of the fields from the main table (client) are on the "Client" form. This table/form contains a number field called "Total Hours". There is a subform (Service Items) on the "Client" form which contains info from the "Service Items" table. The two are related by Customer ID. I can get a total of the number of hours spent on each client. It shows as a field in the footer of the "Service Item" form like so:=Sum([LaborHours]). Fine that part works.

What I dont know how to do is add another field to that footer that shows the result of the "Total Hours" from the Client table minus the Sum of Labor Hours from the Service Item table.

Basically a client would start with a set number of hours (Total Hours) and count down (minus the sum of LaborHours). How can I show the remaining hours?:confused:

It seemed like it would be easy but dang! Thanks for any help you can give...

View 2 Replies View Related

Totals Query/sums/grand Totals

Sep 4, 2007

Hey all! This is my first post. Been searching through the net all day trying to find a solution to this problem. Basically i have a table that looks like this (regular text is what i have and bolded text is what I need:Name Date Qty MOBrad 12/12/2007 23323 4423John 12/11/2007 3445 4432 John 12/11/2007 344 4432 John 12/11/2007 45 4432 John 12/11/2007 44 4432 John 12/11/2007 3445 4432 Grand Total: (Qty)And then I'd like to be able to carry this over and display a grand total at the bottom of every page of a report that I would need to generate. Our company produces forms and we sometimes have 60 - 70 people working on a single job. We want to see their hours individually but we would also like to see a grand sum of all their hours. If someone could help with this or needs more info let me know. Thanks for all your help!

View 14 Replies View Related

Forms :: Getting Totals From A Subform Into A Field On Main Form

Feb 13, 2015

I have a music database. It's somewhat over the top in terms of the information stored in it, but I like it. My issue is with getting the total number of tracks, as well as the total duration, for a given album (and for each disc, but if I can solve one that should also solve the other).

I have a lot of tables and forms, but for this problem I only need to deal with two of each. They are the tables for albums, the table for tracks, and their associated forms.

In my album table I have information such as album title, artist, release date, and album length. The tracks table contains information on each song - track title, track number, parent album, and track length.

My main form is for the album itself, and contains a subform for the tracks on said album. They correspond to the relevant tables.

What I want to do is obtain the value of the album length by calculating the sum of the track lengths for that album, and the number of tracks. Easy enough - Sum(TrackLength) and Count(TrackID) respectively, both of which can be stored in the footer of the subform.

My problem is that, rather than simply copying those values into text boxes on the main form, I want to copy them into actual fields in the album table. Furthermore, I want them to update as I add or edit the track lengths in the subform.

View 4 Replies View Related

Sum Time Of Specific Field Giving Wrong Totals

Jun 13, 2013

I'm trying to create a query that will sum the total time of a specific field. It seems to be doing it, however the value is off by by a couple minutes on all my examples.

Table

- BusArrivalTime
- BusDepartTime

Query

In my Query I'm making a new field like this. It correctly figures out the difference.

BusWaitTime: DateDiff("n",[BusArrivalTime],[BusDepartTime])

Report

Next I'm displaying that Query information inside of a Report by putting this in the Text Box on the Report. It correctly displays the time in the Hours/Minutes format.

=[BusWaitTime]60 & Format([BusWaitTime] Mod 60,":00")

Lastly, I'm using another Text Box on the Report to Sum the Grand Total of the Wait Time for all my records. Here is what Im putting in the Control Source

=Sum([BusWaitTime])60 & Format([BusWaitTime] Mod 60,":00")

...and it's summing my records, but the value is off by a few minutes and I cant figure out why.

In my example, I have 3 records with times of 3:14, 1:35, and 3:20. It should be totaling a figure of 8:09 but its coming to 8:14 instead.

View 7 Replies View Related

Queries :: Count A Field Multiple Times (sub Totals And A Total)

Jul 18, 2013

I have 2 tables (person / merit) currently the merti table has 4 fields (MeritID, PersonID, Issuer, Type) I'm trying to count the the Type field 7 times, once of each type (MC/MI/MN/MP/MS/MV) and once to count a grand total.

I want to produce a query to use in a mail merge that will list the total for each merit type and one grand total.

I've been trying a few different things including sub queries but I've not managed to get the query to produce a single record that counts each individual type.

View 4 Replies View Related

Calculating Account Revenue For Sales Reps - Group On Totals Field

Jul 9, 2013

I have a report that is calculating account revenue for my sales reps. I am able to generate the report, group by the sales rep and then total their total account revenue. Now I want to group on the total field that I just calculated. How do I do that?

View 14 Replies View Related

Table Column Name Properties/Combo Box Head Properties

Dec 8, 2006

Hi, I would like to change the properties of either the column heads in a combo-box or the column names for tables. I don't think there's any way to adjust the column head properties, and I'm doubtful there's a way to change the column caption properties.

Some of my column titles are long, and I'd like to be able to word wrap them essentially. Anyone know if this is possible? Thanks.

View 5 Replies View Related

Totals

Jul 27, 2007

Hi there, I am fairly new to access and am having trouble doing what I need to do. I have a table that contains 2 number fields, 14 yes/no fields, and one text field. I want to be able to display the total number of yes's per field, and the total count of each individual value for the number and text fields.

I was able to make individual queries that count all of these, however I'm unsure how I can make a query that will do it all together, or how to put them all together.
I may be doing this wrong, or there may be a better way of doing this.

Any help would be greatly appreciated!
Thanks very much!

View 10 Replies View Related

Totals

Nov 10, 2006

is there a way that i can get a total from a number on a form and a number on a sub-form (I want to add the numbers from the form and sub-form and have the total appear in a text box on the form)

View 3 Replies View Related

Help With Totals

Mar 7, 2007

i want a field to total up the amount of times a person apears in another table. does it involve using the greek version of E?

View 2 Replies View Related

Calculating Totals

Jun 28, 2005

I am designing a new database for our life and disability department and they have several different policies where the base life insurance reduces at certain ages.

Please take a look at the scenarios listed below and give me any suggestions and tips on the best way to get this done. If you have any.

Below are the 12 scenarios:

1.) Reduces 75% at Age 70

2.) Age 65 to 70 the life amount is $9,000 and Age 70 and over the life amount is $6,000

3.) Reduces at age 65 by 92%, age 66 by 84.64%, age 67 by 77.869%, age 68 by 71.639%, age 69 by 65.908% and age 70 and over by 40%.

4.) On the day following the 70th birthday, the life amount will reduce to 70%. On the day following the 75th birthday the life amount will reduce to 40% of the 70th birthday reduced amount.

5.) On the Policy Anniversary Date following the 70th birthday - reduces 50%

6.) On the Premium Due Date following the 70th birthday, the life amount will reduce to 70%. On the Premium Due Date following the 75th birthday, life amount will reduce to 40% of the amount of the 70th birthday reduced amt.

7.) Age 65 life amount reduces to 65%. Age 70 life amount reduces to 35%. Age 75 life amount reduces to 25%.

8.) Terminates on the first of the month following the 65th birthday.

9.) Terminates Age 70

10.) Terminates on date of retirement or 1st or month following 70th birthday

11.) Terminates Age 65

12.) Never Reduces

Thanks for your time, Brian

View 2 Replies View Related

Totals On The Form

Aug 8, 2005

Hi,

I'm trying to get a total which runs off of a query in a listbox (disguised as a textbox) to always show 1 decimal place. Right now, it'll show one decimal place only if the number is rational. If the number is whole, it doesn't show any.

E.g. For 8.5, it'll show 8.5
For 8, it'll show 8, not 8.0

I want it to show 8.0

Any help would be much appreciated.

View 5 Replies View Related

Help With Totals In SubForm Pls.

Sep 12, 2005

folks,
I have two simple tables in a database. One Table is the entry table and the other is the totals. I then created a Form and a subform from the two tables. What I would like is to maintain running totals in the subform as entries are made in the entry table. Attached is the Database. Please help.

View 2 Replies View Related

Calculatinng Totals

Feb 22, 2005

Hi i have two problems.

I am creating a football dream team database with two tables:

Team table - Team ID, Team Name, Player 1, Player 2, Player 3, Player 4, Player 5, Player 6, Player 7, Player 8, Player 9, Player 10, Player 11 and Points Total,

All the player columns are lookup functions which are linked to player table

players table - Player ID, PLayer Name, Position, Club, Value, Points

My problem is i cannot figure out how to calculate the points total in the team table from inputting the points into the player table.

I also would like to know how to create a 'total' column by adding together other columns in a table.

Thanks in advance

View 10 Replies View Related

Totals In A Query

Jun 23, 2005

I have a table. The table stores a workers name and information such as Primary Station Verified.
The Primary Station Verified is a yes/no checkbox. It also has the dates that each worker is verified.

I want to do a query that will show me the total number of people and out of that total number of
people how many are verified. Then I will turn that data into a graph.

How do I do the Query to show me (example) There are 50 people and (example) 25 of them are
verified? I know how to do the query to show all of the people and who are verified but I just want
the numbers.

View 1 Replies View Related

Totals Query

Jul 6, 2005

I'm needing help with a totals query. I have a table with a Department field, a charge field, and a charge code field.

I'm running a query that first groups by the department, then uses the max function to give me the highest charge within that department. For the third field of the query, I want to return the charge code that aligns with the highest charge, unfortunately none of the general totals seem to work and I can't figure out an expression to work either

View 1 Replies View Related

Totals By Person

Aug 25, 2005

I have a table where 4 different people enter their amount of work sheets they complete each day via a form. Each person could have a different total each day. Example:

AJ=5
CH=10
MH=7
CA=3

Each person will have a entry for each work day of the month.

I am creating a query that needs to show each persons total for a month. I know how to get the month but how can I total each persons page count all together and then separately? Example:

All four combined total page count

AJ Page count individually
CH page count individually
MH page count individually
Ca page count individually

Thanks for all help

View 6 Replies View Related