General :: Variation On A Cross Tab Table?

Apr 15, 2013

I have a table similar to the following

TblMinibusTrips

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.

View Replies


ADVERTISEMENT

Forms :: Variation On Shrink Won't Work

Jan 31, 2014

I have a simple subform that shows an address block.Created as a form, the can shrink property does not kick in. Created as a report, it works fine.All controls and properties and query they are based on are identical.The report pasted into Access 2010 works fine and missing fields shrink.The form pasted into Access 2010 does not.The problem I have is in how do I reference the "subreport" in the form so as to be able to requery?

View 1 Replies View Related

General :: Cross Tab Query To Display Data By Rows And Columns

Jul 10, 2013

I have a cross tab query that displays data by customer (rows) and MONTHS (columns).

However I need the columns to be the 12 months of the year 1 to 12.

However, if the selected data for a particular customer does not have any records in a month then I get an error in the report as the cross tab query only selects the months with data.

How do I get the report to show 0 or a blank in these columns

View 2 Replies View Related

General :: Cross Join To Create Ledger Type Query From 4 Tables

Apr 2, 2013

I have 4 queries in which data needs to be connected from the date and shown as a single date showing each sections entry in a row and a cumulative total is maintained as the balance .

See the attached image ...

View 7 Replies View Related

Cross-tab Table

Apr 11, 2006

Dear all,

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....

Thanks & Regards

View 6 Replies View Related

Creating A Table To Cross Index Items In Another Table

Sep 14, 2005

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.

Thanks

View 6 Replies View Related

Forms :: Cross Ref Form To Table

Jul 16, 2013

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.

View 1 Replies View Related

Queries :: Cross Table By Date

Oct 8, 2014

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.

View 2 Replies View Related

Cross Referencing Records From A Single Table

Sep 22, 2005

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

tblEventRelationships
EventParrent EventChild
1 4
1 3
1 5
6 1
3 2

In addition ... how would one set up the integrity to prevent loops from forming?

View 6 Replies View Related

Tables :: Cross Join TABLE - All Combinations

Sep 5, 2014

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.

View 3 Replies View Related

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.

Thanks,

Bob

View 3 Replies View Related

Cross Tab?

May 11, 2007

I am trying to make cross tab ..to merge the two value of the same column.A B AIRA B GROUNDA C SEAi am trying to make..A B AIR & GROUNDA C SEA..any idea?........let me know, i really need this query to solve the problem..thanks guys..

View 2 Replies View Related

Cross Tab Query

Mar 31, 2006

I want to obtain the value of a third variable using the first one as row heading and the second as column heading. I can't get this using a cross table query.
Is there another way of doing it???
Thank you

View 2 Replies View Related

Cross-Tab Query

Jan 13, 2006

I've recently been having a few problems with getting a cross tab query to work. I'm currently using MS Access 2000.

I've created a query that I know want to use for the basis of my cross-tab.

I'm only after having it produce a tab of Part Type by Month using a count of all Clients.

When I use the wizard it sorts everything out fine, but when it runs I keep getting the same error message:

"The Microsfot Jet database engine does not recognize "[Start Date]" as a valid field name or expression"

In my main query I have it set so I can choose a date range using the criteria "Between [Start Date] and [End Date]" Is there a way of getting a cross tab to work using a user defined date range. I've also got fields that I have changed the column names on, EG. ClientName to Client Name (Client Name: ClientName) it is also having issues with these.

Any help would be very appreciated.

View 3 Replies View Related

Cross-tab Query

Jun 19, 2007

G'day,

I am looking to create a cross-tab query that sums multiple columns together.

A simplified version of the data is as follows

ID Date Column2 Column3 Value
123 2007/05/15 T 1 30.00
123 2007/05/15 T 2 50.00
123 2007/05/15 T 3 15.00
123 2007/05/15 T 4 10.00
123 2007/05/30 T 1 60.00
123 2007/05/30 T 2 25.00
123 2007/05/30 T 3 15.00
123 2007/05/30 T 4 16.00
146...

The desired output is as follows

ID column1(Sum of T1) column2(Sum of T2+T3) column3(Sum of T4)
123 90.00 105.00 26.00
146...

Where T# is a concatenation of column2 and column3.

Currently using the cross-tab queries I am able to get the following output:

ID T1 T2 T3 T4
123 90.00 65.00 40.00 26.00
146...

But how can I add T2 and T3 together?

Any help would be greatly appreciated keeping in mind that I am new to Access. Thanks!

View 4 Replies View Related

Cross Tab Query?

Nov 5, 2007

I can't get the data to display as I want it. I haven't done a lot of Crosstab queries, so I need someone to clarify the field settings....


I have Pallet ID's which contain Multiple UNIT ID's


so this...

PALLET-1UNITID-1
PALLET-1UNITID-2
PALLET-1UNITID-3
PALLET-1UNITID-4
PALLET-1UNITID-5
PALLET-1UNITID-6
PALLET-1UNITID-7
PALLET-1UNITID-8
PALLET-1UNITID-9
PALLET-1UNITID-10

should look like....

PALLET
PALLET-1UNITID-1UNITID-2UNITID-3UNITID-4UNITID-5UNITID-6UNITID-7UNITID-8UNITID-9UNITID-10


the unit ID's must be on the same row as the pallet record, it can be a string in one cell

View 4 Replies View Related

Cross-tab Query Problem.

Dec 22, 2005

Hi,

I have a small problem with a cross tab query. The query works perfectly when i run it and displays the data in the way i would like it to, i want it to be on a form though and when i try and do this i get problems.

I get either a message saying i have possibly chosen a query with no output fields or a microsoft doesnt understand the name of my query.

I have looked at 'cross tab querys' and 'cross tab query forms' in the search facility and have tried some of the suggestions which people have given to similar problems to mine. For example basing another query off my cross tab query and using that for the form, but it just does brings the same problems.

I would appreciate any help, thank you.

View 1 Replies View Related

Combining Cross Tab Queries

Jan 25, 2006

I have a number of cross tab queries which I run on a weekly basis, each of which is running from a different date field in the database (e.g. one query for orders received based on 'Received Date', another for orders complete based on 'Received Date'. On a weekly basis I have to open each query in design view, edit the start and end date for the week and execute the query. It would save significant time if I could enter a date range once into a form and run the series of queries based on these parameters. Any thoughts?:) :)

View 1 Replies View Related

Cross-Tab Query Questions

Sep 11, 2007

Hi

-Is is possible to make a parameterized cross-tab query? I want only to display data between certain dates and for a specific crew.

-Also, for data in the query I have the Line number (of the machine) as the row headers and the Problem names as the column headers. I also want to display the problem CATEGORIES (of the problem names) above the names. How do I do this???

:confused:

View 7 Replies View Related

Cross Tab Query Nulls

Feb 7, 2008

is there any way to make a cross tab query default to zero instead of nulls for unpopulated cells

i know i can Nz all the resultant fields, but it seems like treating the symptoms and not the cause...

:confused:

View 5 Replies View Related

Cross Reference In Form

May 28, 2006

I have 3 fields in a form all have binding to the same table.
the problem i am facing is as follows

when i enter two fields in the form the third should calculate on its own.
Lets say the three fields are names field1, field2, and field3

scenario A
I enter field1 and field2. field3 should calculate on its own by adding field1 & field2

scenario B
I enter field2 and field3. field1 should calculate in its own by subtracting field2 from field3.

any time by changing any of the field1 or field3 the other one should change based on field2.

if field2 is changed then field3 should change.

i hope i have explained the problem in detail..

please help

thanks

aseem

View 4 Replies View Related

Help With A Form And Cross Tab Query

Nov 10, 2006

Hi - I've just followed a tutorial to create a simple parameter query based on the input received by 2 combo boxes on a form. This works ok, you select the 2 values on the form - the form passes it to the query which runs, the form closes and the query is displayed - all ok.

What I would like to do, is to display the results a bit more elegantly as the end users that will use this little database won't really like to see the query window. Is it possible to display the results on the bottom of the form? So that each time you click "Ok" on the form, it re-runs the query and displays the results. Any help would be great - but you'll have to explain it simply for me as I'm a newbee to all this! Thanks in advance:eek:

View 3 Replies View Related

Expressions In Cross Tab Querys

Mar 6, 2006

i'm struggling here

i have a simple cross tab query. i'd like to put a parameter value as criteria. lets say i hard code the criteria with 2006 it works fine. but when i reference it to the value in a form e.g forms!frm_navmaster!txtyeardate then i start having problems. i don't know to start. i've tried using the expression and where values in design view....uuggghhh.

any ideas are appreciated

View 3 Replies View Related

Cross Tab Query For 3 Month Per Report

Jun 13, 2005

I am trying to create a cross tab query which will output the data for only three months starting from the recent month. I would want these months to be heading. However, I don't want to create reports over and over again. I want something, that will resolve the issue through parameters kinda thing. I don't know, if this can be done or not. Right now I have the cross tab query for all the months, but I have to manually choose the months to fit in the page. I hope I am making sense here. I didn't know where to post this, in query or in report. Therefore, I am posting it here.

Thanx in adv.

View 7 Replies View Related

Cross Tab Query In A Form Problem

Jan 31, 2006

Hi,

I have created a form that is based on a cross tab query. I have a set number of fields in the form (1 day, 2 days, .....10 days).
The problem is sometimes when I run the query some fields disappear. for example for no records have an entry in the '2 days' field.

When this is out through the form then that whole column will read '#Name?' for all entries.

Is there some way I can clear the '#Name?' entry or replace with a zero on the form?

Hope anyone can help!!

Thanks!

View 1 Replies View Related

Cross Tab Query With 2 Column Headers

Sep 4, 2006

I have an access 2000 database and i want to make a cross tab query with 2 column headers but access doesnt allow thatI decide to make the inverted cross tab query and its okbut now i want to use this in a report but invertedex product type client1 client2 client3 a 1b 2c 3 product a product b product c type 1 type 2 type 3client1 client2 client3

View 3 Replies View Related







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