Queries :: Change Layout Of Data
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 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
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 5, 2013
I have a form with a child that holds a query and for 99.9% of the time remains locked as it's for viewing data only. However, if the user wishes to make a change then they need to press an unlock button first then make the changes.
The problem I have is when closing the form; if this child's query has been unlocked I'm getting a message asking do I wish to save the changes to the layout of the child's query. The answer will always be "No".
I've added the following line of code to all sorts of procedures with no success.
DoCmd.Close acQuery, "LinkTableManager_Frmqry", acSaveNo
how to stop the message appearing when the form is closed.
View 5 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
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 3 Replies
View Related
Aug 13, 2013
I know that i can edit contents in a single table query and it will update the data in the underlying table, but how do i gain the ability to change data in a query when 3 tables are involved?
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 5, 2014
I am trying to change the button color on a subform if a related form data changes.Main form is products with a continuous subform with serial numbers of products i.e, serial number, location, price and a button to add addtional issues if there are any for this particular serial number (this will open up another form related to the serial number so I can add an issues if there are any).The reason I would like the button to be a different color is so I can quickly see if there are any additional notes been added to the serial number. Just in case you may ask why not add the field to the continuos form is that the issues and be quite lengthy and there may be lots of serial numbers on the form
2346 location warehouse price 29.99 (button - green)
2347 location shop price 29.99 (button - red)
View 1 Replies
View Related
Dec 10, 2014
I have a list of 20 hospital patients that I am trying to use to try and establish a list of supplies or services billed on Day of Surgery, Post Op Day 1, Post Op Day 2, Post Op day Three and Post Op Day 4. My problem is when I ran Crosstab Summaries on their accounts the column headings were the actual dates of services. The dates range from January 2014 to July 2014. Is there a way I can change the dates object to the text objects listed above and then merge the accounts so the items and quantities are tabulated. If I can't do this with Access I'll have to tabulate them manually by either creating a new table and data form or by setting up an Excel Spreadsheet.
Current Crosstab
Item # Description Total Qty Jan 1 Jan 2 Jan 3 Jan 4 Jan 5
201 Gauze 3 2 1
203 Misc 1 1
What I want is:
Item # Description Total Qty DOS POD1 POD2 POD 3 POD 4
201 Gauze 3 2 1
203 Misc 1 1
Keep in mind that there are 20 accounts with varying dates that have to combined into one table or query.
View 1 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
Nov 21, 2005
If the data source changes for an Access database, do I need to recreate all the tables, queries, and reports? I am having quite a time with this issue. Thanks for any assistance you can provide.
View 1 Replies
View Related
Nov 10, 2007
I'm using Access 2002 and need to increase some field sizes, some by increasing the maximum number of characters and others by changing from Text to Memo. When I try to do this I get the message "Microsoft Access cannot change the data type. There isn't enough disk space or memory"
I have two 320GB hard drives and 4GB of RAM and the Access file is a mere 280MB. Can anyone please explain the problem and tell me how to achieve my objective?
Roger
View 6 Replies
View Related
Jan 26, 2007
Does anyone know if its possible to change the data type of a field from "Text" to "Number" by using a macro?
thx
View 1 Replies
View Related
Oct 16, 2013
I finally found and eliminated all duplicate entries in my tblStoreProducts table, so that I could create a 2-field Index called UniqueProduct that would prevent duplicate entries. Without it, uploading information from Excel (not the preferred method) would result in duplicated entries in this table which would ultimately reflect in the Hazardous Materials reports that must be submitted to the fire marshal.
Today I was going through data entries in the Product table and noticed some that are STILL coming up with the UPC (a text field) in Scientific Notation.
I know that I have to change the information in the linked table tblStoreProducts first. The most simple way would be to just change the UPC of the product in that table to the correct UPC. It shouldn't be causing a problem, because it doesn't create a duplicate record. I'm getting the error "The text you entered isn't an item in the list" now that I've created that index.
I changed the index to Unique = No and still get the same error.
I removed the index all together...still get the same error.
I'm making the changes directly in the BE database right now. If I can't enter information directly, the forms aren't going to work either.
View 3 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
