Min/max From Secondary Table Without Returning Extra Rows From Primary
Mar 9, 2006
Hi all, don't know if this is possible, but right now I'm running a query and then performing Dmin/Dmax on each record in the recordset and it's running SLOOOOW. Perhaps if I can get it from a query it would be faster.
I have Table-A and Table-B with a one to many relationship. In the Table-A I preferably need to return only one row per record. From Table-B, I need to return the minimum and maximum record that corresponds to the ID in Table-A.
I am familiar with min and max, but it the join methods I've been using either return multiple rows per ID or none. Any way around this?
View Replies
ADVERTISEMENT
Apr 25, 2006
Afternoon all,
ac2003
I have a problem concerning how to display records dependant or depending on the current record.
Basically I have a seperate area within the db that gives you the option to set which record(s) depends on or dependant to another record(s) as primary and secondary
When trying to display this info, it is fine for a direct primary - secondary within my listbox, but the complication occurs when the secondary record could also be a primary record of another record (if that makes sense).
for example. The data is as such,
PrimaryID SecondaryID
10 12
12 13
15 10
So from this data, my list box should show 1 record that this record depends on (15) , and in another list box 2 records than depend on this record (12,13)
I'm wondering if some kind of loop code would do the trick but not sure what angle to attack this at.
I would appreciate it if anyone knows a simple solution to this.
Thanks
Kempes.
View 13 Replies
View Related
Jun 13, 2006
Hi
I have three figures to display in a chart.
The Total Ongoing Calls
The Ongoing Calls in Area 1
The Ongoing Calls in Area 2
Ideally the Total Ongoing Calls should equal the other two but this is never the case due to calls in wrongs areas etc. To display this I have the Total Ongoing Calls shown as an Area Chart on the Primary Axis with the other two figures shown as a Stacked Area Chart on the Secondary Axis.
This way if I manually fix the Primary and Secondary Axis Scale to the same then I can see at a glance the total in both areas and the discrepancy from the Total. Great.
The issue is that the Primary Axis will almost always be slightly higher than the Secondary Axis which means that whenever the Scale changes I would have to manually change the other Axis. Is there any way, programaticallly or otherwise, that I can link the two Axis together so that if one changes up or down the other changes with it.
Thanking everyone I am about to confuse in advance.
JC
View 1 Replies
View Related
Sep 11, 2013
I have an old table from a database with 2000+ rows deleted and it had no primary key (but it had a number column).
I am splitting another old table which relies on the number column of that old table but I am importing it into a new table with an autonumber primary key which the split table will rely on.
Thing is I need the number and the autonumber to match up accross all records
1 has to be 1
2 has to be 2 and so on
is there a way to "fill" in rows with the missing numbers? i.e. if I have 1,2,3 and 2 is missing the method adds a row "2" to the table.
View 2 Replies
View Related
Nov 14, 2013
I want an append query to create a new record in a table and populate that record with fields from a form. Some of these fields are free text, others are from combo boxes.The append query adds vastly too many records (albeit all populated with precisely the same information) - and the number it adds seems to depend on which combo box options I choose.
I have a similar append query which pulls information from earlier records in the table, and combines that with free text and combo boxes on a near-identical form, which works fine and always just adds one row.
View 10 Replies
View Related
Aug 8, 2013
I am having a problem with a Dlookup query. I want to achieve the following - I have 2 different tables
Demography Table - consisting of 2 fields, City and Region
For example
City Region
NYC NAM
The other Table totalflow consists of several field, one of the is flow from.
For example
Flow from
NYC
WAS
SEA
I want to do the following. Lookup the Flow from filed in table totalflow, and compare it to City in demography Table. If City = Flow from, return the value in Region. All are text fields.
I have tried following Query, but it only returns the same value for all rows. (from SQL view)
SELECT DLookUp("[Region]","Demography Table","[City] = '" &[Flow from]& "'") AS test
FROM [Demography Table], flowsize;
View 3 Replies
View Related
Apr 24, 2015
After a lot of reading and consolidating VBA codes for audit trail.How My Audit Trail Works..A module was made for a function named as "Changes" .Then inserted into before update event of a form where I will do the editing of the records.Then I made a table named as Audit.Inside this table I made all the fields I needed such as:
*AuditRecordId[autonumbered]
*FormName[The name of the form for editing]
*Index[The record ID of the record being edited]
*ControlName[The Field being edited]
*DateChanged[Date change was done]
*TimeChanged[Time change was done]
*PriorInfo[for the old value of data being changed]
*NewInfo[For the new value of data changed]
*CurrentUser[The user base on log in form that was set to Global into another module]
*Reason[The reason for changing for future references]
And Here is the Function Code:
Code:
Option Compare Database
Option Explicit
Function Changes()
Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Dim strCtl As String
Dim strReason As String
[code]....
This audit trail function is valid only for one(1) form, due to the limitation of
Code:
Screen.ActiveForm.Controls("SUP ID").Value
where "SUP ID" is the primary key of the record being updated/Change, so if there are Five(5) tables that needs audit trail, there will be also Five(5) forms, as well as Five(5) Function Changes namely; Changes(), Changes1(),Changes2(),etc... because all the table do have their own sets of primary Key.
Is there a shortcut, in such a way that the "rs!Index" will automatically return a value, equivalent to the Primary Key/Record Id of the record being updated/change, given that there are different updating forms for each table to be updated?
View 5 Replies
View Related
Oct 27, 2014
I have an Access database, where one of the tables is near the maximum amount of fields (241 of 255), was just wondering if I could set up a secondary table that would be linked to the original table by a specific field and would store the other fields that would like to add to the table.
View 7 Replies
View Related
Sep 24, 2005
Hi,
I can't seem to get this right...any help much appreciated:
I have a two tables LESSON<PAYMENT (One to Many)
LESSON
Lesson_ID (PK autonumber)
Lesson_Date (Date)
PAYMENT
Payment_ID (PK autonumber)
Lesson_ID (FK)
Payment_Date (Date)
I am maintaining LESSON using a simple form. On creating each new LESSON record, I also wish to insert a row into PAYMENT, using values from the LESSON table - Payment_ID(autonumber), Lesson_ID = LESSON.Lesson_ID, Payment_Date = LESSON.Lesson_Date
What is the best way to do this?
I have tried to add an Event Procedure to 'Before Insert' but I can't seem to get the syntax correct. Also, for this to work, do I need to paint all fields on LESSON form including (hidden) PK?
I have simplified the tables above but they are relevant to what I am trying to do. Any help would be much appreciated- am new to Access...rather frustrating.
Many thanks,
Simon.
PS I have searched through existing messages but can't see one that answers this, I apologise if this has been answered before- just point me to the orig post.
View 2 Replies
View Related
Jul 24, 2012
I have an infopath form, which people in my company can fill out to order parts. The form is linked to an Access database. The primary keys for the database are the Order Number and the Line Number (where the specific part info is in the system). The form also takes info about who entered the order, when they entered it, etc.
My problem is that while the form will be used for only one order, it may be used to order multiple parts. So the infopath form has a repeating section with a table where they can enter multiple line numbers and part names, etc.If I use the form to enter just one part, everything works great. But when I use the repeating section to add multiple parts, the information specific to the repeating section is added to the database but the rest of the information (who entered, when the entered, etc) isn't.
My intial idea was that since the first part is entered correctly and the subsequent parts are missing some information which is already in another line in the database. Maybe if there was a way to pull the information from another line into the lines with missing info within access would solve my problem. So basically automatically populating the rows with matching Order Numbers with the information that is missing.
View 7 Replies
View Related
Jun 26, 2007
I have a problem... I have four tables(but my problem is limited to just two)
The tables are Orders, Customers, Items and OrderedItems
The two more detailed below creates the problem when trying to copy an already existing order to a new since the customers usally order the samethings over and over again.
Orders
IDOrders
IDCustomers
strSalesperson
strWhen
strSent
OrderedItems
IDOrderedItems
IDOrders
IDItems
iNumber
bPacked
So I have made a copy button on the order form. The copy button should do this:
1. First save the old orders IDOrders and IDCustomers (which it does)
2. Create a new order with old data (which doesnt do)
3. Copy all post in OrderedItems with a new IDOrdered using INSERT INTO and using columns
My SQL statement is like follows:
sSQL ="INSERT INTO Orders(IDCustomers, strSalesperson, strWhen, strSent, strComment) VALUES (" & itmpIDCustomers & ",'" & strSalesperson & "', '" & strWhen & "','" & strSent & "');"
But the reply I got is "Could not find output table 'Orders'" but I have checked the names over and over again but it doesnt work.
View 1 Replies
View Related
May 14, 2014
I built an Append Query to take records of 'Leavers' from my Primary Table and add them to a Secondary Table named 'Leavers. This worked perfectly, but on reflection I determined that I needed to append a further column 'Notes' which exists in the Main Table but not in the Secondary Table.I amended the SQL statement as follows, but the query now fails stating that it doesn't recognize the field 'Notes'.
INSERT INTO Leavers ( [Member ID], Surname, [First Name], [Address 1], [Address 2], Town, PostCode, Phone, [E-Mail], Notes )
SELECT [Mail List].[Member ID], [Mail List].Surname, [Mail List].[First Name], [Mail List].[Address 1], [Mail List].[Address 2], [Mail List].Town, [Mail List].PostCode, [Mail List].Phone, [Mail List].[E-Mail], [Mail List].Notes
FROM [Mail List]
WHERE ((([Mail List].Leaving)=True));
Does this mean that one would need to recreate a new Secondary Table to incorporate the additional field? I have attempted to edit the secondary table by merely adding the 'Notes' field but that doesn't seem to be possible.
View 3 Replies
View Related
Dec 4, 2013
I have a DB with 4 tables:
Property (used for look-up values)
Service Type (used for look-up values)
Rate Table (uses the Property Table and Service Type Table and is used as a rate look-up table for the daily service table noted below)
Daily Service
Using the Daily Service table, I need to choose a property, choose a service type that is limited to the property (100's of service types, but a property may only have a limited number), and enter a quantity, and have the Rate Table return the specific cost of the service. I didn't think this was too hard in Access (2013), but I seem to be hitting a wall and spending too much time figuring it out. Now I am behind in my project.
I have been trying to do this in a table format, but have been unsuccessful in limiting the results of the Service Type combo box based on the property chosen.
View 2 Replies
View Related
Jul 19, 2012
I have two tables. The first one is as follows:
MP No (Primary key), Sales, Date Entry, Specs, Email
The second Table is as follows:
MP No, Quantity, Description, DWG, Price
There is a "one-to-many" relationship between the two tables through the MP No field so whenever I go to the first table there is a sub-table for each MP No. Correct?can I create an extra sub table under the sub table already created?
View 10 Replies
View Related
Jul 15, 2015
I have an Access table with data fields however at after the last field column, I have about 15 blank and unselectable or uneditable columns that I don't want there. How I delete these? Reason I want to delete them is because table is a subform on a main form and these extra columns are visible on it and they also cause a scroll bar to shown
View 4 Replies
View Related
Nov 29, 2012
I have two tables containing data from two separate sources. The unique "link" between the two is a JobNumber field. I need to set up a relationship between the two tables using the JobNumber field.
Problem:
In one table, the JobNumber has been input in this format: 12-00345-01
In 2nd table, the JobNumber has been input in this format: 12-00345-1
How can I tie these when the 2nd table is missing the "0"?
View 8 Replies
View Related
Feb 20, 2014
Managed to export a table to csv file. The problem I now have is I need to add the following Text at the start of the file:-
H,LKJ85485524,DE
and the new last line :- T,whatever the record count is,2
Whether this info is added before or after export is not important.
I have even resorted to using VBA to create an Excel spreadsheet and saving to csv.
Thought this had cracked it however I had surplus commas at head and footer where the original csv file had additional columns (ok manually delete these comas) ,then found that the end of each csv line was missing a final comma that is needed by the recipient of the file.
View 4 Replies
View Related
Jan 23, 2006
Hey everyone,
Is there any way that you can do a secondary sort on a field?
Any help would be of great!
Thanks - edawson
View 2 Replies
View Related
Oct 16, 2007
Hi all!
I have a bit of an issue. Im trying to retrieve a row from SQL and insert it into my local DB table.
However, when I try and execute the code, I get an error message. The data had not been inserted into my local DB table.
Here is the code:
strsql = "INSERT INTO EntryDetail_Local SELECT EntryDetails.* FROM EntryDetails where entrykeyid = " & plngentrykeyid & ";"
Set qdftemp = mydb.QueryDefs("qry_Append_EntryDetail_Local")
qdftemp.SQL = strsql
qdftemp.Close
qdftemp.Execute
DoEvents
Any ideas?
Cheers
View 2 Replies
View Related
May 28, 2015
I have a table for a multiple parents linked to a child table. I need to figure out a way to only allow 1 parent to be coded as primary, 1 as secondary, and then the rest as other... I thought about making Primary/Secondary/Other a primary key. But then I can only have 1 other. I would have to make a finite number of parents that could be entered and I want an infinite number.... My end goal is to have a report that only has a primary and second parent on it, but the rest of the parents still exist in the table...
*Child_ID
Parent_First_Name
*Primary/Secondary/Other
View 8 Replies
View Related
Mar 15, 2005
The main form (frmMain) is fed by qryMain. I'd like to put a memo field on a separate form (frmMemoField) to avoid cluttering the main form.
Is there a way to ensure that frmMemoField offers the same record currently displayed on frmMain, please, Experts?
View 2 Replies
View Related
Oct 9, 2006
Hi
I have a main table in my database. For each record in this table there may exist 0, 1 or many records linked to it in a secondary table.
I have created a form for viewing the records in the main table, and now want to link to a second form that displays the records in the secondary table, if indeed any exist for the main record being displayed on form1.
Specifically . .
If I can detect whether secondary records exist for each record in the first, how can I pass a parameter (ie Table1id) to the secondary form so that we can view only the secondary records associated with the main record selected ?
thanks
ajm
View 2 Replies
View Related
Oct 11, 2004
Hello
I have this problem. I've made a table called "producers" and a main form based on this table. I've also made a table called "orders", with several data regarding orders, and a secondary table based on the "orders" table. These tables are linked. So these forms show the orders made by each producers. Now , I try to add the "price" field of each record of the secondary form and then show the result in a text box in the main form, so that I can have a total price amound for each producer.I've tried many things but nothing seems to work. Is this so difficult to achieve? Pls help. Thank you in advance!
View 14 Replies
View Related
Feb 17, 2005
Hello
I have a form that is bound to a table called "date". Date has to fields "date_id" which is a numeric value and "date_description" which is the name of the month.
A secondary form is bound to a table called "Employees" which has fields regarding data about the emploees of a company (eg "surname", name" etc). I use these 2 formes together to select the month of year and show/edit the data of the employees. Everything work perfectly except one last thing. Is it possible that after I edit (or add) a record in the secondary form (table "employees") the secondaty form to be sorted by "surname"? How can I do such a thing? I would be grateful if you give me an example.
Thank you in advance
View 6 Replies
View Related
Aug 8, 2005
Is anyone else experiencing similar problem ?
Basically here's what happen:
1. User need to look up item name, while in a subform. He pressed F5
2. A form showed up, with a ComboBox (which got its values from a query)
3. User chose an item name in the ComboBox, and then pressed the Close button
4. The item name got copied into the subform.
Problem is, for some items/records, step 4 doesn't happen :eek:
When I investigated, turned out that for some records, the ComboBox returned null (instead of the selected record)
This is on a 5800+ records table, on Access 2000.
Here's the code :
[Forms]![005_cashier].[subform_0051_enter_sold_items]!selling_price = Me!item_name.Column(2)
[Forms]![005_cashier].[subform_0051_enter_sold_items]!quantity = 1
The second line always works (always got copied to the subform properly), while the first one only works with certain items/records.
I've examined the offending records, but couldn't imagine how they'd be able to make Access to behave like that.
Any ideas ?
Thanks,
Harry
View 2 Replies
View Related
Jun 18, 2013
what I am trying to do with pretty much create a search query through code. So what's happening exactly is that the user enters a part number and expects to get 2 values: total orders and total items associated with the part number he/she entered. I have 3 tables and 2 of them are related. So I began my creating a query relating table 2 and table 3. I cannot include table 1 in the query.
1) Part Number is input by user
2) Search that Part Number into Table1
3) Take returned Parent Values associated with Part Number and store in Array
4) Modify Array values
5) Search Array values using a For Each loop into the query
6) Take the returned values found in query associated with each parent that was retrieved from the part number (user input) and return that through a table or query.
7) Also return the total rows in a specific column (Order Numbers)
View 10 Replies
View Related
