All Rocords From One Table Included In Another.

Mar 21, 2005

I need to build a database to track projects.

There are 10 groups that may work on any given project.

Each group may perform any number of tasks.

I made 3 tables

1. Projects
A. PROJECT_ID
B. PROJECT_NAME
C. GROUP_ID

2. Groups
A. GROUP_ID
B GROUP_NAME
C. PROJECT_ID
D. TASK_ID

3. Tasks
A. TASK_ID
B. TASK_NAME
C. DESCRIPTION
D. TIME_SPENT
E. NOTES
F. GROUP_ID

Projects to Groups is one to many
Groups to Tasks is one to many.
The problem I think I am running in to is how to show all of the groups in each Project even if a given group has not performed any tasks.

View Replies


ADVERTISEMENT

Imported Table Not Included In Reports!

Sep 2, 2004

I have an imported table that I created a form for (containing 400+ entries). I created a subform on it for a new table that I will be entering information to tie in with the imported form.

However, when I try to create a report, the report won't acknowledge any of the information. It's all outlined in the SQL, but nothing appears.

I did discover, though, that if I create a new entry on the imported table, that information appears on the report, but nothing for the existing data that was imported.

Please help!!!!!

View 1 Replies View Related

Missing Records When A Table Is Included In A Query

Mar 1, 2006

I am working with a normalized database that has MANY tables. Most of these consist of lists of options to select from for the primary table. The primary table is linked to the secondary tables (and those to tertiary tables) by Primary Key ID fields, and the other tables contain additional information.

For Example, the Name list table is related to the Actions table by the SSN field, and the Actions table is related to the PayStatus table by an ID number collected by a lookup field.

When I pull all three tables into a query to display all the information related to a particular individual an his action, there are more records if I remove the PayStatus table from the query. It seems to only pull reports for which a PayStatus has been selected.

How can I get the query to display ALL the records, whether or not the individual has a pay status? Whether or not the individual has one is irrelevant, but I want his name to be displayed, whether or not he has one.

Thanks for any suggestions.

View 2 Replies View Related

Queries :: IIF With Null Included Field - Lookup Table Values

Jul 1, 2013

I am currently working on an instrument datebase, I have a mainquery that takes care of user inputs from a form. The main fields that have been queried on are Type, System, and Manufacturer and they are all look-up fields that contain some null values.

On the same criteria row for these fields, I have

Like IIf([forms]![User Interface].[qtype2]="","*",[forms]![User Interface].[qtype2])
Like IIf([forms]![User Interface].[qsys2]="","*",[forms]![User Interface].[qsys2])
Like IIf([forms]![User Interface].[qman1]="","*",[forms]![User Interface].[qman1])

qtype, qsys and qman are the user inputs from the user interface that returns look-up table values.

This works fine when all 3 of these fields are all filled out for a certain instrument. The problem arise when some fields of the instrument are left blank or is null. The instrument won't show up in a query at all. What I wanted it to do is to show everything including the ones with null fields when the user input are null or "". When the user specifies certain requirement I only want to show the ones that are not null. I understand that putting them on the same row means AND, I have tried to OR them and did not have the result i wanted.

View 3 Replies View Related

Combo Box Help (database Included)

Jul 25, 2005

hi All,
I've included an extremely cut down version of my database (33k zip file) with the example I'm trying to do.

There is a Combo Box (three options), I basically want this box to switch to the relevent record. (e.g. if you select Rural, it will switch to the next rural record) Rather, the combo box is changing the field of the current record to Rural which I don't want it do. I also want the ability to "Filter by Selection" too which it does but it just changes the current record :(

Any help would be GREATLY appreciated! It's been driving me crazy for days now.

If you click on "FOCUS OF SERVICE", the combo box in question is called "AREA" that is located up the top.

databasedemo.zip (http://www.deakin.edu.au/~apb/databasedemo.zip)

thanks,
andrew

View 3 Replies View Related

Can't Open PDF - Code Included

Sep 26, 2005

With this code, I can open Word and Excel files, but not PDF. Please let me know what I'm doing wrong. (I tried changing strProg = "C:Acrobat.lnk" including to a .exe file but nothing happens when I click the Open button)

---------------------------------------------------

Private Sub cmdOpen_Click()

Dim sSourcePath As String
Dim strProg As String
Dim objword As Object
Dim strFile As String
Dim XL As Object

sSourcePath = Me.FilePath

DoCmd.RunCommand acCmdSaveRecord

If fIsFileDIR(sSourcePath) = -1 Then

Select Case ParseFileName(sSourcePath, 3)

Case "xls"

Set XL = CreateObject("Excel.Application")

If IsNull(Me.FilePath) Then
MsgBox "You haven't Attached a File"

Else

With XL.Application
.Visible = True
.workbooks.Open Me.FilePath

End With

Set XL = Nothing

End If

Case "doc"

Set objword = CreateObject("Word.Application")

strFile = Me.FilePath

objword.Visible = True
objword.Documents.Open strFile

'--------------------------------------------------
Case "pdf"
'Open File

strProg = "C:Acrobat.lnk"

Call Shell(strProg & " " & sSourcePath, vbMaximizedFocus)


Case Else

MsgBox "File type not recognised"

End Select

Else

MsgBox "File cannot be found. Please check the file path. ", vbExclamation

End If

End Sub

View 1 Replies View Related

Trying To Have Sums And Individual Records On The Same Page (picture Included)

Sep 8, 2006

well i tried asking for help on this earlier, but i wasn't able to find any help so here is my problem.http://img330.imageshack.us/img330/2850/pictureaccessro7.jpg (http://imageshack.us)the sales - net = profit is fine but what i want to do is have a totals at the very bottem. everytime i try to do this, i can only get the totals to show and each individual record dissapears, or nothing shows at all. i've tried using a report but i can't seem to pull it off either. if anyone knows what i'm talking about and knows what i have to do then please help me :)

View 4 Replies View Related

Query Records With Todays Date But I NEED The Time Included

Dec 13, 2006

I have a basic trouble ticket type database that I built. It records troubles and auto fills the date and time when a new records created. I want a simple query that will display the number of calls that each person has taken today.

The problem being that If I use >Date()-1 I will get calls from this time yesterday until now. Is there a way to round down the time to today only? I don't want to lose my time stamp.

View 8 Replies View Related

Modules & VBA :: Export Pdf With Field Names Included With File Name

Feb 7, 2014

I have a report that I would like to export to a folder, and I would like to include the FullName within the naming convention of the PDF. When I run the report it prompts for the Employee ID..Here is what I have so far, I'm getting an error of "Run Time Error 424 object required" on the String Report Name,

Private Sub Create_PDF_Click()
Dim myPath As String
Dim strReportName As String
DoCmd.OpenReport "Report_Salary_Worksheet _Finalized_By_EmpID", acViewPreview
myPath = "W:COMPENSPHYSICIANSComp Plans"
strReportName = Report_Salary_Worksheet_Finalized_By_EmpID.[FullName] + ".pdf"
DoCmd.OutputTo acOutputReport, "", acFormatPDF, myPath + strReportName, True
DoCmd.Close acReport, " Report_Salary_Worksheet _Finalized_By_EmpID "
End Sub

View 5 Replies View Related

Auto Backup On Entire Database Close / Forms Included

May 10, 2014

I have a database with multiple forms and I would like to on close I would assume of the form, save a backup to a specific folder. This will all change once I move the database to the server but for now want a backup on my machine. Here is my code:

Code:
Private Sub Form_Close()
Call db_backup
End Sub
Below is in a module

[code]...

The problem is when I run this the program will give me an error saying: runtime error 438 object does not support this property or method

View 8 Replies View Related

Queries :: Union Within Subquery - Specified Expression As Part Of Aggregate Function Not Included

May 27, 2014

I've created the following but it keeps coming up with the error message You tried to execute a query that does not include the specified expression 'ICE Team' as part of an aggregate function.

SELECT ztSub.[Master Sheet].[ICE Team], ztSub.[date], Count(ztSub.[Count])
FROM (SELECT [Master Sheet].[ICE Team],[Master Sheet].[Visit Date (planned for)] AS [date],Count([Master Sheet]![Visit Date (planned for)]) AS [Count]
FROM [Master Sheet]
UNION
SELECT [Master Sheet].[ICE Team],[Master Sheet].[Date retasked to?] AS [date], Count ([Master Sheet]![Date retasked to?]) AS [Count]
FROM [Master Sheet] ) AS ztSub
GROUP BY ztSub.[Master Sheet].[ICE Team];

View 5 Replies View Related

Sql Query Problem (Query Included)

Sep 21, 2006

Operating System: Windows XP Pro
Access 2000 (9.0.8948 SP-3)

Problem Category: SQL query


Greetings:
I have a problem that I cannot solve. I have a form named “ALABAMA ARREST REPORT” on that form in a listbox named “List17” the row source is a query named “List17Query” this query is the problem in that I need its search criteria to come from the value of a textbox on the “ALABAMA ARREST REPORT” named “SSN” . Below is the query in question. When I type in a value (Example 1) it works however when I change it to receive the value from the form (Example 2) it does not.

Somebody please help




Example 1

SELECT [Master Name Index].ID, [Master Name Index].Soc, [Master Name Index].[Last Name], [Master Name Index].[First Name], [Master Name Index].MI, [Master Name Index].MOB, [Master Name Index].DOB, [Master Name Index].YOB, [Master Name Index].[Case #], [Master Name Index].Address, [Master Name Index].City, [Master Name Index].State, [Master Name Index].[Zip code], [Master Name Index].[Home phone], [Master Name Index].[Work phone], [Master Name Index].Race, [Master Name Index].Sex, [Master Name Index].Height, [Master Name Index].Weight, [Master Name Index].Hair, [Master Name Index].Eyes, [Master Name Index].Aliases, [Master Name Index].Status
FROM [Master Name Index]
WHERE ((([Master Name Index].Soc) Like "595-90-0000"));




Example 2

SELECT [Master Name Index].ID, [Master Name Index].Soc, [Master Name Index].[Last Name], [Master Name Index].[First Name], [Master Name Index].MI, [Master Name Index].MOB, [Master Name Index].DOB, [Master Name Index].YOB, [Master Name Index].[Case #], [Master Name Index].Address, [Master Name Index].City, [Master Name Index].State, [Master Name Index].[Zip code], [Master Name Index].[Home phone], [Master Name Index].[Work phone], [Master Name Index].Race, [Master Name Index].Sex, [Master Name Index].Height, [Master Name Index].Weight, [Master Name Index].Hair, [Master Name Index].Eyes, [Master Name Index].Aliases, [Master Name Index].Status
FROM [Master Name Index]
WHERE ((([Master Name Index].Soc) Like "*[Forms]![ALABAMA ARREST REPORT]![SSN]"));
.
Resolution:

WHERE ((([Master Name Index].Soc) Like "*" & [Forms]![ALABAMA ARREST REPORT]![SSN]));

Thank you very much "ByteMyzer"

Mshelley1

View 1 Replies View Related

Queries :: Update In Table From Same Table Based On Criteria Column In Same Table

Sep 29, 2013

How can I update (some columns) in a table from the same table based on a Criteria column in the same table.

View 2 Replies View Related

Need 2 Fields From Table 1 / Using Lookup Info From Table 2 And Put Into Table 3

Jun 28, 2013

I need to get x and y coordinates for each device, but the data has to get looked up from 2 other tables.

I have a table (called InstReclosers) that has device names. Each device is on a Section. I can go to another table (called InstSections) and look up what Node that particular section is tied to. Then I need to go to another table (called Nodes) to get the X and Y location for that particular node.

How I can go about getting this X and Y data into the InstReclosers table?

...in summary, InstReclosers has device name and section name. InstSections has section name and node name. Nodes has node name and XY coords. Need XY coords for each device in InstReclosers.

View 3 Replies View Related

General :: When Transfer Data From Table In Excel To Table In Access / It Loses Format

Jan 29, 2015

When I try to transfer (ctrl c + v) data from a table in Excel to a table in Access it loses format.

Exemple: $ 1.000,00 (Excel) become 1000 (Access).

I need to keep the format to make sure that the code works properly.

View 9 Replies View Related

Tables :: Pull Field From Main Table And Create A New Table With Date Stamp

Nov 4, 2014

I have a make-table query that pulls all the fields from 1 table (MainTable), and creates a new table with a date stamp based apon a form value entered (New Table = MainTableWithDate).

Currently, I setup the query to pull info from the form field like this:

DateField: [Forms]![frmmain]![DateField]

However, when the make-table query is done - all date fields are blank (all other fields are correctly created), and when I look at the new created table (mainTableWIthDate), the typeassigned to the date field is "Binary" (in the form, I've specified LongDate).

View 6 Replies View Related

Queries :: DSum - Update A Field In Specific Table With Info Form Another Table

Aug 5, 2014

I have a table products with a field "id_product" and "total" (Total items in stock)

I have a query with the fields "id_product" and also the field "total in stock"

I want an update query to update the field 'total' in table 'products' with infos from that query

For each id-product in table products, replace the field total with the field 'total in stock' from the query

So I want to update a filed in a specific table with infos form another table.

View 2 Replies View Related

Queries :: Updating Child Table List View On Change Of Main Table Row

Apr 19, 2013

Here's a query that the bottom listview in the attached form i.e. a listview representing a table of calls(many) to fims (1 top listview)

Code:
SELECT calls.id, calls.firm_id, calls.called, calls.said, calls.spoke_to, calls.next
FROM calls
WHERE (((calls.firm_id)=[firms].[id]))
ORDER BY calls.called DESC , calls.next DESC;

When I run the thing...I get a dialog asking me for firm id.

I want to change this so when I move up and down the firms LV (top)... the bottom LV updates taking firm id from the top LV with focus.

Access 2003.

View 2 Replies View Related

General :: Copy Data From Local Access Table To Linked SQL Server Table?

Jan 13, 2014

I have an MS Access accdb with linked SQL Server 2012 ODBC tables. I am working on a procedure to copy data from local tables to these linked tables (identical schema). I did a simple

Code:

DoCmd.RunSQL "INSERT INTO linkedTable SELECT * FROM localTable"

This works, but is very slow. Way too slow. (INSERT copies the data one record at a time).

I would like to copy the data in a bulk operation, or operations that I can execute programmatically.

View 1 Replies View Related

Tables :: Multiple Parents Table Linked To Child Table - Primary Keys

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

Making Form Open With Data From A Table Based On Date And Time In Table?

Sep 12, 2014

How to make a form open with data from a table based on a date and time in the/a table?

View 7 Replies View Related

Extracting Data From Linked Db2 Table Using Access Make Table Query

Aug 29, 2006

Hi,
I am extracting data from linked db2 table using access make table query. First I create a select query and can view the linked db2 data, but when I change to a make table query I get an error message, "invalid argument", when I run the make table query. There is no selection critera specified. Has anyone had this happen? and Do you know a solution?

Thanks for your support
GinnyP

View 1 Replies View Related

Move Record To New Table After Data Change In Combo Box Value In Original Table.

Nov 24, 2006

Ok my problem is this......

I have a form with a field "Property Status" on it. It has 4 possible values -

"C - SHELTERED (with warden charge)";"H - SHELTERED (No warden charge)";"J - WHEELCHAIR SHELTERED (With warden charge)";"M - WHEELCHAIR SHELTERED (No warden charge)";"X - DISCONNECTED"

When the value "X - DISCONNECTED" is selected in the form I want the record to be removed from its existing table and sent to a new table which keeps all the "X - DISCONNECTED" records together.

Any ideas would be greatly appreciated. How would this be coded?

View 1 Replies View Related

If A Make-table Query Result Is Null, How To Have A Default Message Appear In Table ?

Jan 21, 2005

Hi everybody,

Beginner here needs help !

I'm building a make-table query for which if the result is null (no record correspond to the set of criterias), a default message like "there was no activity during the period" would appear in the table (not a message box...I need the message in the output table). The best I could think of is an IIF function but it doesn't seem to work... Is there any way to do this without using VBA?

Thanks in advance !

View 1 Replies View Related

Modules & VBA :: Split Table Record Into Multiple Records / Rows In A New Table

Nov 10, 2014

In a situation where I imported an excel file with so many columns and split them into two temp tables and they are linked using a key.

the data has a fixed part lets say

Field1....Field2.....Filed3.....Field4...then Field5.....Field6.....Field7....Field8 is the same data range as Field9...Field10...Field11...Field12. I would want to split this data into multiple rows like this

Field 1 Field2 Field3 Field4 Field5 Field6 Field7 Field8
Field 1 Field2 Field3 Field4 Field9 field10 field11 field12 and so own...

What is the best approach?

View 3 Replies View Related

General :: Possible To Have Data From Linked Table Automatically Update Into Existing Table?

Aug 17, 2012

I have one DB that is used for creating/storing customer ID's, and another DB that is used for creating/storing job information for customers.I have linked the table from the customer DB to the job DB.

There is a table in the job database that holds customer name and ID, and some VBA that generates unique job codes.

Is it possible to have the data from the linked table automatically update into the existing table?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved