How To Show The Data In Such Layout
Jul 9, 2007
I have this very important question, I dont know how to do it but I am sure Access support this and it is doable.
I have the following recorset:
Name LeaveType LeaveDate LeaveYear
Frank Annual 25/06/2007 2007
Frank Annual 30/04/2007 2007
Frank Unpaid 11/09/2004 2004
James Unpaid 04/03/2006 2006
James Unpaid 19/09/2006 2006
Meco Paternity 17/12/2004 2004
Meco Paternity 18/12/2004 2004
Meco Annual 02/07/2006 2006
Paolo Annual 25/06/2007 2007
Paolo Annual 12/06/2007 2007
Paolo Annual 24/06/2004 2004
I want the data to be shown in a report as follow:
Name: Frank
Leave Type200720062004
Annual200
Unpaid001
Paternity 000
Name: James
Leave Type200720062004
Annual000
Unpaid020
Paternity 000
Name: Meco
Leave Type200720062004
Annual010
Unpaid000
Paternity 002
Name: Paolo
Leave Type200720062004
Annual201
Unpaid000
Paternity 000
I WILL HIGHLY APPRECIATE IF SOME ONE HELP ME DO THIS VERY IMPORTANT TASK.
REGARDS,
View Replies
ADVERTISEMENT
Feb 22, 2007
Hi there,
I have a warehouse table that lists a stock code with the last 12 periords closing balances in a record.
I would like to write a query that just shows each stock code with only one month per record and has the openning and closing balance for that month.(the closing balance of one period is the openning balance of the next period).
So the Table currently looks like this
StkCode....Whouse....12.....11......10.....09..... .08.....07
1111.........TZ..........100....200....300...400.. ...500...600
I would like it like:
StkCode....Whouse....Period....open....close
1111.........TZ..........12.........200.....100
1111.........TZ..........11.........300.....200
1111.........TZ..........10.........400.....300
1111.........TZ..........09.........500.....400
1111.........TZ..........08.........600.....500
I haven't worked with access for years and can see how to do this in my head - i just can't figure out what to write. Any help would be greatly appreciated.
View 3 Replies
View Related
Mar 4, 2014
I have been given a large set of records that is layed out with 10 ID fields, then 31 (Day 1, Day 2, etc.) fields representing each day of a given month. These fields contain data. I created two fields labeling the month and year as well.
My goal is to create a date field where I can put together the m/d/y data and take the corresponding day's data and delete the other 30 daily fields. I think I can temporarily create 31 extra fields that give me the date. So then I would have (m/d/y - Day 1, m/d/y Day 2, etc.). How to then separate them into unique records.I am looking to turn the 30k records i have into 900k by reducing the fields.
View 1 Replies
View Related
Nov 25, 2004
I'm getting seriously frustrated with Access now so I hope some of you can help me out.
I have made an access application to enter various types of products (eg paint, tools, glue etc).
The organization I have built this for has asked me to create a report which can be used as
a catalogue of all their products.
They want this report to keep different categories of products in different sections. So say the paints
will go into a section with an orange background and the tools have a blue background. I thought
I could just let it print a number of reports (one for each type of product) however this will give
problems with the page numbering and indexes etc.
Anyone have any ideas how I can let the lay-out (mainly colors) change if a new category kicks in and also
force this category to start on a new page? Any help is very much appreciated!
Cheers Thomas
View 1 Replies
View Related
Jun 24, 2013
Export Query Data with Formatting and Layout Using A Button On Form Before Or After DoCmd.TransferSpreadsheet
View 4 Replies
View Related
May 12, 2013
I have made an unbound check box, named "myckeck" in a form which shows data from a table in tabular layout.
1- When I check one check box, all check boxes are checked. How can I solve that? I mean I want to check or clear check boxes independently.
2- I made a text box, named "jobdate" to sow todays date automatically upon checking a check box.
Code:
Private sub mycheck_afterupdate()
jobdate = date
End
But this is not working and checking a check box does not make anything to happen.
3- Then I made this expression in the default value of "jobdate" properties:
iif( mycheck = true, date(), null)
This not working either.
View 3 Replies
View Related
Apr 16, 2014
I have data from a survey with qualitative responses. For a single qualitative question, I moved the ID & responses into a new table and categorized the response according to a bucket/theme, where each column is a new bucket. I now have 10 columns. Each response is represented in 1 or more columns. I used an excel formula to copy the response data into the column itself.
Example:
A1 // B1// C1 // D1// E1//... L1
ID // Response // Cats // Dogs // Elephants //.... Column 10
1 // I like cats // I like cats //(null)//(null)// ... (null)//
2 // I like cats and dogs // I like cats and dogs // I like cats and dogs //(null)//..//
3 // etc.
However, now I'm realizing that Access always wants to show data for all records, or at most I can limit using a WHERE clause in my query.I want to use Access to generate this report:
1. Section 1: Show all responses from the Cats bucket where there is data
2. Section 2: Show all responses from the Dogs bucket where there is data
3. and so on
I know how to do summary values, and I know how to do filtering that apply across the whole report, but this seems like more advanced filtering, where I want to see selective details differently for each field.
View 3 Replies
View Related
Oct 26, 2005
I have subforms that are datasheet view, if the user rearranges the coloumns, or resizes etc , I would like to save what they have done so it is as they left it next time they open up the screen.
This works fine with an mdb (all my forms are set to close acsaveNo).
However in an mde this doesn't work.
Can I get this to happen in and mde.
(A button that saves a layout change would be fine for my purpose)
Thanks
Paul
View 1 Replies
View Related
Mar 16, 2006
Hello Everyone,
I'm pulling my hair off with this situation. I have an excel file that was given to us by the payroll department of our company, but my manager doesn't like the layout in which was given to us. I've attached a sample of the file, the file has two sheets the first one is how the original file looks like and the second sheet "final look" is how my manager want the file to look.The original file is pretty big, it's for 33 different employees and it contains 93,500 rows. I think it will take forever to do this manually, any ideas would be really appreciated.
Thanks,
Pablo
View 3 Replies
View Related
Mar 1, 2007
I had a couple of forms go corrupt in one of my databases this morning, which has around 50 or so tables in it. I repaired the database by creating a new db then importing all the db objects from the original and the 2 corrupted forms from yesterday's backup.
However, the relationships table is now a disorganized mess. Is there some way to import the layout of the relationships table from the original db or am I stuck with manually re-arranging?
View 4 Replies
View Related
Nov 20, 2007
Hi all,
How should I do if I want to display my records in Report by 3col x 5row instead of 1col x 15row.
View 3 Replies
View Related
Jan 13, 2005
I have an issue with a database layout and I am not sure which is the best way to proceed.
The problem is in an invoice part of a db . When a service is delivered, say 2 hours of a technicians time, it has to be assigned to a particular tech. No problem when there is only one tech however there could be two or more techs who have helped to supply this particular service and the time ( and income) needs to be divided up between them. However the invoice can only show one item the ie total time taken to deliver the service ( quite likely to be a fixed cost for a particular service ).
Possibilties I have considered are: to have a number of fields in the invoice detail table , one for each tech, however I would have to assume a maximum number of possible techs per job and also there would be a lot of redundant fields and trying to analyse how many hours each has worked would be painful.
Each tech could be assigned a code ( single number or letter ) and these could be stored in a single field . The code would then have to be manipulated to work out how many techs worked on each item and how much each had worked. Again this is rather messy.
I think the ideal would be for each tech to have their own table where each time an service was invoiced which they helped delivered the appropiate percentage is assigned but I cant quite see how to do this.
Any ideas or examples gratefully recieved
View 1 Replies
View Related
Apr 11, 2007
17099Good Morning
I was wondering if someone can please assist/advice. I am currently a bit stuck and feeling like I am walking into a dead end the whole time.
I am currently creating a DB with the purpose of Capturing Potential Candidates provide them with an ID. Fill in their basic details Name Surname, submit to a report, which I will then export, email to them, they will fill in the outstanding information requested, send it back and will import it back into the DB with my various queries to run searches on candidates for placements.
My only problem is that I have quite a lot of fields...
So I Decided to Create 4 Tables
In each table I am using the same ID for a person so should be a 1 to 1 Relationship.
1 General Information
2 Info Relating to Skill
3 Work History
4 Qualifications.
I have all of these separate tables due to the fact that with all the records in one i exceed my 255 table fields allowed
But then if I decide to create a query so I can run some of the details i get errors because there are to many fields. Even if i run one on the other. My last one will still have to many fields.
Basically I am hoping someone can just assist in what would be the best way for me to lay out my tables. And what would be the best to link these tables.
Is it better to have all the records horizontally, or as a one to many with duplicates.
Please find attached a copy of the tables. I would really appreciate any advice
Regards
View 3 Replies
View Related
Aug 12, 2006
Hi,
I created a sub-form using the wizard; however, I can't get it to view as Calumnar even when I move its text boxes about and change their order. When I run it, it always show as a datasheet!
Any suggestions will be very much appreciated.
Bee
View 2 Replies
View Related
Jun 18, 2005
Is there a way of putting the checkbox to the right of the text other than dragging it over?
View 1 Replies
View Related
Jul 5, 2007
:confused: Running Access 2000. Have a table with Employee number and data, another with training (number ID's) that also has the Employee number.
Therefore the employee number is shown numerous times throughout the table.
What I want to do is bring up a list of employees who have NOT got a specific training ID only.
Hope this makes sense.
View 1 Replies
View Related
Aug 19, 2005
Hi.
I have a subform (based on table "Source") that allows the user to create a new record in another table "Events" (it copies some entries to facilitate entering new data). The data should then be dispayed in another subform. However, the newly created redord is does not show in the subform although I use Me.Refresh. When I close the form and then open it again it works - but there has to be an easier way.
How can I reload the data from the table. I have looked but am unable to find the right method.
The code:
'Create New Record in Table Events
Call AddEvent(EventDate, Country)
'This function opens the table and adds a record
'Open Subform
DoCmd.GoToControl "subfrm_events"
DoCmd.GoToRecord , , acLast
Me.Refresh
Help would be much appreciated!
View 1 Replies
View Related
Jun 27, 2005
I am working on a complex database to store Business information as well as specific into about that Business... here is the tricky part...
The individual companies are sorted by Business type (i.e. Computer Software Companies, Child Care Providers, Commercial Building Contractors, etc.) and each Business type needs to hold a simi-customized set of information (i.e. Commercial Building Contractors needs Total AZ Billings for Commercial Bldg., $ Awarded for Completed Contracts, % of Work Subcontracted, No. of Local Employees, and Areas of Specialty). I will refer to each type of information as a Category. Each Business Type has between 4 and 7 Categories. Within a Business Type there are between 10 and 30 companies listed. There will be just over 200 Business Types listed within my database structure, as well as around 100 unique types of Categories.
Primarily, I need to be able to query and format each Business Type, to include the Companies information as well as each Category that is associated with the Business in a flat spreadsheet style layout.
Now, I intend to create a unique report (if needed) for each Business Type as needed to customize the look (i.e. column widths and any other special formatting needed) of each Business Type,
Here is where I stand currently with this project…
I have 4 tables,
1. Business (it includes basic contact info that is common to all business)
2. Category (is basically a list of all possible categories)
3. BusinessCategory (a Many-to-Many relationship container that links the BusinessID to the CategoryID and includes the corresponding Value)
4. BusinessType (a list of business types that a business can belong to)
This structure seems to work in that it can handle the customization of categories for each Business within a BusinessType. The problem I am running into with this structure is creating a query to handle the data and create a “flat” table for a BusinessType with each Business and it’s categories.
It may be that I can use this structure to do what I need done, however I think that there must be a better way to structure my data to work the way I need it to work. I really appreciate your input on this!
View 2 Replies
View Related
Nov 15, 2005
I have designed a few databases for my work. I have been recently assigned a new one that has me a little stumped. I am looking for some suggestions as to how to go about this.
Essentially, we have a training department that does training in the various aspects of the job. In the various classes, the "students" take a midterm exam to gauge the level of retention of the training midpoint in the class. Then at the end of the class, they need to take a final exam.
My department director would like the "students" to be able to take the exams using Access, since 99% of the questions are either multiple choice, yes/no, true/false, or fill in the blank, and Access would be able to "auto-grade" the tests, essentially.
I am at a loss, basically, as to the layout. The "student" information table would be the easy part. I am unsure of how to create the tables that would have the questions, depending on the class and whether it is a midterm or final, with the answer choices attached.
Any suggestions?
View 1 Replies
View Related
Sep 7, 2006
Ive been using the form wizard, with columnar but I find it squishes up some of the words, and doesnt use the whole screen width, is there a way I can change this? and is there anywhere that you can download more styles?
Any thoughts would be awesome.
Cheers Ezy
View 1 Replies
View Related
Nov 7, 2006
ok, i have a few design requirements and am unsure as the best way to approach the situation..
here is the basic layout of my tables..
http://www.londonheathrowcars.com/bait1.jpg
i will basically need a form with the following layout
http://www.londonheathrowcars.com/bait.jpg
you enter the date at the top of the form.. and then all the records you enter will use that value as the job date..
also.. the customer name and number will need to be entered in this form.. and the customer id will have to be registered to the job in the job table..
can anyone suggest where i should start with regards to creating a subform.. a query.. etc??
View 3 Replies
View Related
May 2, 2007
Hello everyone.
Im just wondering if anyone can find any problems with my table layouts or relationships are anything else?
The ER-D can be found here:
(URL address blocked: See forum rules)=17320&d=1178117475
Thanks, Ant...
View 3 Replies
View Related
Aug 24, 2012
Views show design view, datasheet view, pivot table view, and pivot chart view. Where did layout view go, and how do I replace it?
View 3 Replies
View Related
Jul 29, 2005
Hi,
I'm wondering how to do the following:
I have two combo boxes that exist on a form record (i.e. two combo boxes for every record on the continuous form): mainCategory and subCategory.
Currently, I can choose a mainCategory and depending on the mainCategory, I have certain values available for the subCategory.
What I want to do is only show the values that have not been used for the subCategory (the mainCategory can always show its default value).
An example of what I mean is below...
The comboboxes and their row data:
mainCategory (combobox)
-------------
A (combobox rows)
B
C
subCategory (combobox)
-----------
for A: 1, 2, 3 (row for the selected mainCategory)
for B: I, II, III, IV
for C: red, blue
Sample scenario:
1. For the first record on the form, a user selects "A" from mainCategory and "1" from the subCategory for "A".
2. The user adds a new record to the form
3. For this new (2nd) record on the form, a user selects "A" from mainCategory and can only select "2" or "3" from the subCategory combobox. The "1" from the subCategory combobox is no longer available for choosing, unless the user changes the first record's "1" in subCategory to another value.
Any help would be much appreciated.
TIA!
View 4 Replies
View Related
Aug 14, 2005
I have the following table:
Blast NoHole NumberHole Depth TapeBCM/MORE mORE bcm
1.002007.909.757.9077.03
1.002008.209.758.2079.95
1.002018.009.750.000.00
1.002018.109.750.000.00
1.002027.809.757.8076.05
1.002037.209.757.2070.20
1.002047.909.757.9077.03
1.002057.709.757.7075.08
1.002062.809.752.8027.30
1.002077.409.757.4072.15
1.002087.809.757.8076.05
And i want to show only the last occurance of the Hole Number field. e.g:
Blast NoHole NumberHole Depth TapeBCM/MORE mORE bcm
1.002008.209.758.2079.95
1.002018.109.750.000.00
1.002027.809.757.8076.05
1.002037.209.757.2070.20
1.002047.909.757.9077.03
1.002057.709.757.7075.08
1.002062.809.752.8027.30
1.002077.409.757.4072.15
1.002087.809.757.8076.05
However I cannot figure out if the LAST function dose this. When i have applied it hole 202 & one of the 200's dissapeared. However 202 should not, but 201 should??
View 10 Replies
View Related
Nov 7, 2007
I am really new to using access and I have a massive table 16000 or more entries. What I need to do is take this example data :
VPIAddress
001:99_97_AO
001:99_96_AO
001:99_0_AI
001:986_51_CI
001:986_50_CI
001:985_51_CI
001:985_50_CI
001:984_50_CI
001:983_51_CI
001:983_50_CI
(This is just a tiny fragment of the total data)
The 001: to the first '_' is the important bit this is defining a "Router" what I need to do is total up how many '001:*_' there are, not including duplicate numbers.
Firstly if I understood this (http://www.access-programmers.co.uk/forums/showthread.php?t=92001) post correctly I need to have all the VPIAddress the same. If this is correct is there a way to get access to delete the data after the first '_' ? (I would of posted in this tread but i didnt think that would be right, if im wrong mods move the post :) )
Secondly will the info in that post help me to reduce the data into this :
VPIAddress
001:99_
001:986_
001:985_
001:984_
001:983_
So that I can then see how many Physical "routers" there are
If any one can help me and tell me that my idea will work then tell me how to do it id be extremely happy!
Even if any one says that wont work and gives me the correct way to do it and does a virtual "Clip around the Head" and call me a Daft Bugga I would still be happy because I wouldn't have to trawl through the entire 16000+ entries deleting and modifying.
View 4 Replies
View Related
