Date Related Query Across Tables

Jun 13, 2006

Hi, Ive got another query I need to do which I just dont know where to start from.
I have two tables, membersdata and bankdata. These tables are linked in a relationship thou the field 'description' in both tables.
What I want to do is be able to input a date and have the fields I require from both tables displayed. the dates are stored in the bankdetail table. I have made a query that allows me to input the dates in the bankdata table and that works but I need the membersdata to be shown on this query also.

How can I get the query to show up all the data i requre from both tables?

This query will give me the information of people that have paid us in the dates selected. I also need to revese trhe query so it shows the people who have not paid. so Im basically showing the records with a null value in the description field.

This ones totally caved in my brain this time of the morning! Any help would be fantastic!

Cheers
Phill

View Replies

Date Related Query- Help Please!

Dec 1, 2006

Hi there,

I have a table which shows telemarketing that has been completed on a weekly basis where the caller places the date they called in one field and a followup date (if needed) into another field if a followup call needs to take place.

I want to create a query which will show me all calls that have got a followup date put inplace but the followup call has not been made.

E.g i call mum on 29/11/06 i put down followup on 30/11/06. I call on the 30/11/06 so the followup has been completed but need to follow up again on the 01/12/06 but i do not complete this one.

Any ideas???

Thanks

View 3 Replies View Related

Need Help For Date Related Query

Jun 11, 2007

Dear all, i have the following codes:

enter_date = Format(txt_date, "dd/mm/yyyy")

Set db = CurrentDb()

Set rs = db.OpenRecordset("select * from [entered_date] where Date = # " & enter_date & " # ")
If Not rs.RecordCount = 0 Then
DoCmd.Beep
Msgbox "Selected Date's report already been created", vbInformation + vbOKOnly, "Error"
Exit Sub
End If

rs.Close
Set rs = Nothing

suppose,i have one record in the table [entered_date] which matches the user input - enter_date = "08/06/2007",but whne i run the above coding,i get the rs.recordcount = 0. it suppose to be rs.recordcount = 1.
can anybody pls help? thanks

View 3 Replies View Related

AND Query, Across Related Tables

Jan 12, 2007

Hello, I apologize in advance if there have been lots of questions like this, but the search won't let me use AND as a search term!

I have two related 1 to many tables. The parent table contains clients, and the child table contains categories, each client can have many categories. (i originally intended to have the categories be columns in the client table, in which case what i want to do is easy, however, then it becomes a nightmare when the user wants to add a new category hence the related situation described.)

Is there an easy way to find all the clients who have both category 7 and category 10? I can do it writing a query to find all the 7s, then another to find all the 10s, and a third to find those which have both. I am hoping there is an easier way, as i need to give the user a way to search via categories in whatever combination they fancy. The OR's i can do easily it's the AND's that are causing the problem.

The only idea i have at the moment is to make a temp table with the the clientid, and a long field holding each of the category ids, seperated by commas, and then searching using like "*7*" and like "*10*".

Does anyone have any better ideas, i'm hoping i'm missing something really obvious......

Thanks, Anna

View 1 Replies View Related

Lookup Query For Related Tables

Sep 9, 2005

I have a table of 'things' and two related tables, type and subtype. Each thing has a type and a subtype. The types table contains simply Index (autonumber) and Type (text). The Subtype table Index (autonumber), type (number) and subtype (text), where index and type form the primary key and type is a foreign key to the types table. Thus each type has its own sub-set of subtypes.
In the design of the Things table I have set the lookup for Type and Subtype to be a dropdown, but of course the subtype drop-down shows all subtypes, not just valid ones. If I select a subtype that is incorrect for the selected type then of course I get an error, but it would be nice to set the query in the subtype lookup's Row Source to just display the valid subtypes. At the moment it just says "SELECT index, type FROM subtype" How do I put in a WHERE clause that references the Type field for the current row: "SELECT index, type FROM subtype WHERE type=<type selected in current row>"?

View 3 Replies View Related

General :: Parameter Query / Report With Related Tables

Jun 13, 2013

I have a parameter query for looking up a specific lot number in my database. The lot number table has 4 related material tables that deal with material issued, reworked, and rejected for that lot number. They lot number table is in a one to many relationship with the related tables.

I have a report based on this parameter query with 4 subreports. I want the report to display the lot number data and sub reports to display the related data in the material tables (all linked by the lot number defined in the parameter)

View 1 Replies View Related

Tables :: Developing Related Multi Level Child Parent Tables?

Jan 14, 2013

I have created a table that acts as a header for my data and a second table that acts as line item data. What I need to do now is add a second child table that uses the line item data as its parent table and stores associated line items for each record. Is this possible?

This is a skeleton view of what I'm going for:

Master Table:

tlbAuditReportHeader
- AuditDate
- AuditArea
- Auditor

Sub Table:

tblDiscrepancy
- Discrepancy
- CorrectiveAction
- ActualCompletionDate
- VerifiedDate
- Notes

Sub table to Sub Table

tblFollowUp
- FollowUpDate
- AssignedTo
- SpokeWith
- EstimatedCompletionDate
- Notes

Sometimes tasks change hands or are pushed back depending on work load. It would be nice to be able to track something like this.

View 1 Replies View Related

Moving Data From 2 Related Tables To 2 New Tables As An Archive

Apr 3, 2007

I have a form displaying the 11 fields of the parent/primary table using a selection from a combo box. I am using queries and vba code modules respective to form, combo box and command buttons. I have initial code that uses the two fields from the combo box selection to append same to a new parent/primary archive table. I now want to add to the append SQL the remaining fields to the parent/primary archive table. When I add the second sql string for the remaining fields to the same procedure and execute I keep getting 'null in primary key'. If I copy the primary record and paste same into the archive table it works.

Private Sub Command26_Click()
On Error GoTo Err_Archive_Primary_Click

Dim strSQL As String
Dim strSQL2 As String

strSQL = "INSERT INTO ARC_289325045 ([Survey Point ID], [Survey Area Detail], [Date On Site]) " & _
"VALUES ('" & Me.cboAreaDetailDate.Column(0) & "','" & Me.cboAreaDetailDate.Column(1) & "'," & _
"#" & Me.cboAreaDetailDate.Column(2) & "#)"

CurrentDb.Execute strSQL, dbFailOnError

'strSQL2 = "INSERT INTO ARC_289325045 (RecordID, UnitID, UserName, [TimeStamp], [Survey Point - Area], Measurement, NewArea, [EXIT Form] ) " & _
'"SELECT FORM_ID_289325045.RecordID, FORM_ID_289325045.UnitID, FORM_ID_289325045.UserName, FORM_ID_289325045.TimeStamp, FORM_ID_289325045.[Survey Point - Area], FORM_ID_289325045.Measurement, FORM_ID_289325045.NewArea, FORM_ID_289325045.[EXIT Form] " & _
'"FROM FORM_ID_289325045"

'CurrentDb.Execute strSQL2, dbFailOnError

Exit_Archive_Primary_Click:
Exit Sub

Err_Archive_Primary_Click:
MsgBox Err.Description
Resume Exit_Archive_Primary_Click

End Sub

The next step is to do the same for the child table and append related records to the child archive table.

View 4 Replies View Related

Moving Data From 2 Related Tables To 2 New Tables As An Archive

Apr 3, 2007

View 1 Replies View Related

Tables :: Splitting One Table Into 3 Related Tables

Jul 16, 2014

I'm trying to split a table up because I now realize it won't be able to do what I need in the future. It wasn't designed properly at the outset, and I'm trying to correct it now.

This is a database of pregnancies and deliveries.

The single table does not cater well for multiple pregnancies (twins, triplets, etc), and also I foresee problems when mothers come back in future for another delivery.

Therefore I am trying to separate data into 3 tables: Mother, Delivery and Baby.

This is because each mother can deliver more than once, and each delivery can have more than one baby.

I have set up a trial database, with just a few fields in each table to see if this works. An screenshot of the table relationships is attached.

The primary key of the Mother table is linked to the Delivery table, and ditto the primary key of the Delivery table and the Baby table.

I'm not sure how to migrate the data over, in terms of the primary keys of each table, because in the new tables, these should be an AutoNumber field, so that they are unique numbers.

View 14 Replies View Related

Tables :: Update Data In Related Tables?

Oct 22, 2014

I have to make a Costing System but for that I need to enter our Expense details in database according to Fiscal year and months.

I need a table for Fixed expenses and one for Variable Expenses and then I need one or more Forms to update data in those tables. Now I've created a table with Fixed Expenses. I have to update Year and Amount in that. Now it is only letting me one entry per Expense.

I want multiple entries for one Expense say 'Advertising' for different years. I'm thinking may be I need to make more than one Table, may be one for Expenses with ExpenseID and other for Years with Year and YearID and the third one for Amount with columns Amount, Method of Payment, Date and Notes. I did tried this but I think I'm not creating proper relation may be because its only updating for one year.

I'm using Access 2003.

View 2 Replies View Related

Tables :: How To Deal With Exception To Related Tables

Aug 27, 2013

I am having an issue where a small percentage of my records do not follow my related Tables.

ex: 3 tables
tblEmployee
-intEmployeeID
-intWorkCenterID

tblWorkcenter
-intWorkCenterID

tblManager
-intManagerID
-intEmployeeID
-intWorkCenterID

where:
tblemployee.intworkcenterID -- tblworkcenter.intWorkcenterID -- tblManager.intWorkCenterID

Basically I have my general employee information in the tblemployee table, the workcenter information in the tblworkcenter, and the manager of the Workcenter in the tblManager. This is correct for 99% of the employee population. however, there are a few employees whose manager isn't the 'general' manager for the employees assigned workcenter.

How I can adjust an individual employees manager, while maintaining my structure? or would i have to go back and add the managers ID to a field in the tblEmployee Table?

View 7 Replies View Related

Tables :: Saving Data To Related Tables?

Sep 27, 2012

I have a built a database to record and collate info about clinical trials that are run within my department. I have one large table [Trial Info] which contains all trial info and have created a second and third table [Milestones] & [Comments] to collect dates of various milestones and a notes that occur throughout the course of a study.

The milestones (dates) are recorded in the 2nd table and the comments are recorded in the 3rd table.

All data is entered by staff on forms. The first form captures basic info about the study, once this has been entered, the idea is to click a button to open the second form to document the trial milestones, this form also contains a subform for listing any free text comments required during the life of the study which are stored in the 3rd table.

I have setup the relationships between tables as best I can.

The main table with basic trial info is the parent table, linking the primary key to the foreign key in the second table, the foreign key of the third table is related to the primary key in the second table so these tables should be looking up the correct clinical trial as far as I can tell.

The form containing data from the main table has a button which I have set to open the Milestones & Comments form, and I have set this to open to the record that relates to the record in view in the primary form. The primary key is related to the foreign look-up key in the milestones table.

This opens the form fine, however this is not updating the foreign look-up key, this remains '0' which I assume is the cause of the following error when I try to save changes to the record

Quote:

You cannot add or change a record because a related record is require in table 'Trial Info'

How do I trigger the foreign key to update to the primary key so that the first time a milestone page is opened for a new study it creates a matching record?

View 4 Replies View Related

Date Related Problem

May 31, 2005

I have a date related problem. I have an IF statement, that verifyes if a certain date entered in a table is <=Now(). The problem is, that it's vorking great just for curent year, curent month. For example: today is 31.05.2005 and if the value in my table is 13.06.2005 than for the <=Now() testing, it's telling me that 13.06.2005<31.05.2005. Why? Please help!!!!!!!!!!!!

Thanks,
Attila

View 4 Replies View Related

Tables :: New Row To Two Related Tables With One Form

Aug 6, 2013

So I have a form, it has a few fields from a related table in it, related by the name. I click 'new' on the form, but when I click save Access complains about the related table does not have a matching record. How do I have it create a new record?

View 2 Replies View Related

Updating Related Tables

May 15, 2006

Hi everyone!

My names james, im 18 and im a data manager for a school.

My schools database stores student data such as names, classes and exam grades. I have a table containing each students personal details such as name and address. Each pupil is assigned a unique PupilID which is the primary key. I then have a number of other tables linked to it with one to many relationships. Each of these tables holds a different set of exam results. The tables are linked through PupilID. The primary keys for the other tables are auto numbers.

The problem is this: when i add a new student to the Students table i cannot get their PupilID to automatically be entered into the other tables containing grades as part of a new field. How can i make this happen?

Thanks.

View 9 Replies View Related

Updating Related Tables.

Jan 22, 2007

Ok due to user error (and my own fault for not archiving an old database) we now have two databases which have had data entered into with the same table structure.

The table structure we have at the moment is with the related field in []:

Propertytbl[Propertycode] is related to Systemtbl[sysID] which is related to Assessmenttbl.

There is also another Temperaturetbl linked to Systemtbl by [sysID]

The problem i have is sysID is an autonumber. I can't simply paste in the difference in data as the assessmenttbl records and Temperaturetbl records will point to a different system (as the sysID will change in the Systemtbl data)

Can anyone think how i can do this either through using queries or programmatically so that i don't have to enter 5000 records manually.

I've tried using append queries but this produces the same affect as above.

Many thanks

Matt Collins

View 1 Replies View Related

Updating From Related Tables

Apr 2, 2007

I have a combo box in my main form that gets its list of physician names from tblPhysician. When the physician is chosen and the record saved, it saves the PhysicianID to my main table, tblOncReg.

When I delete a physician, any record that had that name chosen continues to have that name in the record despite it being deleted from tblPhysician.

I thought that setting relationships would solve it, but when I select "Enforce Referential Integrity" and "Cascade Update Related Fields", I get an error saying "Invalid field definition 'ID' in definition of index or relationship.", and it doesn't set the relationship.

Would setting the relationship solve that problem?

View 4 Replies View Related

Can Related Tables Have Same Field?

Sep 2, 2007

In a case where two tables are related, can the field names which relate them be the same? Or is it necessary to use different field names?

Robert

View 4 Replies View Related

Appending Related Tables

Jan 26, 2006

whats the best way of appending tbl_orders and the related data in tbl_order details to the two identical archive tables when the "COMPLETE" column is "YES" in tbl_orders

View 1 Replies View Related

Tabs With Related Tables

Aug 16, 2005

Hi,

I have a database with a couple of tables. All of which have a 1:1 relationship to another table, patient:
- patient (id (auto_increment), name, address, etc)
- nutrition (patient_id, some, stuff, etc)
- sleep (patient_id, foo, bar)

The patient_id in nutrition and sleep is a primary key and corresponds with the id of a patient (my 1:1 relationships)

I have a couple of forms. One for each table actually. In this form the user should be able to add/modify a patient's data. These forms are not to be used by themselves.

I have another form(the main form), which has a tab-control element on it. There are 3 tabs on this and on these tabs the 3 different forms are displayed.

Now for the problem:
- when I add a new record to the database using the main form, there is no problem when adding a new patient. Woohoo! When I switch to another tab, , however, the problems start: I can enter new information, but when I leave the tab I get an error: "Index or Primary key cannot contain a null value." Blech.

I want Access to realise that it should use the id that is given to the patient in the first tab, but it seems it cannot get it automatically. (I am not sure, but I think the problem didn't occur at first, so I think I may have done something which is causing this problem.)

Any help here is appreciated.

PS I was unable to find any posts on this forum that can help me with this. The closest I could find was: http://www.access-programmers.co.uk/forums/showthread.php?t=39933

View 4 Replies View Related

Default Values For Columns (Date Related)

Jul 27, 2007

Hi,

I want to make the default value of my column "TimeonOW" to be the difference in months(past 12) from the entered date in "OWStartDate" and the current system date.

For for example
mm/dd/yyyy

CurrentDate - OWStartDate = TimeonOW
07/01/2007 - 05/01/2005 = 26

I played around with the DateDiff function but to no avail.
=DateDiff("d",Now(),[OWStartDate])
=DateDiff("d",Now(),#2004-31-12#) etc

The reason behind this is because I need to count how many people fall into different month span categories, maybe theres a better way?

Thanks in advance.

View 4 Replies View Related

Help Required Again Pls(date/time Field Related)

Apr 2, 2005

Hi i have a field which is a date/time field and its format is short date which is xx/xx/xxxx. I want to ask if there is any way i can add a validation rule for only the year to be larger than 1980???

View 2 Replies View Related

Date Field That Shows Only Related Dates

Jan 27, 2013

I have a table that lists classes available (tblCourses); one that lists class dates (tblDates) and another that lists employees (tblEmployees). I have a fourth table that combines that data into classes taken by the employee (tblTraining). I have a form to input the employee's training with combobox fields for the COURSETITLE and CLASSDATE. I want the CLASSDATE field to only display the dates available for the specific class that was selected. How do I do that?

View 1 Replies View Related

Querie Involving Related Tables

Mar 28, 2007

hello, I have tables which have relationships in them. for example, ID appears within 3 different tables for the same item. How can I set up a query to delete all records with that ID from all of the tables in one go?
Thank you,
JDRasq

View 1 Replies View Related