Help Stamp Out Brain-Death: Cross-Tab To Make Table
Aug 28, 2006
Hi -
Desperately need a kick in the butt to restart the cognitive functions.
Am attempting to prepare a FY (1 Jul 05 - 30 Jun 06) rollup of services provided. Have done this for years with very little stress. Lost my notes in a melt-down and now it seems I'm lost.
Situation:
Properly normalized recap of various (about 15) services provided to clients over the one-year period. Created a cross-tab query that sums each of the services and displays columns for each service (represented by ServiceID, e.g. 2019, 2020, etc.)
The cross-tab works perfectly, just now need to turn it into a report. Try to use the Report Wizard, based on the cross-tab. Problem is, it returns no fields to select. Changed strategy and attempted to turn the cross-tab into a make-table query, which, I understand would return a spreadsheet-style table. For these purposes, that's exactly what I need and have examples going back 5 to 6 years how this worked wonderfully.
The cross-tab -> maketable works, but it reverts to the original normalized structure, which is not what is needed.
If someone can help to put me out of my misery, I offer my first-born as a small token.
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).
I am trying to add a user stamp in a table. I have seen online that perople put a macro (Before Change) in microsoft access 2010 but I dont understand how to use that.
I have seen you are suppose to do something with = Environ("username")
I have a small problem..... I hope u 2 help me solving it..... I cross tab query, with this column: PIVOT Format([Date],"Short Date") But as we all know that Format function returns variant(strin), So how can we reorder that column by date... Sorry for bothering you....
How do I create a table that can cross index items in another table. Maybe I am not using the right terms here so let me show a small example.
Say I have a tables of words. tblWords numWordID txtWord
Then I have some entries, all more or less synonyms of each others fresh new clean
Now I want to create a cross-index table, related to the table "tblWords" where I can select synonyms from words already in the table "tblWords", so if I for the word "fresh" add "new" and "clean" as synonyms or entries, if I then go and look at the word "new" it will already have the synonyms "fresh" and "clean", likewise the entry "clean" will then have the synonyms "fresh" and "new".
Kind of a many to many relationship junction table but only with one table!
I hope my explanation have not been to confusing, but let me know if you need a clarification.
I have a lookup form that is basically one combo box that holds my user names (Combo0). I need to use the afterupdate event to open my form (frm_tc1) and filter that users records. If no records are found I need it to prompt me to add a record. If yes is selected I need it to open frm_tc1 in add format, and if no is selected I need it to go back to the switchboard menu.
Here is the code that I have so far:
Private Sub Combo0_AfterUpdate() 'On Error GoTo Err_combo0
Dim stDocName As String Dim stLinkCriteria As String Me![Combo0].SetFocus DoCmd.FindRecord Me![Combo0], , True, , True If MsgBox("User Record Not Found. Do you want to add a new record?", 4) = 6 Then DoCmd.OpenForm "frm_tc1", acViewNormal, , , acFormAdd, acWindowNormal Else DoCmd.OpenForm "frm_tc1"
End If
End Sub
What am I missing? It is always prompting me to enter a new record, even if there is already one in the db.
FieldMinibus / FieldDate / FieldCount A 10/4/2013 1 A 11/4/2013 1 A 12/4/2013 1 B 01/5/2013 1 B 02/5/2013 1 C 01/8/2013 1
I can do a pivot on it no problem to get for instant the first and last dates for each minibus I am however wanting to produce a cross tab from the above that produces a format similar to the following
RowMinibus / Listed Dates A 10/4/2013 / 11/4/2013 / 12/4/2013 B 01/5/2013 / 02/5/2013 C 01/8/2013
I'm basically only wanting two columns in the related cross tab query result and the "value" within the cross tab needs to be a list of concatenated dates.
I can get all the dates to appear for an individual bus but they are spread across independent columns which results is not concise enough for my objective.
I am creating a production type database with different stations (assembly 1, assembly 2, etc) with a master parts list that has every part. I want to cross reference each station input with the master parts list in order to make sure the part is in inventory. Can I create a button or to do this on the form? Or is there a better way? I would cross reference two fields.
I have this cross table for a query called OUTPUT. The query has four fields (country, indicator, value and date). I want to cross table by date as follows:
Code: TRANSFORM Sum(OUTPUT.value) SELECT OUTPUT.country, OUTPUT.Indicator FROM OUTPUT GROUP BY OUTPUT.country, OUTPUT.Indicator PIVOT OUTPUT.[date];
This works fine but I'd like to visualise the date formatted by quarters instead of dd/mm/yyyy. I have modified my SQL code as follows:
Code: TRANSFORM Sum(OUTPUT.value) SELECT OUTPUT.country, OUTPUT.Indicator FROM OUTPUT GROUP BY OUTPUT.country, OUTPUT.Indicator PIVOT "Q" & DatePart("q",OUTPUT.[date]) & " " & Year(OUTPUT.[date]);
My problem is that using the DatePart and Year functions I miss the sorting.
Is there any approach that allows me to keep the date sorted in the cross table?
I have also tried to include the date formatting in the OUTPUT query and to sort the date there. It works fine there but when I create the cross table I again loose the sorting.
I tried the idea sugested in post (http://www.access-programmers.co.uk/forums/showthread.php?p=423080#post423080) but it didn't seem to give the cross referencing that I had hoped for .... example 1 is related to 4,3, and 5 while 6 is related to 1 thus implying an extended relationship to 4,3, and 5.
In a standard one-to-many I'd look at record 1 and see that it is related to 4,3,5 but if I were to look at record 6 I'd only see that it is related to 1. How would I set up the table relationships to drill further to see that 1 is also related to 4,3,5?
How do you do a many-to-many from one table back to that same table?
tblEvent EventID(AutoNumber) EventTitle(Memo) 1 memo content 2 data 3 more memo content 4 some info 5 more stuff 6 other text
I am trying to create a cross join or Cartesian product TABLE, not a query.
I am creating a training database. For each and employee and each training event, I want to know - is this event required, who approved it, and when was it completed. The table I envision looks like this:
Code: EMPLOYEE_ID EVENT_ID REQUIRED APPROVER EVENT_DATE 1 1 Y WPD 9/5/14 1 2 N
I currently have 39 employees and 473 events - 39*473 = 18447 records
I was able to make a cross join query and use make table, but whenever I add a new employee or event, if I update the make table query I will lose all my existing data.
There are multiple employees on a table, each with a period of time for which they worked. I am trying to gather these times together to see how many complete records I have for the time period of 4/1/1999 to 12/31/2004. So, for example,
Employee 1 works from 4/1/1999 to 12/31/2000.
Employee 2 works from 1/1/2000 to 12/31/2004.
(From these two records, there will be 1 complete record of 4/1/99 to 12/31/04 AND 1 remaining record for the year 2000 left over.)
Employee 3 works from 1/1/2000 to 12/31/2004.
(From these three records, 1 complete record + 2 2000 + 1 each for the years 2000-2004.)
Employee 4 works from 1/1/2001 to 12/31/2003.
(= 1 complete + 2 2000 + 2 2001-2003 + 1 2004)
Employee 5 works from 4/1/1999 to 12/31/1999
(= 2 complete + 1 2000 + 1 2001-2003)
And this goes on for all the employees. I need to find out the end amount of how many complete records and remaining dates all the employees have worked. I hope this was at least, somewhat clear. I remember doing something similar to this ages ago in programming class, but since then, my mind has degraded. =P Any help, insights, or advice would be greatly appreciated on how to get this done. =)
I would imagine that reading through someone else's normalization problems is as exciting as watching paint dry.
But I have seen some brilliant answers presented here so I'll give it a shot. I'll try to be brief and concise. I'm hoping someone can read through this and tell me if I'm on the right track.
I have a table full of employees. A few of these employees are supervisors. I was going to add a field called Supervisor to identify who each employee's supervisor is. I thought about just entering the Supervisor's SSN into that field. (SSN is the key field)
But every time a supervisor is promoted and replaced (quite often), every employee's Supervisor field would have to be edited. Sounds like I ought to create a seperate table with a list of supervisors, so I can replace the SSN just once.
I THINK this new table would have two fields, let's call them CrewCode and SupervisorSSN. So there might be, say, 15 employees in a particular supervisor's "crew", and all these employees' "CrewCode"s will point to their supervisor in this new table. When the supervisor is replaced, you just type in the new guy's SSN once.
Sound correct so far?
Here's where I am overcomplicating things for myself. I don't want it to be necessary for someone to arbitrarily create unique "CrewCode"s, because there are hundreds of Crews in this company but also because it feels wrong to do this arbitrarily when we already have data in other fields to uniquely identify the employee's crew.
Consider this:
The employee record has the following fields to identify which crew they work in. (I'll provide some possible values for these fields, to help put things into context.)
Region = {SE | E | NE} Division= {Sawmill | Woodlands} Site = {Atlanta | Charlotte | Axton | Foxboro} Department = {Sawmill | Planermill | Treeplanting | Harvesting | Trucking | SupportStaff} Crew = {1 | 2 | 3 | 4}
So if I string these values together, employee "John Smith" might work on a crew which is uniquely identified as:
SE.Woodlands.Atlanta.Treeplanting.2
No other crew in the company has that unique string of characters. (I would add the dots myself for readability.)
So my first question is, am I on the right track by trying to link employees with their supervisors using a table like:
That seems like a more elegant solution than arbitrarily assigning a CrewCodes of "00001", "00002", etc.
Second question: when the time comes to actually look up an employee's supervisor, the two tables would have to be JOINed in some manner. Is it possible (and advisable) to perform a JOIN using a collection of FIVE fields in the employee table? Or would I have to combine then into a new single field first, then JOIN on that field? I'd really like to avoid doing that.
Im just wondering about a date stamp which is in one of my queries. The database which i am working on is for a fuels company, one of the tables they have is a runout table, obviously for when fuel runs out at a station and also contains sales history. They have used a date stamp, so they can see how many runout were recorded each day.
My user now wants to be able to see what the previous 'date stamps' sales were, which is DATE_STAMP-1. I was just wondering if this was actually possible, i have tried several different ways of adding the criteria to the DATE_STAMP fields but it doesnt work and have got no where. I would be grateful of any help.
I have a CSV file that is going to be imported into a DB. the CSV file has a field that has a date & time stamp and I need to be able to store this file as 2 seperate fields.... the field would look something like this:
5/10/2006 17:00
I would like to store the datestamp and possibly the timestamp but the timestamp is not very NB!
I update, import and export tables, problem is I need a way to track what I have done or allow someone to pick up where I left off. Is there a way for MS Access to return the Create or Modified Date and time stamp of any table? :eek:
I have done this in the past but it has been sooo long since I consistently used Access that I am in a fog:confused:
How can I create a timestamp or series thereof, so that each time data is added to a Memo field there is a date time record created? My goal is to allow people to enter notes but have the timestamp automatically either append itself to the note OR timestamp a seperate field? Open to ideas...
I have searched for the answer but just can't seem to find the answer...
I have a form with a combo box field called Transaction Status with two values to choose from.."Approved" and "Rejected". I also have an "Approved Date" field.
When a transaction goes into "Approved" status, I want the Approved Date field to be date stamped with that days date. Currently in my Approved Date fields Control Source I have the following formula "=IIf([Transaction Status]="approved",Date()).
The problem is when a transaction gets approved the Approved Date comes up with the correct date like it is supposed to but the date does not seem to be fixed, it changes as with every new day.
One thought I had is to put this formula into the "defauld value" property, could that work?
Does anyone know how to put a checkbox on a form and when the check box is changes from blank to checked it puts the current date in that field? Can this be done by formatting the properties or create an event? Or does VB Code need to be written? Any assistance would be helpful.
I been through about 15 different time stamp threads and none of them seem to get to the point. I have a texbox on my form where a user can enter a comment. I would like a timestamp just to be on the form next to the box so anyone looking at it can tell when the comment box was updated. Is this possible in Access? Someone Please point me in the right direction...
I been browsing and I seen a few statement regarding a timestamp that displays when you create a new record so each new record would have the time it was created. What function is this?
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?
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?
I have what is probably a stupid question but I've been struggling with this one for a while. I have an ordering database which has an Orders table (containing Order ID, Date, Supplier etc) and an Order Line table within which I have a combo box for Product name, supplier, price, VAT rate, Line price etc. At the moment, I have the order line table as a subform within the Order form (run from the Orders table). The problem I'm having is the subtotal and total fields. At the moment these are in the Order Line table as I cannot figure out how to get these in the Orders table. In summary, can I make a calculated field in one table that calculates values from another table (linked by Order ID)?
I'm trying to copy the structure of a table to make a temp table. I'm using CopyObject (which also copies the data). So when I delete the data from the temp table, it also deletes data from the source table. Is the data linked? It should just be deleted from the temp table. Below is the beginning of the code. I've stepped through, and at the last step shown, the data in the source table deletes.
Code: Dim strFile As String Dim temp As String Dim tbl As String Dim db As DAO.Database
