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 Replies
ADVERTISEMENT
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
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
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
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
Jan 4, 2007
Hi Folks,
First of all i apologise for posting double, but i m compelled. I badly need help. i m not an expert like you guys. I hope someone will surely come to my rescue.
I have a problem with summing values in a query that is based on 5 tables.
In my form i have a text box which shows the total quantity on hand. The formula comes from all 5 table fields. for example:
GOODS RECEIVED GOODS SENT REPAIR RECEIVED REPAIR SENT ISSUES
Trx_Qty Trx_Qty1 Recd_Qty Sent_qty Issued_Qty
The formula works this way:
Expr1: [trx_qty]+[recd_qty]-[Trx_Qty1]-[Sent_qty]-[Issued_Qty]
When the records are limited to one row only then all works as desired, but as soon as i add more records in REPAIR RECEIVED Table and REPAIR SENT Table i get multiple rows in the form. Also the sum is not correct. For example:
GOODS RECEIVED GOODS SENT REPAIR RECEIVED REPAIR SENT ISSUES
Trx_Qty Trx_Qty1 Recd_Qty Sent_qty Issued_Qty
40 10 5 10 20
In the above example the formula gives following result
Expr1: 40 + 5 – 10 – 10 – 20 = 5
Expr1 = 5 (This is quantity on hand)
Bu now when I add more rows to other tables I get multiple rows and incorrect sum. Example
GOODS RECEIVED GOODS SENT REPAIR RECEIVED REPAIR SENT ISSUES
Trx_Qty Trx_Qty1 Recd_Qty Sent_qty Issued_Qty
40 10 5 10 20
5
I m attaching the database for you to have a look at it and come up with the solution.
Please help me I m badly stuck
I tried using group by and sum functions but no use
[Sumoftrx_qty]+[recd_qty]-[Trx_Qty2]-[Sent_qty]-[Issued_Qty]
Please Help me out of this mess. It will be highly appreciated
i will b gr8ful
Thanx once again for investing ur time in reading
Note: Expr1 formula may be different in database as quoted here on this forum due to trial and run.
Regards
Darno
View 5 Replies
View Related
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
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
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
