Create a temporary table to hold the results. Use VBA to open the data table and temporary table in two recordsets. Loop through the records in the data table and place the data in the temporary table in the required positions.
View RepliesI have several Databases with similar tables with similar information, but entered differently. (Different persons made separate databases before my time) I would like to make new tables, write a query to combine the information into these tables, but I need to get the data similar before I can combine.
Example of information:
7L 24 3A719 AD one table might have it stored this way
7l243a719 AD another this way
7L24-3A719-AD or 7L24 3A719 AD both ways in this table
and so on. There are alot of combinations
I have been in attempting Mid$, Right$, Left$, Like, Not Like and others to no avail.
Looking to make all the information the same layout 7L243A719AD, then I will be able to combine information.
Any ideas / help would be appreciated.
Hi everyone, here is one of the tougher problems ive had to deal with.
i have a number of fields, called teammember2, teammember3 etc..to teammember12. This is done because on the form, the user is able to select team members in addition to their original selection using a button.
Another field on the form is a "total days worked".
The problem now is adding the total days worked for each record based on the team member
for example:
if record 1 has : john smith and jane smith as team members
and record 2 has: john smith and tom smith as team members
i need the report (query) show the total days worked for john smith as record 1+ record 2. and for jane smith just record 1. tom smith would show total days for record 2.
im sorry for the weird explanation! i will be happy to clarify anything.
Hello all. Hope someone can help.
I want to know which of my customers received a particular service (service id: #199) during the last month. It gets complicated (I think) because of those customers that received service 199 last month, I want to know which ones did not receive any other services in the 90 days prior to receiving it. Any thoughts on how this can be done? I can find who received the service during the last month, but how do you check to make sure they have not received any others during the last 90 days?
Any help is much appreciated.
Thanks,
S.J.
Hope any Access guru can help me with this problem.
I want to create a cross training matrix with crosstab query in Access to show the relationship between the trainers and trainees with purpose of the trainings from the following table.
XTrainID...Purpose.............Trainer............ .Trainee
1.............Web Focus......... John................Edward
2.............Search Engine.....Mary................John
3.............CEI................... Matthew...........Edward
4............SAS................... Paul..................Mary
5.............NET.................. Greg.................Sam
6............SAS................... Jennifer..............Mary
7............SAS................... Matthew.............Edward
The resulting matrix should look something like this:
............ Greg...... Jennifer....John.............Mary........Matthew.. ....Paul
Edward..............................Web Focus...................CEI, SAS
John.............................................. .......Search Engine
Mary...................SAS........................ ....................................SAS
Sam....... .NET
I tried to use crosstab Q and picked Trainee as Row Heading, Trainer as Column Heading and Purpose as Value - with the Total option as First, but it would only show the first purpose even if trainer with the same trianee has more than 1 cross training involvement. The example is for Matthew & Edward. In this case, it only show CEI but not SAS. My challenge here is how to concatenate training purposes with the same trainer and trainee.
Any suggestion would be much appreciated.
Mike
All,
I'm working on a bespoke logging feature for a web portal which records user events in a single table (tblUserLog as illustrated below).
idsession_idauth_userapp_nameeventremote_addrhttp_user_agenttimestamp
2331063924932jimApp 1Some event10.203.115.30Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.1.4322)22-Nov-06
2341063924934joeApp 2some other event10.203.112.40Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.1.4322)22-Nov-06
2351063924934joeApp 1Some event10.203.112.40Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.1.4322)22-Nov-06
2361063924934joeApp 1Some event10.203.112.40Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.1.4322)22-Nov-06
2371063924936jimApp 2some other event10.203.112.40Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.1.4322)22-Nov-06
2381063924936jimApp 2some other event10.203.112.40Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.1.4322)22-Nov-06
2391063924936jimApp 2A completely different event10.203.112.40Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.1.4322)22-Nov-06
To supplement this flat list I'm preparing a number of key stats which will be reported to administrators (e.g. unique user/session count by period, app usage by period etc).
I'm now looking to report the (10?) most active users where most active means those users having the most unique sessions. The desired results are as follows:
User Sessions
joe 23
paul 10
chris 5
jim 2
... ...
Within Access its relatively easy to create/combine a number of queries to acheive this but I need to do this via an ASP script. The challenge is to design a single SQL statement (with nested SQL?) which will return a recordset as above.
Any help on this would be much appreciated.
Thanks in advance,
Rob.
I need to complete a project for school for my computer class. I am having some problems with this one assignment. I am finished but want to know if anybody can help me. Just verify that the answer is right or wrong. Any input will be greatly appreciated. Here it is... Its just a fill in the blank so if anybody who knows MS access can help me that would be great. Thanks.
The word required are the ones with the underscores around them.
Fill in the blanks:
1. Some developers like to use the _Design View_ property for a field to document its contents, such as identifying a field as a primary or foreign key or providing users with instructions about entering values into the field.
2. A _Query__ is a database object that stores criteria for selecting records from one or more tables based on conditions you specify.
3. To select multiple controls, you could click a control, and then press and hold the _Shift_ key and then click the addition controls you want to select, releasing the key once all the controls are selected.
4. A form?s _Table_ is the underlying object that provides the fields and data in the form.
5. You can view a description of each property in a property sheet by clicking the property and then pressing the _F1_ key.
6. When you select a control, eight handles appear on its corners and edges. The larger handle in a control?s upper-left corner is its _Move_ handle.
7. A(n) _Data Type_ displays a list of values, and lets users select one from the list.
8. Each report can have up to _Over 300_ sort fields.
9. An advantage of using the _Table Wizard_ to create a table is that the sample tables and fields already have properties set that might work well in your database or that are easily changed to accommodate your specific data needs.
10. In Design view, you can use the _Property Sheet_ to set the properties for fields that do not use the default settings.
11. In Access, you can specify restrictions on the data that users can enter into a field by creating a(n) _Validation Rule.
12. If a user attempts to enter an invalid value in a field, the _Validation Text_ property for the field opens a dialog box with a predefined message that explains the valid values.
13. In Access, you define table relationships in the _Relationship_ window.
14. You use _Borders_ around field names to distinguish them from function names or other types of values.
15. When entering the data in a table in Datasheet view, you would press the _Control + Minus Sign_ key to cancel a record that you have just entered.
16. In Access, you can control the format of a value by creating a(n) _Input Mask_.
17. A(n) _Look Up_ field lets the user select a field value from a list of existing field values stored in the database or from a list of values specified when the field was created.
18. The primary objective of creating a(n) _Indexed_ field in a table is to prevent users from entering duplicate records into the table.
19. If an expression is complex, you can use _Brackets_ to indicate which operation should be performed first.
20. To move from one record to another in a main form, you would click the buttons on the _Navigation_ bar at the bottom of the main form.
I am new to access, so I haven't been able to come up to speed on all of the functionalities that it has. I have a table with a social security number, an effective date, a department, and pay periods. I have seven other tables with a social, a date, and absentee occurances. For any social in the seven tables, I need to subtract one pay period from the original pay period table based upon the max effective date that is less than the occurance date. I am dealing with large amounts of data, at least a million rows in the original table, and 50,000 in the 7 smaller tables. I know access may not be ideal for this solution, but it is what I have to work with. Any suggestions?
View 2 Replies View RelatedDear Access Chart Expert
I have been trying to figure out how I can make the x-axis show month abbreviation names instead of numbers. Please see attached gif.
Currently, I use the Month([DateField]) function to get numbers of the month because if I use Format([DateField], "mmm") to get "Jan, Feb, March" the chart sorts the months in alphabetical order instead of the correct monthly order.
How can I be able to use the Jan, Feb etc... labels and have the chart sort them in the correct month order and not alphabetical order?
Thanks so much...
greatly appreciated.
Ok guys, I've been eyeing this problem since June and I must say that I'm rather sick of it. After months of dredging the web, I've come to the find multiple mocking hits implying that the answer may lay somewhere in the Experts-Exchange, where i understand that some of you have been know to roam.
Without furthur blather, I must admit that im trying to avoid paying 12 bucks for this answer, especially as I've grown suspicious that this is a straight up MS/Adobe collaboration failure... and now for the problem.
When opening PDF files from my database (Access 2003) Acrobat (7 and 8) successfully opens the pdf. When I attempt close the PDF, nothing happens and I get the following Error when i go back to access:
The operation on the Acrobat Document object failed.
The OLE Server may not be registered
To register the OLE Server, reinstall it.
Hit Ok and the PDF file closes. ANNOYING AS H#LL.
I have repaired access, and acrobat. I have reinstalled. I have checked registries. I have updated .dll's. The only help I can offer is that when opened on an IT guys computer the problem seems to be gone. I'd bet this is because he has a pro copy of adobe. Basically I'm trying to figure out if i need to convince the powers that be to put a copy of Pro on any computer using the DB (if thats whats really helped), or if there is still some way I can fix this myself. Thanks gentlefolk! You (plural) are the rule.
I am trying to merge data with MS Word, but am getting problems. I
only seem to be able to merge a single record. Can anyone help pls as this
is quite urgent
Thanks
barnettpaul@gmail.com
Forms used:
frmAnnualService
frmPostCardback
Tables used:
tblAnnualService
tblCustomers
I have a database created containing 2 tables. (tblAnnualService and tblCustomers). I have the Repair Order set as the primary key in both tables since it should never have a duplicate. I have a one to one relationship, enforce referential integrity and I have cascade updated related fields. The join type I chose is 3. Include ALL records from tblCustomers and only those records from tblAnnualService where joined fields are equal.
I have a couple issues ongoing at the moment. I have a button on the master form (frmAnnualService) used to input service information for each customer. This button runs and update query that updates tblAnnualService and all fields on this form are bound back to tblAnnualService.
With the setting on the relationship as listed above neither one of the tables is being updated. When I change my relationship and remove enforce referential integrity and cascade updated fields, change join type to 1. Change only include rows where joined fields from both tables are the same the tblAnnualService updates but the tblCustomers does not.
I have another form (a postcard mailer frmPostCardback) that is bound to the tblCustomers. I need to be able to from the frmAnnualService input the data, click the update query table button and have it update the tblAnnualService and the tblCustomers. I assume I do not have my relationship setup correctly. Can someone please assist? I some background with Access but need your comments to be specific and detailed so I can make sure I am correcting the issue properly.
Thanks in advance for any assistance I receive.
I Need to eliminate repeat entries of Names in a Combo pull down list
I have since found out that this is possible with the DISTINCT directive in a simple query like
SELECT DISTINCT [Name] FROM Miracle_Cloth_Main ORDER BY [Name] DESC; as the source
property of the combo box.
It looks like ACCESS needs A primary Key included in the querry for the source property
of the combo for the FindFirst function in the after_update event of the combobox
to work with a code similar to
rs.FindFirst "[RecordNum] = " & Str(Nz(Me![Combo112], 0))
The Wizard generate the following code even if you don't select the Primary Key(RecordNum)
during the creation process.
SELECT Miracle_Cloth_Main.RecordNum, Miracle_Cloth_Main.Name, Miracle_Cloth_Main.Cust_ID FROM Miracle_Cloth_Main ORDER BY [Cust_ID] DESC;
Even if you add the DISTINCT Cluse to the above code
it doesn't filter the repeat data.
Is there a compromise where I can get the Filtering by DISTINCT clause to work with the code generated by the wizard so that I get the FindFirst function as well to work ?
Hi there,
I'm creating a database for my computer repair business and it's going to store details on my customers, their computers, and any repairs I carry out on them.
I've created some tables, but I'm unable to create the relationships I require between the tables. Here's what I have:
http://img352.imageshack.us/img352/4724/helpbk3.jpg
I want to create a one-to-many relationship between the tables left to right, excluding the Engineer table, which I will talk about in a moment.
The idea is - One customer has "many" computers, one computer has "many" repairs, one repair has "many" problems. Each repair has just ONE engineer assigned to it, although the engineer will be assigned to many repairs.
I can link the Customer and Computer tables, via the CustomerID, but when I try to connect the other tables, I get the "Relationship Type: Indeterminate" error.
Can anyone help me with what fields I need in each table, and what relationships I need to achieve what I need?
Many thanks :)
Paul
My friends,
I am trying as hard as I can to learn Access. I am developing a database for my signage design business. I've been at it for about 5 days already. I know this question would be a piece of cake for the experts in this forum.
When one of my artists finishes with an artwork, he will save this as a jpeg file. I want him to upload it to the database via a form.
1. I would like to come up with a command button that, when clicked, will open a browser on the users remote computer and enables him to find the file to upload.
2. When this file uploads, I want it to be saved in another directory, outside the db, and then linked to a field within the db.
3. And I want this feature also for mp3 files.
Can any one help with this?
Here is a challenge for the best of you!
I have a cattery system which will be using the following tables:
tblCAT tblVisit and so on
I need to be able to check booking availibility. NOTE: THIS IS NOT THE SAME AS A HIRE-SERVICE SYSTEM. I.E. I need to be able to check the following:
1. For every day of a cat's intended visit...is there less than 40 cats @ the cattery
FOR EXAMPLE:
At the moment I can search to see how many visits will be taking place on ONE particular day. So the challenge is:
HOW CAN I REPEAT THIS QUERY TO CHECK HOW MANY VISITS ARE TAKING PLACE ON EVERY DAY OF A CATS INTENDED VISIT.
*At the moment, my querey can only search for the total number of visits taking place on one day...I need to repeat this as mentioned.
THANKS EVERYONE :D
Here's a tricky little problem I am trying to figure out. I'm sure someone out there should be able to help me without breaking anything. Heres the problem.... I have a form that has an underlying table attached. I have a combo box pull in a pull down list of locations from that table. What I would like to happen is, I would like it so the user will select a location from that combo box, and then click a command button. When they click that button, I need whatever they chose in the comobo box to copy over to a NEW table, which will be referenced later by my program. Say the table being pulled from is called "table1", and the table I need the info to go to is "newtable". I guess it doesn't SOUND hard, but I can't seem to figure anything out on it. I'll let you experts tear it apart and tell me how dumb I am for not knowing. Be kind... I'm still a noob. :D
View 6 Replies View RelatedHi,
I have been thinking about this since two day and tried everything, but no luck.
I have a form with recordsource a query. A listbox contains all references and if you choose a reference, everything else on the same form gets updated. Now i want to avoid that two users edit the same record at the same time. So i thought i would make an extra field in my table with a checkbox "yes/no" wich says if the record is in use or not. Now whenever a user edits a complaint i want that when another user is trying to edit the same record a message pops up saying that the record is in use. I can get it to work, but only have the problem that when a user decides to change reference number in the listbox and call upon another record, the value needs to get to fals again on the record he was editing.
Maybe there is another way to now if a user is working on a record.
Anyone that can help with an easy solution on this?
Hi Everyone.
I am building an app which will be used to administer a small company which has several mobile service engineers.
Everything is fine. A fairly standard sort of job until the client asked for a screen showing each engineer for a given day with a bar graph showing planned start and finish times for each allocated job, together with the location! As an added challenge, I need the user to be able to click on the "Chart" and so open another screen showing full details of the job clicked on.
My first approach was to use a chart. Couldn't do it:confused:
The approach I have now used is to create a series of labels on the form "On the fly" to represent each job and the unallocated time in between jobs. I can also run a macro to display the full job details when the label is clicked upon.
HOWEVER, this is a very cumbersome way of doing things to my mind. In addition, sizing of the controls in each day's bar is far more difficult than I expected.
Anyone got any ideas as to how I might achive this task in a more polished way?
This is probably not too bad to most... Considering I'm still pretty new to this...
I'm designing a database to track training for about 6 offices where I work. I am having trouble trying to set up a questionaire that adds a worker's training requirements automatically when a new worker is added to the system.
TABLES
tblPersonnel
PersonnelID (Primary Key)
tblRequirements
RequirementID (Primary Key)
RequirementTypeID - Denotes the type of requirement based off of the question on the questionaire.
tblPer_Req (Join Table)
PersonnelID (Joined to tblPersonnel)
RequirementID (Joined to tblRequirements)
QUERY
In my query I have pulled the PersonnelID from tblPersonnel, the RequirementID from tblRequirements and the RequirementTypeID from tblRequirements.
FORM (part 1) - New Member Form
Enter the new member's information...
FORM (part 2) - Requirement Questionaire (Must bring over PersonnelID from FORM (part 1)...
I have asked the following question... (total of 10 questions, but we'll just go with one for now...)
"Does the member wear prescription eyeware?" (Yes/No - using an Option Group)
(RequirementTypeID = 2)
If the member answers yes, I want the database to automatically add the member's PersonnelID and all Requirements from tblRequirements that have a RequirementTypeID of 2 to tblPer_Req (Join Table).
So far, I have gotten the query to only work by itself as a SELECT query and was not able to add the information to tblPer_Req, let alone try to tie it together with the Option Group...
If anyone could please help me get this together I would greatly appreciate it... I've been trying to work this one through for almost two weeks, the boss has tried to help too but he's also stumpped!!!
Thanks!!!
Dear All,
I currently receive MS excel files with forty columns and 7000 rows of data. I Perform a sense check and then convert the file to csv for loading into another system.
I've had a request to start performing this task for files of up to 13 million rows. Obviouslt MS excel will not be capable of doing this.
Can anyone tell me what MS product I should use? Access, foxpro? or do I need a non MS product and if so which one?
Many thanks
Wayne
I am currently having the following difficulty. Let say i have 3 fields in a table.
Field 1: = ID
Field 2: = operation Number
Field 3: = Status
What should i do if i want the following result from a query.
Field 1:= Grouped by ID
Field 2:= Count of operation Number
Field 3:= "status1", "Status2", "status 3"(basically concatenating the strings in each group.
.:confused:
Please note that i am using Access 2003.
eg of how table wold look
Gr ID CountOf Operation Status
1299B 10 R
1299B 20 C
1299B 30 B
1299B 40 D
1299B 50 A
1299B 60 Z
1275A 10 P
1275A 20 Z
eg of an out put for my query should look like
Gr ID CountOf Operation Status
1299B 6 R,C,B,D,A,Z
1275A 2 P,Z
Please note that it is important that the order is maintained.... ie in the case of 1275A the order should be PZ and not ZP..
Apprecate any help
Thanks in advance :) :)
BACKGROUND
The current challenge I have in MS Access in really giving me a headache. I can’t seem to find an answer or indeed think “outside the box” so I am hoping there is someone who can help.
Below I will outline the design of my database, the problem I have and my proposed solution. My solution is “What I want to do” rather than “How I do it”. If you have the time and patience to look at my problem and suggest how I proceed, I will be really grateful.
CURRENT DATABASE DESIGN
I created a database that imports daily telephone data. The design below is a simplified version of the real thing but it contains the essential information needed to understand my database.
I extract data from the phone system for “Lines” (3 digit code) e.g. ‘301’,’302’ which each have a corresponding line “Description” e.g. ‘New Customers’, ‘Accounts Queries’. There are three main daily extracts (1) Inbound Calls (2) Outbound Calls and (3) Time. Each extract has either “Line” or “Description” as the unique identifier but not both.
There are 50 lines with matching descriptions and each is allocated to one of 20 teams. I have a query which links all three extracts, groups the data by team and date. Therefore this query creates 20 records (teams) for each day (date).
CURRENT QUERY AND TABLES
Tbl Line Lookup (50 records)
Line
Description
Team
Tbl Inbound Calls
Line
Date
Offered Calls
Answered Calls
Abandoned Calls
Tbl Outbound Calls
Description
Date
Outgoing Calls
Tbl Time
Description
Date
Answer Time
Abandon Time
Talk Time
Wrap Up Time
Qry Grouped By Team Daily
Line – linked to line in Tbl Incoming Calls
Description – linked to description in both Tbl Outgoing Calls and Tbl Time
Team – Grouped field
Date – Grouped filed
Offered Calls - Sum
Answered Calls - Sum
Abandoned Calls - Sum
Outgoing Calls - Sum
Answer Time - Sum
Abandon Time - Sum
Talk Time - Sum
Wrap Up Time - Sum
PROBLEM
Customer Services “Team” is made up of six lines (301-306) and Business Partners “Team” is made up of three lines (307-309). For each day my query creates a record for each of these teams based on how the line is allocated in Tbl Line Lookup.
The business had decided that line 304 calls are part of the Business Partners with effect from 01/05/06. If I change the team name associated with 304 in Tbl Line Lookup this will, incorrectly, allocate all information (from 01/01/06 to present) to Business Partners.
I need a mechanism to allocate 304 to Customer Services prior to 30/04/06 and to Business Partners after 01/05/06.
PROPOSED SOLUTION
I want to introduce a new field in Tbl Line Lookup called Effective Date.
Tbl Line Lookup (50 records)
Line
Description
Team
Effective Date
Then I want to introduce a new query which links the data in the three data tables and allocates the correct team name by comparing the date in these extracts with the effective date in the Tbl Line Lookup.
I would then run my original query (Qry Grouped By Team Daily) over this query instead of the original tables.
Please can someone tell me if it is possible to look up values in another table to determine the value of a field in the way I have described?
The scenario - I use MS Access to access an advantage database system from a program we use. I can either import or link to each table in the program's database via OBDC drivers.
The problem - I need to track the status changes of our customers. Tracking customer status changes are not recorded in the advantage db. The TRDATA table contains the information. From now on, I want my access db to track the changes and save it in access for report purposes. The status's available are A, B, I, K, L, O, P, W, X and Y.
The solution - So far, I have queried the TRDATA table to pull the Account #, Buyer, and status. I made a crosstab query which would kind of aggregate the data into a readable form. If the account is active, there would now be a 1 in the column A. I then made another query which would then update a table. From here, I'm unsure of what to do. Obviously, there needs to be a comparison of some sort. If only account 1234 changes from an A to a Y, then I want a new table to show all the other accounts where they stand and a change of Y for the status with the date it changed. I hope I'm explaining myself well enough!! I’m very unsure of where to go from here.
I have a problem!
I have a table, tbl_Members.
tbl_Members contains many fields, two being SName and RefferedBy.
ReferredBy is stored in the table from a lookup from a form that looks up the SName field from tbl_Members.
What i want to do is create a query from this table with an extra field which counts the number of instances based on the records SName, in a field called NoReffers.
For example, i want a query that looks like:
qry_tbl_Members:
-----SName-----ReferredBy-----NoReffers---
-----Bloggs--------Hills-------------1-------
------Hills--------Barnard-----------2-------
-----Smith--------Bloggs----------- 0------
----Truman--------Hills-------------1------
---Williamson-----Truman-----------0------
I 've been looking into the Count() function, but cant seem to work it out!
Can someone help me?
Access 2000
Hi All
I am running a dcount
I need to count values that are between (and including) 3000 and 3499 the field is cmbLkpSCType
I also need to count values that are between (and including) 3500 and 3999
The cmbLkpSCType values are 3000 through 3499 the field is cmbLkpType
My challenge is to find a way of counting one and not include the other
If Me.cmbLkpSCType = "3000" Then
strBuild = Me.cmbLkpSCCountry & (Right([cmbLkpSCYear], 2)) & (Left([cmbLkpSCType], 2)) & (Left([cmbLkpSCBudgetUnit], 2))
strCt = DCount("strSourceCodeID", "tblProjectSegment", "(Left([strSourceCodeID], 8) ='" & strBuild & "')")
End If
'*****************
If Me.cmbLkpSCType = "3500" Then
strBuild = Me.cmbLkpSCCountry & (Right([cmbLkpSCYear], 2)) & (Left([cmbLkpSCType], 2)) & (Left([cmbLkpSCBudgetUnit], 2))
strCt = DCount("strSourceCodeID", "tblProjectSegment", "(Left([strSourceCodeID], 8) ='" & strBuild & "')")
End If
TIA
Detrie