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 Replies


ADVERTISEMENT

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

Creating A Table With All Possible Combinations

Apr 6, 2007

Hi,

I have the following dilemma. I have to make multiple tables with many columns and if I were to type in the data manually it would take me a few weeks. But if I could somehow specify the number of columns(and the number of choices in each column) and then have Access(or Excel) create a table with all the possible combinations it would make my job a LOT easier. For example, let's say I have department stores in multiple cities. I need to create the following table:

CITY DEPT CLOTHING

San Jose Men's Shirts
San Jose Men's Pants
San Jose Men's Shoes
San Jose Women's Shirts
San Jose Women's Pants
San Jose Women's Shoes
Los AngelesMen's Shirts
Los AngelesMen's Pants
Los AngelesMen's Shoes
Los AngelesWomen's Shirts
Los AngelesWomen's Pants
Los AngelesWomen's Shoes

Instead of typing that out manually, is it possible to create some sort of list like:

Cities: San Jose, Los Angeles
Dept: Men's, Women's
Clothing: Shirts, Pants, Shoes

And have Access or Excel create the table with all of the possible combinations?

Thanks for your time.

View 1 Replies View Related

8 Table Need To Design Queries For All Possible Combinations...

May 21, 2007

Hi,

HELP!

I have 8 tables all with a membership number as the unique identifier, I need to find all the possible combinations and then create queries for each combination, is there a quick and easy way to do this? or do I have to sit and define a query for each of the 250ish combinations?!

I'm pulling all the data together using a union query. What I need to do now is to find the combinations using either "is null" or "is not null" criteria (in the queries).

Regards
H.

View 14 Replies View Related

Create Table Of Different Columns In Three Tables Join To Form A Master Table?

Jun 4, 2015

I have three tables that contain different columns but linked by a primary column call Name. I want to create a table where all these different columns in the three tables join to form a master table which can be updated regularly either through the master table or the smaller tables. The master table also has the primary column as Name.

If I update the master table with records, it should update the respective linked table and vice versa. I also want to link these tables to my SharePoint site.

Note: except the Name column, none of these tables have any other columns in common How do I go about this?

View 7 Replies View Related

Join Tables Into A New Table

Jul 13, 2005

Have some problems with a Purchase Order (PO) System I'm trying to do.

Figure 1 is the PO entry form. The upper part of the form shows the Header, while the bottom part shows the Line, containing all the ordered item details.

I separated the information keyed in into 2 tables, namely Header table (Figure 2) and Line table (Figure 3). The header table shows only the header details. With the order ID, the header is linked to the Line table where all the ordered items detail can be found.

I want to join the 2 tables and put the data into a new table, which will later be exported to text/spreadsheet for other purposes. I know it can be done using the make-table query. But my concern is about the layout/format of the table.

I would want to put my data where there's one header row followed by the corresponding line rows. Then another new header row with its line rows. I'll give an example:

H XXX XXXXXXXX XX X
L XXXXX XXX XX
L XXXXX XXX XX
L XXXXX XXX XX
H XXX XXXXXXXX XX X
L XXXXX XXX XX
L XXXXX XXX XX
L XXXXX XXX XX
L XXXXX XXX XX

Note that not all the fields for Header row and Line rows are the same. So can I put different data into the same column?

Would appreciate it if anyone could solve my problems. Thanks. ;)

View 1 Replies View Related

Create A JOIN Of Different Tables Called Join A Variable And List

Nov 16, 2013

And then called this join as a symbol or variable, and then have it use to select the items from these joined tables, can this be done in Access? Here is an example of a code that I created, but it has an error message saying the FROM syntax is incorrect.

Code:
SELECT firstJOIN.trainID, firstJOIN.trainName, firstJOIN.stationID, firstJOIN.stationName, firstJOIN.distance_miles, firstJOIN.time_mins
FROM (trains INNER JOIN ((station INNER JOIN lineStation ON station.stationID = lineStation.stationID)
INNER JOIN bookingLeg ON bookingLeg.startID = station.stationID or bookingLeg.endID = station.stationID )
ON trains.trainID = bookingLeg.tid) as firstJOIN

Can Access do something similar to this, in the FROM statement I joined 4 tables, because each unique fields are in each table and I have to joined them to get those fields. I called this join firstJOIN and in the SELECT statement, I list those columns in the table by calling it firstJOIN.trainID. Can Access do something like this, but syntax it differently?

View 6 Replies View Related

Forms :: How To Join Tables Using A Link Table In A Form

Mar 10, 2013

I have been asked to maintain a directory for our local Scout district. It's currently in a spreadsheet, but is crying out to be a database.I have created a "group" table, this lists all the groups in the district.I have a 2nd table called "people" this holds details of all the people in the district.As some people can have a role in multiple groups, I have created a 3rd table called "link", this is to link the people to groups.

Having read various different posts on sub forms, I am still at a loss on how to create a form / subform to populate the link table.My initial thoughts were to have a combo box on the main form, listing the people and then have a list of groups on the sub form (the are only 24). I would have a check box next to each group and if checked, it mean that the person selected in the combo is associated with that group.However my issue is that the tick box would be a "yes/no" field and the entry in the link table needs to be a number.

View 8 Replies View Related

Using Dlookup To Cross Reference Between Two Tables

Aug 13, 2012

I'm new to access and need to modify an existing database to get a report I want to create. I'm an Excel user and could accomplish what I want to do in seconds using Excel's Lookup functions, so I'm trying to do the same with Access Dlookup.

I have two tables in my database that tracks components in a hardware product. The first table is just a list of hardware units shipped. The second table is a list of various components (and versions thereof) used in each unit. The two tables are linked by a UNIT_ID field. I need to create a report showing each unit and what the various versions of the components are. My difficulty is that the component table is does not have a dedicated field for each component and each unit can be comprised of a different quantity of components. So the tables look something like this:

UNITS TABLE:
ID Customer
1 Joe
2 Bob
3 Bill

COMPONENTS TABLE:
ID Component Revision
1 Main Board A
1 Blue Board B
2 Main Board B
2 Blue Board B
3 Main Board A
3 Red Board C

I want to produce a report that looks like
ID Customer Main Board Blue Board Red Board
1 Joe A B --
2 Bob B B --
3 Bill A -- C

I setup queries to get revisions of each Board and am trying to compile into the full report, but can't figure it out. Right now I'm trying to use Dlookup to lookup data from one of the queries and insert into a larger table. I can lookup a single value fine, but can't figure out how to get Dlookup to give me the Blue Board & Red Board values for each of the system ID numbers in a master query (which would be a snap in Excel).

View 4 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

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

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

Join Tables SQL = Problem = My Tables Are Not Normalized

Dec 15, 2005

Okay - the other database is in the works and is going sloooow (the one that is being created with normalization).

Meanwhile, my other database that has no normalization I am having a problem running a query (now I know why its so important to have good structure).

To give you an idea on how bad this is...:o .....

3 tables - 94 fields to each table - each table has identicle fields, just different data. :eek:

Anyways, I have students in each table (each table is an Annual Report). I want to be able to run a simple query and combine all the students into one.

How would I achieve this?

Thank you!

And for all who are thinking about designing a database - READ READ READ and do some more reading on normalization!!!

View 3 Replies View Related

Multiple Combinations

Jun 8, 2005

Hi Everyone,

I want to perform queries that count the number of customers using combinations of different products.
The data is organised like so:

CustomerID ProductType No of Items
1 A 5
1 B 1
1 C 2
2 D 2
3 B 2
3 B 3

and so on.

There are 5 or 6 product types. I need to be able to produce a table showing all the various combinations. For example, number of customers using JUST product type A and nothing else, number of customers using product type A and product type B, customers using product type A, B and C
and so on.

I think there are around 64 different combinations!
I was wondering if there is a quicker way to do this apart from writing 64 different queries!!

Hope this clear and hope someone can help!

thanks!

Sasha.

View 3 Replies View Related

How To Search For Duplicate Combinations

Apr 16, 2012

I need to be able to find duplicate combinations (or, I suppose, permutations). I have four columns in a table that all contain inter values. I want to be able to query so that I can see if there are more than one records that have, say, 12, 28, 5, 14 in columns 1, 2, 3, and 4, respectively.

View 5 Replies View Related

How To Join 3 Tables?

Feb 25, 2005

helooo...

i have 3 tables -Recipes, Ingredients and Products.

Recipes table:
RecipeID -PK

Ingredients table:
IngredientID -PK
IngreRecipeID -Foreign key to Recipes table
IngreProductID -Foreign key to Products table

Table:
ProductID -PK

how do i join them into 1 recordset? :confused:

View 1 Replies View Related

Join Tables

Jul 11, 2005

HELP!!! :confused: I do have a 5 tables 4 tables do have a foreign key of the main table. I join the 4 tables with the main table but when I am editing the information I cannot edit it. and no error appears. so I am just wondering what happen with my joined tables? please help me!!!!Thank you in advance

View 1 Replies View Related

Join Tables

Apr 3, 2006

How do I join two tables. I have a table and a lookup table. My table has products on there that are listed as custom or basic. I have a look up table that has an ID for basic and Custom. In my table, i want it to read what the id is for each product instead of it reading "basic" or custom. Someone said that I need to join the two tables and do an update query, but I don't understand how to.

View 3 Replies View Related

To Join Tables Or Not To Join Tables

Mar 24, 2008

That is the question:
Whether 'tis nobler in the mind to suffer
with VBA Programming and the outrageous errors,
Or to take up arms against a sea of Access troubles.

Sorry I was in the mood for Shakespeare.

A quick summary first:
In the attached file I have my Relationships. One main table, Workorders with various one-to-many relationships back to their respective tables. If you look at the attachment and see a field with 'wrk' that's my foreign key.

What I'm trying to do is this:
Where-ever there is a 'wrk' field I want to add in all the fields (minus the ID Primary Key) into the main Workorders Form.

Then on the form itself I should just be able to enter in the data that is required for the fields.

So here's what I'm thinking for the coding is to Join the various tables to thlet me know if I'm on track or not. I'll start with a small one, because if I can get that right, the rest should be simple.

SELECT Model.ComputerID, Make.ComputerID
FROM Computer
INNER JOIN wrkComputerID ON Model.ComputerID = Make.ComputerID;

View 6 Replies View Related

JOIN Three Tables

Dec 12, 2005

Hi,

I've got three tables:

tblEvent
--------
Id (PK) | Event_Name



tblDelegate
--------
Id (PK) | Delegate_Name



tblBooking
--------
Id (PK) | Event_Id (FK) | Delegate_Id (FK)





I need to retrieve a recordset with the following information:

Booking Id | Event_Name | Delegate_Name


Can anyone see how to do a SELECT statement to do this?

ANy help would be great, thanks!

View 2 Replies View Related

JOIN 4 Tables!?

Dec 20, 2005

Hi,

I have the following four tables:

tblGroup:

Group_Id (PK) | Group_Name


tblSubGroup:

SubGroup_Id (PK) | Group_Id (FK) | SubGroup_Name


tblProductType:

ProductType_Id (PK) | SubGroup_Id (FK) | ProductType_Name


tblProduct:

Product_Id (PK) | ProductType_Id (FK) | Product_Name




I need to select a single Product_Name (first one which appear alphabetically) from tblProducts given a Group_Id.

Is this possible? Presumably I need to join the tables in between?

If anyone can help with this it would be much appreciated, thanks...

View 8 Replies View Related

How Can I Join These Tables?

Jun 30, 2005

there must be a way to do this, but i haven't been able to figure it out yet...

table one has three fields i need to be concerned with, member_ID, range_start, and range_end. no part of any range spanning range_start through range_end will be duplicated.

table two has several fields i need, but the field of primary concern is a person_ID that will fall somewhere within a range designated in the first table.

how in the world can i join these tables, since none of the IDs in the second table are actually listed in the first, but fall in between values?

i need to get member_ID from table one, person_ID and several other fields from table two.

any ideas?

thanks for any help,
john

View 4 Replies View Related







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