I am working with access 2010 and I have a database that has attachments in the table. I have set up a query to in by database but every time I have a record with 2 or more attachments on it it shows that record as two different records in my query. I am wondering if there is a way I can make the query show one record and to be able to view attachments like in the original table.
View RepliesI am creating a database that needs to store a large number of pictures. I have a component that we're working on that needs 100 photos of specific details taken and stored.
I have created two separate tables.
One that includes four fields, ID, Component Number PhotoType and an attachment field.
The second table has ID,Photo Type and in this table I've coded 100 records detailing the photos I need. This is used as a lookup field for the first table.
What I would Like to accomplish from this point is to be able to add 100 fields onto the form for the user to click on the attachment and then save the specific photo that corresponds. How do i edit the control source of the attachment item on the form so that it is specific to one component number, and one phototype?
I have an Access 2010 database connected to several SharePoint lists. I'd like to be able to print a report (based on a query) and add that report as a .pdf attachment to the record I was looking at when I generated the report.
At the moment I can save the report as a .pdf to a local file store, then upload it as an attachment, but is there any way to skip that step and add it to the correct record automatically?
We're a small shop building a modest database to keep track of our product listings on eBay. Two people on non networked computers are doing the data entry into cloned copies of our database. This includes attached photos for each record. At the end of each week, we want to combine the records created separately into the master database on my system.
It seems like it would be simple enough to just cut and the paste the records from the two databases into the identical master. The problem I'm anticipating concerns the attached photos.
Some strategies are to transfer the records from the data entry computers to the master database while keeping the attached photos intact.
Is there an effective, efficient way to set this up or would it be best to use the OLE format for the pictures instead of attaching them (which I prefer)?
I am looking for a way to attach X amount of pictures to a record. I want to use a command button that opens an explorer to choose X amount of files. I don't want to just open a folder because that won't return the file location.
How to open an "attachment explorer"?
Access 2007
I currently have an unnormalized table that consists of individuals (employee profiles) which currently has a field for attachments. I am using the "attachments" for photographs of each employee however I dont want to maintain photographs for former employees, or if I do not forever. I was told to normalize the table by removing the "attachment" field and placing it in a new table with referene to the employeeID.
Since I currently have something like 200+ records where I want to relocate the employee picture, i tried to create a make table query off the origional table and only referencing the employeeid and "attachment" to the new table however I keep getting an error when running the query. The error indicates something to the affect of having multiple values.
I am looking for a query that will return records from a table that have related records in another table. Opposite to the Unmatched Query Wizard.I have two tables: tblSupplier and tblSupplierProducts.The two tables are related by the field "SupplierId".I need the query to only return Suppliers that have Products.
View 3 Replies View RelatedCurrently, I'm taking my unread emails, storing them in a table then sending any Excel files to a specified folder I've modified some code I found while surfing and it works great but I'd really like to send the attachment name and file path with the email recordset. It's been a long day so maybe it's super simple. Below is my code. Office 2010 Windows 8.1
PHP Code:
Function ReadInbox()
Dim TempRst As DAO.Recordset
Dim OlApp As Outlook.Application
Dim Inbox As Outlook.MAPIFolder
Dim Atmt As Outlook.Attachment
Dim InboxItems As Outlook.Items
[Code] ....
I have a survey database that I've been using for the last year for monthly auditing of employees files. I need to be able to get monthly audit scores for each employee but grouped by their manager. The problem I'm having is employees have moved between managers throughout the year, so employees that are listed under Manager 2 now were actually working for Manager 1 when the audits occurred.
ie. Audits occurred Jan - April for Employee 1 while they were assigned to Manager 1. Employee 1 moved to Manager 2s team in May. So when running monthly reports for the year Employee 1 audits should fall under Manager 1 for Jan-April and Manager 2 for May-Dec.
I do have a history table set up like:
tblEmployeeHistory
ID (PK)
EmployeeID (FK to Employee table)
ManagerID (FK to Manager table)
MoveDate (date employee assigned to manager)
The Employee table is set up like:
Employee ID (PK)
EmployeeName
ManagerID (FK to Manager table)
The Manager table has the ManagerID and ManagerName.
That's the employee side of things; then I have the tables that store the audit results:
tblAudit
AuditID
FileNumber
AuditDate
EmployeeID (FK to employee table)
tblAuditResults
AuditID; QstnID (Composite PK, QstnID is FK to tblQuestions)
Answer
How can I use AuditDate and MoveDate to relate audits to the managers the employees were under when the audits occurred?
I'm using an UPDATE query to update records in one table (tblMain) from another table (tblTemp)
Here is my SQL :
Quote:
UPDATE [tblMain]
INNER JOIN [tblTemp] ON [tblMain].[MainField1] = [tblTemp].[TempField1]
SET [tblMain].[MainField2] = [tblTemp].[TempField2];
I only want to update the records in tblMain which have a corresponding record in tblTemp (linked by MainField1 / TempField1)
If any record doesn't appear in tblTemp, I want tblMain to retain the existing value for that record.
However, it appears that in such situations, the record in tblMain has it's MainField2 value set to null / ZLS.
I've tried using LEFT JOIN and RIGHT JOIN and also tried WHERE clauses but the result is the same every time.
tbl_A has column "ID" with duplicates. tbl_B has column "ID" with distinct values.i want to find all the distinct IDs in tbl_A that are present in tbl_B.I've tried the following with no luck:
SELECT DISTINCT tbl_A.ID
FROM tbl_A LEFT JOIN tbl_B ON tbl_A.ID = tbl_B.ID
WHERE tbl_A.ID IN tbl_B.ID;
I have an Access 2007 application that has a Parts Table that contains a list of automotive parts.I have attached a screen shot (parts.jpg) showing the structure.I have another table called Web_Parts that has exactly the same structure as Parts. The Web_Parts table gets its data from a CSV import that I do that is data extracted from an MySQL database used by an eCommerce website.
You will notice that there is a field called "Web_Product_Id" (number). This is the unique ID for each of the products that I have exported from the Web shop system. I need to regularly (probably every couple of weeks), export out of the web shop system and import into the Access environment.
Due to the fact that the data in the web shop system may change (pricing, description, add new items, delete new items etc), I need to find a way that I can simply update any existing records in the Parts table with any new information contained in the Web_Parts table......
I have a table which lists amendments (amendments history file) which need to take effect of another table (M0070), I can select the records fine and so the sub query looks to be okay but I'm missing something (probably obvious) in the deletion query where by it does only delete the records selected but everything (I know that the asterisk is wrong in the code below but I'm not sure what to replace it with).
At this point I'm trying to delete all records for a specific employee on a specific contract (they can be 1 or more employee records for that individual on a contract - and they could exist on multiple contracts).
DELETE *
FROM M0070
WHERE EXISTS
(SELECT M0070.[Contract No], M0070.[Contract Name], M0070.[Employee No], M0070.Forename, M0070.Surname, M0070.Branch, M0070.[Long Desc], M0070.[Emp Post Start Date], M0070.[Days per week],
[Code] .....
I have a table with the following
Code:
fname,lname,location1,location2
-------------------------------
john, doe, dublin, null
john, doe, null, galway
and i wish to consolidate it to show like
Code:
fname,lname,location1,location2
-------------------------------
john, doe, dublin, galway.
I could do it by creating a new table, then merging, but its cumbersome.
delete Query How do i Specify the table containing the records that I want to delete?
View 1 Replies View RelatedI have two tables as follows;
Master file:Vessel_master
Vessel_code
Vessel_name
main data table:Main
Vessel_code
container_no
size
Voyage
weight
by using above two table have made a query as follows;
Vessel_name:Vessel_master
Vessel_code:Main
container_no:Main
size:Main
Voyage:Main
weight:Main
Have joined "vessel_code" fields of the both tables by join type 2.
by using the query as data source made a form to edit/update/delete data. the problem is, when delete a record by using the form, it will delete the related data in the master file as well, which i do not want.
I have a table that includes a date/time field. I want to query that table and show all but the TOP N records. Since the number of records will always be changing I cant do it using the BOTTOM N records.
View 2 Replies View RelatedI've got 2 tables, same structure, one [T-temp-Target] holds number of training units split by module a trainee needs to finish the course, the other [T-temp-Actual] holds what they've completed so far.
Both tables have structure
TRAINEEID
MODCODE
CountOfUnits
I'm trying to find the modules that they've not done yet so I can add up the units for them, only modules that have been started are recorded in the table of what they've done [T-temp-Actual], modules they haven't started yet aren't included in it. Here's the SQL
Code:
SELECT
[T-temp-Target].TRAINEEID,
[T-temp-Target].MODCODE,
[T-temp-Actual].MODCODE
FROM [T-temp-Actual] INNER JOIN [T-temp-Target] ON
([T-temp-Actual].TRAINEEID = [T-temp-Target].TRAINEEID) AND
([T-temp-Actual].MODCODE = [T-temp-Target].MODCODE)
WHERE ((([T-temp-Actual].MODCODE) Is Null));
I'm using Access 2007.I have 2 tables that are identical in structure.The tables are called [Workorder Parts] and [Workorder Parts Temp]. They both have the same structure:
WorkOrderPartID (Autonumber)
WorkorderID(Number)
PartID(Number)
Quantity(Number)
UnitPrice(Currency)
Notes(Memo)
KitID(Number).
[Workorder Parts] has 128 records in in and [Workorder Parts Temp] has 28 records in it that are a small subset of [Workorder Parts] therefore, the contents of the key field column in [Workorder Parts Temp] is the same as in [Workorder Parts].I tried the following SQL but get an error message saying "Cannot Update "WorkOrderPartID" Field not updateable":
DoCmd.RunSQL "INSERT INTO [WorkOrder Parts] SELECT * FROM [Workorder Parts Temp]"
My aim is that I want all of the records from [Workorder Parts Temp]to be copied to [Workorder Parts] and have their "WorkOrderPartID" fields correctly updated with a new value...
I have a table which has duplicate records so I want to write down the query that will remove the duplicate records from the table.
View 1 Replies View RelatedI'm using Access 2007.
I have a button on a form that when clicked, does the following:
I have a table called [Workorder Parts] that has 128 records in it with Fields named WorkOrderPartID (Autonumber), WorkorderID(Number), PartID(Number), Quantity(Number), UnitPrice(Currency), Notes(Memo), KitID(Number).
I want to copy records into another table called [tKitsWorkorderParts] that has the identical structure based upon a value in the field WorkorderID. For testing purposes let's assume that the field contains the value "12". There are 28 records in the table that have the value set to "12"
If I hardcode the value "12" into the following SQL statement, it finds and copies the 28 records correctly.
DoCmd.RunSQL "INSERT INTO [tKitsWorkOrderParts] SELECT * FROM [WorkOrder Parts] WHERE [WorkOrder Parts.WorkOrderID] = 12"
so far so good.....
Now, on the form there is a Text Box called WorkorderID that contains the value "12".
If I change the code to the following, I get all 128 records instead of just the 28 I am expecting:
DoCmd.RunSQL "INSERT INTO [tKitsWorkOrderParts] SELECT * FROM [WorkOrder Parts] WHERE [WorkOrder Parts.WorkOrderID] = WorkorderID"
I have inserted a few MsgBox displays to display the value contained in WorkorderID and it shows "12"...
What am I doing wrong???
I have a file that I want to import on a daily basis and append to an existing table in my database. The date changes each day. I want to create a query that checks to see if the date (of the first record) already exists before I import and append the new file. If it does, I want to show a message saying something to the effect of "This file has already been imported".
View 3 Replies View RelatedHow can I combine several records in a table into one record?
Suppose that I have a table like Table1 in the attached image.
Then I want to combine all records with the same value for Key1 in one record.
The result is shown in Table2 in the attached image.
I would prefer to do it using SQL only, but I guess that this is not possible. Is it possible?
Alternatively I could accept to turn to VBA that could do it. Any good links about this?
I'm trying to do a query to count corresponding records in another table. It works except for returning zeros. I've tried using NZ and switching the type of join, but to no avail.
Here's what I have:
Query A has 3 columns (FU kids)
AlphaID
DtcCtr (a Location Code)
DlsDtc (a Date of change)
Table A has many columns, but I'm only using a few. (dbo_MNCPSTNote)
AlphaID
DtcCtr (the same Location Code)
ServDate (the date I'm trying to count)
Here's what I have:
SELECT [FU kids].AlphaID, [FU kids].DtcCtr, Count(dbo_MNCPSTNote.ServDate) AS CountOfServDate
FROM dbo_MNCPSTNote RIGHT JOIN [FU kids] ON dbo_MNCPSTNote.AlphaID = [FU kids].AlphaID
WHERE (((dbo_MNCPSTNote.Center)=[FU kids]![DtcCtr])) OR (((dbo_MNCPSTNote.ServDate)>[FU kids]![DlsDtc]))
GROUP BY [FU kids].AlphaID, [FU kids].DtcCtr;
I want to know the count, including zero, of the number of records based on ServDate for each AlphaID in Query A.
I didn't create the tables and have no control over how they are designed/organized.
I have a table which has a primary ID and then date/time then an empid field relating to an empid table,
Is it possible to design a query to return all records per empid which exceed 30 minutes for the last 30 days? or even 'today' or a specified date whichever is easiest?
How to get the following results using 1 table:
Field1 ID is an auto record ID, field2 ID is actually field1 ID assigned that record, in other words record 1 has a roommate (record 5) assigned to it, record 2 has a roommate (record 4) assigned to it
Table A
Field1 ID Field2 ID
1 5
2 4
3 6
4 2
5 1
6 8
Results records I'm looking to display for would be:
1 5
2 4
3 6
6 8
I only want to display all fields for records, but I don't want to show their matching record, so I want to display record1, but not record 5 because record 1 has record 5 as a roommate, want to display record 2 but not record 4.