Help With A Semi-pivot,crosstab Type Thing

Aug 24, 2006

Hey all, I've been banging my head all day trying to find a method and have nothing. Perhaps someone could guide me towards the right path... Here's a generic simplification of what I have:

ID# - Friend
1 - Fred
1 - George
2 - Fred
3 - Bill
3 - Ray
3 - Scott

Here's what I'm hoping to end up with:

ID# - Friends
1 - Fred, George
2 - Fred
3 - Bill, Ray, Scott

or even this would get me there:

ID# - Friend1 - Friend2 - Friend3
1 - Fred - George
2 - Fred
3 - Bill - Ray - Scott

I'm dealing with thousands of id#'s and thousands of possible friend's names.

Any help out there? If I need to explain myself better, just ask and i'll write some more...

thanx for any help you can offer...

Jim

P.S. I'm only a lite-weight in programming. I've been trying with a thousand different query methods so far using temporary tables and anything else I could possibly imagine...

View Replies


ADVERTISEMENT

Crosstab/Pivot Query Issue...

Apr 30, 2008

I have a simple crosstab query. I have source as the row heading (basically source is just string category field) and Create_Date as the column heading. This gives me a count of the tickets for each source by day.
My Issue is that when Access breaks my Date/Time field into the column heading (Format([Create_Date],"Short Date")), it must turn the date value into a string, then sort that way, because my columns come out as so:

1/18/20081/19/20081/2/2008 1/20/20081/21/2008


Notice that 1/18/2008 comes before 1/2/2008… so it is ordering the columns as a string.
How do I get Access to order the columns by date?

Thanks,
Gary

View 1 Replies View Related

More Than 4 Tables? Gd Thing Or Bad Thing?

Mar 9, 2008

Hi, im currently under going a Driving school project database. I have the following tables, Client, Instructor, Lesson and Lesson Type. However i feel like i want to go into more depth, e.g. make tables like recording sections of theory and practical tests, etc.

Currently my database with 4 tables is in 3rd normal form.. but say if i had 10 tables linking via relationship in query, what normal form is this? Is this gd practice or bad practice? :confused::confused:

This has been going through my head lately and im wanting to get take alot into consideration for my project. This might be absurd question to say in the forum but hope anyone can help me and explanation for me.:o

Thanks and much appreciated! :)

View 4 Replies View Related

Modules & VBA :: Type Mismatch Report On-Open For Dynamic Crosstab

Nov 22, 2014

This code runs to the set frm part then i get a type mismatch? ive tried a few different things and still nothing?

Code:

Private Sub Report_Open(Cancel As Integer)
' Create underlying recordset for report using criteria entered in
' EmployeeSalesDialogBox form.
Dim intX As Integer
Dim qdf As QueryDef
Dim frm As Form
' Set database variable to current database.
Set dbsReport = CurrentDb

[code]....

View 2 Replies View Related

Queries :: Average Calculated Field From A Previous Query - Crosstab Data Type Mismatch

Jun 3, 2014

I am trying to construct a crosstab that averages a calculated field from a previous query. It is returning a "Data Type Mismatch" message.

The field I am trying to average is a subtraction of dates to find total days. I assume my field is not a number so I have tried to wrap it in CDbl() to change the type.

The formula is

Code:
CASE_DAYS: CDbl(IIf([Actual Close Date]-[Creation Date]>=0,[Actual Close Date]-[Creation Date],""))

View 5 Replies View Related

Filtering Semi Unique Values

May 11, 2007

Hey guys, I'm relatively new to Access and I need some help building a query. So I work with a website that handles credit card transactions, and we keep a log of activity ie. NameOnCard, Date, Status. About once a month or so, I do an audit to make sure that all the attempted transactions are successful. Sometimes the server needs to restart, or the users computer has a problem, etc. Typically, someone who's had a failed attempt will have a successful attempt minutes or seconds later, so the NameOnCard is exactly the same, but the Date and Status are different. Right now I've created a query that look for failed attempts and one that looks for successful attempts, but what i want is one that returns only people with failed attempts that don't also have a successful attempt. Everything I've thought should work hasn't. Filtering for unique values doesn't help, because each row is slightly different, and the NameOnCard isn't the primary key. I recently tried this as a column with this in the field value:Success: IIf([Online Gift Errors]![NameOnCard]=[Online Gift Successes]![NameOnCard],"Success","Failure")(NOTE:Online Gift Errors and Online Gift Successes are queries, not tables)But when I try to run the query it thinks [Online Gift Errors]![NameOnCard] is a parametric value and asks for an input. What can I do? Should I be trying something else entirely?

View 5 Replies View Related

Semi-urgent Help With Linking Tables/forms

Aug 2, 2006

Hi there - I have a database for a conference. I have an attendee's form where I enter their personal details. Then I have another form called Options Form (which I've set up as a command button to open the form) where I can enter the workshops/sessions they sign up for.

As attendees send us their completed options forms, I need to enter their choices into the options form.

TheProblem: If attendee 103 sends in his form, I enter his choices and close out of the options form. If attendee 67 sends his form, I do the same. BUT when I go back to these attendees and open their forms, those choices I had just entered have disappeared.

The Options Table (that I set up and designed the Options Form off) has the following fields:
AttendeeID
FirstName
LastName
Workshop 1
Workshop 2....etc

When I designed the form, I formatted the AttendeeID, FirstName and LastName field to use this same info from these fields in the Attendee Form (ie, =Forms!Attendees!AttendeeFirstName). However, when I tried to find out where these options 'went', I had a look on the Options Table and they are there, but there is nothing in the AttendeeID, FirstName or LastName field - these fields are blank.

Originally when I first started entering the options, it just seemed to Autonumber the choices I entered, so even though I may have entered them for Attendee 103, in the Options Table is showed in the Attendee column as 1, then if I entered info for Attendee 86, the Options Table showed it as 2 and so on, so whatever order I entered the information it put it in numerical order as opposed to the actual AttendeeID number.

What I need: I need to be able to enter any attendee's options (whether they are attendee 1, 26, 200, or 105 - ie not necessarily in numerical order) on their options form and for this info to be saved.

I hope this is not too confusing for anyone and someone is able to help. I would really appreciate it, or if you need further clarification, please just let me know.

(My guess is it has something to do with making sure my fields are formatted correctly, ie on the Options Form the AttendeeID field was set on Autonumber which is why it may have been putting the choices in numerical order instead of whatever the AttendeeID number is. I'm also wondering if I need to set up some kind of relationship between the Attendee Table and the Options Table - but I'm not sure what type of relationship it should be on. I've tried several options and none of them seem to work)

Many thanks
Kath Price
Auckland, NZ

View 1 Replies View Related

Tables :: Adding 1000 Semi Blank Records With Sequential Numbering

May 21, 2013

I've received a database that is a digitized population register from the 19th century. All adults have been entered into the database, but all children are missing.

Every person has a unique number that corresponds with the original source (this variable is called 'no', this is not the autonumber primary key thing). Instead of searching in the original source which numbers are still missing, I would like to add the missing numbers (with no additional information, because I still need to type that in).

For example, the table now looks like this:

no - name_last - name_first - occupation etc

1 Smith Henry baker
2 Smith Mary
5 Williams John butcher
6 and so on

So 3 and 4 are missing.

How can I add these missing numbers automatically?

View 3 Replies View Related

Is There Any Thing Missing In This ERD?

Nov 13, 2007

I make that ERD :-

http://www.imagehosting.com/out.php/i1365582_erd.PNG

is there any suggest to add or remove fields or tables ?

I attached the file.

and thanks.

View 3 Replies View Related

Crazy Thing

Aug 30, 2005

I've been ask to think of some solution to this problem but i'm no way near of it. I have a txt file (ascii) with aprox. 1845000 lines.

The code is something like this:

"++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++++
Date:02-12-2004Time: 11:06:24

Job Number:2542
Job Name:al
Priority:Normal

User Name:msescudeiro
Customer:
Account:1341000
Distribution:
Notes:

--------------------------------------------------------------------------------

Original Filename:C-0884_Vers_E.TIF

recycled paper
Used Area:0
Plotted Area:0

Media:paper
Used Area:4977
Plotted Area:4977

Media:film
Used Area:0
Plotted Area:0

Media:vellum
Used Area:0
Plotted Area:0

Operation Count
Mirror:0
Rotate:0
Zoom:0
Stamp:0
Fold:38

++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++++

I need to have a table like this:

View 14 Replies View Related

Only A Little Thing But Its Getting On My Nerves

Mar 25, 2005

I have a very simple (it has to be) database which holds names, address’s and telephone numbers (I told you). What I want to do is this when I come to the Town field, a drop down list is displayed with a few local town names (Peterborough, Huntingdon etc) and the respective STC code (01733, 01480 etc). When I select Peterborough in the town box the STC code 01733 is placed in the STD field automatically. I have two tables, one with the address’s (including Town and STC) field and the second one with two fields Town and STC. How do I get the two tables or forms to work together. Any help would be appreciated, many thanks.

View 1 Replies View Related

The Most Weird Thing Just Happened ... Please Help... Thanks!

Feb 7, 2006

have a table call 'ClinicTimeTable' there is a column call 'ClinicDate', another column called 'DrID', different row could have same 'ClinicDate' and 'DrID', so I wanna to count how many Drs in a particular day, I have following code, but it acts so werid, it onlys work for some dates! say if I put 02/02/2006 then I can get the number of DrID by observing 'count', however, if I put 01/02/2006 like below, count is 0! but there is some records with 02/02/2006 (I open the table directly in Access), how can this happening? how can it only partly works? it will make more sense if it don't work at all.

Dataset ds = new Dataset();
query = "SELECT DISTINCT DrID FROM ClinicTimeTable WHERE ClinicDate = #01/02/2006#" ;
OleDbDataAdapter myAdapter = new OleDbDataAdapter( query, oleDbConnection1 );
myAdapter.Fill ( ds );
int count = dds.Tables[0].Rows.Count;

any helps will be so appreciated! I am going mad at this problem!



by the way, i just tested that if any day in Jan 2006 is fine, say if i search 23/02/2006 or 22/02/2006 is fine... can't imagine why...

View 3 Replies View Related

Deleting PM's - How's A Thing Like This Work?

Jul 1, 2006

Moderators - please help. Apparently there's a 70 message limit on saved Private Messages (PM's). I've long since exceeded the limit, but can't find a mechanism in this forum to delete unwanted PM's Where am I going wrong here?

Best wishes - Bob

View 2 Replies View Related

Eliminating Several Queries That Do The Same Thing?

Jan 3, 2007

I have many queries that all do the same thing. The problem is that they're connected to specific controls on a form, so I have to make a new query everytime I make a new form to perform the same task as the others. This would be eliminated if there was a way to use the criteria the same way you would in code, like Me!controlname. Is there anyway possible to make these queries more universal so that they can be used by many forms?

View 1 Replies View Related

Strange Focus Thing

May 6, 2005

I have a form, with some controls, a subform and some command buttons. In the OnKeyDown Event of the subform, i put the following:

If Me.NewRecord And Keycode = 9 Then
Me.Parent.cmdAddSong.SetFocus
End If

However, as soon as i hit the tab key when i am in the new record of the continuous sub form, rather than it moves to cmdAddSong, the focus is set to the control after it (cmdDeleteSong). Similarly, if i try to set the focus, to the cmdDeleteSong, after i hit the tab key, the focus will move to a next control (cmdAddAlbums).

Any ideas why does the focus does not go to the control i specified in the Me.Parent.Control, but it goes to Me.Parent.Control + 1 ?

Thank You

View 5 Replies View Related

Forgot Simple Thing

Aug 11, 2005

gosh, I can't believe I forgot simple thing. when I'm opening a form in Access it automatically goes to new/blank form, I'm try to make it go to first record instead and can't remember how I didin't before.

Thx for help

View 2 Replies View Related

Fun Little Weird Date Thing

Feb 11, 2005

This is a lot like my last post, but not exactly...

I have a table with some really strange fields. They all have 4-8 digits, and those digits stand for the date--but not for simple mmddyy. This is much more complicated than that. Each 4 or 2-digit number set stands for something different.

|___START____|_____END____|
|___196303___|___199912___|
|___192603___|___199910___|
|___193101___|___199812___|
|___195606___|___200112___|
|___192801___|___1999_____|

is a sample of the numbers in the fields. Here is the way the website says to define them:

NOTE: The seventh and eighth columns of each document describe the initial and most recent coverage dates using the SICI standard, which defines standard month codes for seasonal and quarterly issues. (A full list of the month codes used for seasonal and quarterly issues appears below.) Dates may appear as a year (1989), a year and month (197601), or a year, month, and day (19331229). Sometimes the format for initial and most recent coverage dates may differ due to changes in publication frequency over the history of the journal. For example, coverage may start with an annual issue (1898) but finish with a monthly issue (199912) or coverage may start with a monthly issue (193002) but finish with a supplemental issue containing no corresponding month (1972).

Month Codes Used in JSTOR Delimited Lists


01=January02=February03=March04=April05=May06=June07=July08=August09=September10=October11=November12=December20=Winter (when it appears at the beginning of a year)21=Spring22=Summer23=Fall24=Winter (when it appears at the end of a year)25=Early Spring26=Late Spring27=Early Fall28=Late Fall29=Early Summer30=Late Summer31=1st quarter32=2nd quarter33=3rd quarter34=4th quarterSo, if it's a 6 digit number, that's going to be 4 for the year, and 2 for one of the codes above.

If it's an 8 digit number, it's going to be 4 for the year, 2 for the month, and 2 for the day.

If it's a 4 digit number, it's just 4 for the year.

There are 665 of these, and I'll need to update them each time I download a new version of the file. So.... can anyone help me? If I could get the numbers into separate cells (at least temporarily), I could do a find and replace and add my final results back together in one cell when it's finished. I would want something that would put the first 4 digits into a cell, then the first 2 digits of what's left into a cell, and then the last two digits of what's left into another cell. I don't mind running 3 different queries.

Thanks,
Siena

View 4 Replies View Related

Weird Thing In Access 2002

Apr 14, 2008

I've been using Access 2002 since approx 2003 now. I won't upgrade as I've used 2007 and don't like the new interface.

I'm competent with it and regularly build locally used databases or databases for websites.

About 2 months ago my copy starting pissing me off. Everytime I click on any of the "create" links, it plays an alert sound. Anytime I click on an already created query/table/report etc it plays an alert sound. The only other time it happens is if I click on the button in the toolbar in windows to open a previously minimised window.

There seems to be no option to turn this off or on anywhere, so I'm completely stumped. It's not critical but it's bloody annoying. Anyone else had this and figure it out or anyone simply know how to stop it doing it?

I could obviously turn the speakers off but I like to listen to music whilst I work, and shouldn't have to either!

Thanks in advance

PS - My "Provide feedback with sounds" option is set to OFF

View 2 Replies View Related

Sending Email Via Outlookobject In VB, Weired Thing

Mar 23, 2005

i wanted to be able to send an email with attachement for this i created an outlook object and i filled in all the parameters and it creates the message successfully with the attachement and everything, but when i press send instead of sending the email to the recpient it puts it in the sent items without sending it, unless i opened my outlook and looked into the sent emails box then it sends it, i dont know if i can do something in order for the email to be sent automatically because i dont know if the problem is the code or it s a sort of security thing for outlook
if anyone has an idea.......................
thanks

View 4 Replies View Related

Crosstab Query Based On Crosstab??

Sep 21, 2007

Hi all, I am utterly unsure if what I want to do is even possible:

I have two crosstab queries, qryRewCOCredit and qryWrapCOCredit which show the changeover (CO) times for the specified machine when they are NOT zero. (all zero entries don't show up).

There are many cases when there is a CO for the Rewinder on a specific day, but not for the Wrapper, and vice versa.

I want to make another crosstab query which performs a calculation. To keep it simple:

If (RewCOCredit>WrapCOCredit) Then
5-RewCOCredit
Else 'WrapCOCredit>RewCOCredit
5-WrapCOCredit

Please help!!!

View 2 Replies View Related

Connecting Super Type & Sub Type Entities With A Condition

Sep 21, 2004

hi friends,
i have tried had to connect sub type tabels (Saving, Checking, Loan... they have their own ids...) with super type (Account...it has account id...) on the condition of account_type (either "S","C" or "L") attribute in ACCOUNT entity.
how to joint them??? with query or with expression??
i expect help from you.........please.
........thanks.

View 5 Replies View Related

Tables :: Converting Text Data Type To Number Data Type

Nov 3, 2012

I have a table with a field with names set to text data type and i want to change it to number data type but when i do it in design view the data get lost. I want to know if there is a way to convert the data in the field as number type and keep the data in the field.

View 7 Replies View Related

Pivot Table

Oct 13, 2005

Hi,

Have Access XP on Desk top and lap top. Have about 10 various databases for different projects.

On the desk top, I cannot get a Pivot Table to open in any of the databases, but on the lap top, the same databases all have Pivot tables.

Is it possible that I have turned it off on the desk top?

Thanks.


Mike

View 3 Replies View Related

Pivot Table

Jan 6, 2006

Hi everybody.

I have a pivottable query.
This works great.

I have a column with averages.
This works great to.

Now here is my problem:
At the bottom of my Averages column there is a total (sum)

I want an average in stead of a sum.

Please help me guys....

Thank you very very much in advance...

View 3 Replies View Related

Pivot-like Problem

May 4, 2005

Hello,

I am a student on a apprenticeship about MS access. My assignment is to build a MS Access application. But i am stuck. I will put my problem as simple as possible.

I have 2 tables. 1 with employees:
Department(string) | EmployeeNr(AutoNr) | EmployeeName(String)


And one with a couple of times:
Time(string)


The table i need is as follows:
Department(String) | EmployeeName(String) | EmployeeNr(AutoNr) | Time1(Boolean) | Time2 (Boolean) | time3(Boolean) | ......

Unfortunatly i have absolutely no idea how to do this and it is killing me :S

Can anybody help me? Thanks in advance.
The CitiZen

View 5 Replies View Related

Queries :: Use ID As A Pivot

Jun 27, 2013

I work on an enterprise which we send people to do field work, & i am doing a database in Access 2013, there i have a table with all the data of our employees defined by an ID, & i have also a table with all the drivers that take em defined by another ID for them, the problem is when i am trying to make a query..I made a table with the ID of the drivers, the ID of our employees that are on that trip with him, the departure date & the arrival date; and when i try to make my query i want it to display: the both dates, & Name of the driver, his ID, & the truck he is using (all this info is on the driver table), and also to diplay the name, the ID & the rank of the employees going with him (all this info is on the employee table), the problem comes that since i am using the ID to search for every employee info, when i put that more than 1 employee is going in that trip i get this error on the top of table: Expr1000, Expr1001; instead of ID, Name, etc... & no data is displayed.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved