organised data structure

[arman28]

I have two table Import and Export. They have multiple columns but I have picked Import Style(I_S), Import Color(I_C) and Import Quantity(I_Q) as well as Export Style(E_S), Export Color(E_C) and Export Quantity(E_Q) to show what I want to accomplish.
The tables have no PK or FK and the only relationship is by dragging I_S and dumping it on E_S and the same with I_C and E_C.
Now I want to match my data and retrieve the ones that have the same I_S and I_C in the Import table with the same E_S and E_C in Export table.
I do get my result but I have designed it so I get the result in two queries.
Does anyone know how to brig these two queries together so I can get the result like so;

I_S I_C I_Q E_S E_C E_Q
============================================
A42 Multi 15 A42 Metallic 15
A42 Multi 360 A42 Multi 15
A42 Metallic 210 A42 Multi 30
*** **** *** A42 Multi 15
*** **** *** A42 Multi 15
*** **** *** A42 Multi 15
*** ***** *** A42 Metallic 15
B33 Black 85 B33 Black 10


instead of;

I_S I_C I_Q E_S E_C E_Q
============================================
A42 Multi 15 A42 Metallic 15
A42 Multi 360 A42 Multi 15
A42 Metallic 210 A42 Multi 30
B33 Black 85 A42 Multi 15
A42 Multi 15
A42 Multi 15
A42 Metallic 15
B33 Black 10

Any ideas?

 

[dhookom]

Every set of records you provided in your previous thread thread701-1593083 resulted in the exact output you wanted.

I just went back to your posting on 14 Mar 10 20:25 with the A201 Red items and ran the same query I proposed. The records returned were an exact match accept for your typo. Your results had an import date of 8/20/09 when the actual import date was 11/18/09.

Is the issue that you want the *** to show in place of blanks? If so, use the Nz() function. Also tell us why all but one I_C have 4 *s and the other has 5.

Duane
Hook'D on Access
MS Access MVP

 

[arman28]

I am working with 182000 lines of recods and sometimes due to integrity of the comapny I am unable to post complete data. I grab a sample with some minor changes so I can only deliver the idea(not so good at it seems like it)as to what I am trying to get.
Ok, these are shoes coming in on variety of different dates and quantities, the company ship some of these out again on different dates, some on the same date with different quantities.
I have two tables Import and Export, I want to pull the shoes that have the same "Style #" and the same "Color" so I know on what date what style of what color and how many came in and out.
I can do the matching and pulling the right "Style #" and the right "Color" out of the tables by writing a query but Access finds whatever is matched and not in an orderly fashion. This is what I mean..

Import Table Export Table
I_S I_C I_Q E_S E_C E_Q
A42 Red 20 A42 Red 10
A42 Red 30 A42 Red 5

I have to be able to get this same set up as my result through a query but what I get is this;

Import Table Export Table
I_S I_C I_Q E_S E_C E_Q
A42 Red 20 A42 Red 10
A42 Red 20 A42 Red 5
A42 Red 30 A42 Red 10
A42 Red 30 A42 Red 5

OR if I have this;

Import Table Export Table
I_S I_C I_Q E_S E_C E_Q
A42 Red 20 A42 Red 10
A42 Red 5

I want to get this as output;

I_S I_C I_Q E_S E_C E_Q
A42 Red 20 A42 Red 10
*********** A42 Red 5
doesn't matter how many stars or @ or & just any character will do, this is so the cell underneath is filled therefore the record belown is not going to jump uo to fill the cell and then I have sit and clean it up for another 80000 matched data. But again what I am getting is;

I_S I_C I_Q E_S E_C E_Q
A42 Red 20 A42 Red 10
A42 Red 20 A42 Red 5

which is not what I am interested. I hope to heavens that I make sense

 

[dhookom]

Didn't my queries in the previous thread result in

I_S  I_C        I_Q    E_S   E_C       E_Q
============================================
A42  Multi       15    A42   Metallic   15
A42  Multi      360    A42   Multi      15
A42  Metallic   210    A42   Multi      30
B33  Black       85    A42   Multi      15
                       A42   Multi      15
                       A42   Multi      15
                       A42   Metallic   15
                       B33   Black      10

Did you try my previous queries using my suggest Nz()?

Why are you mentioning "cells"? Are you exporting this to Excel?

Duane
Hook'D on Access
MS Access MVP

 

[arman28]

Your previous thread worked on all but some and I tried and tried and tried to understand what is different about the records that they didn't produce the correct result but I couldn't see why.
The astrisks showing I am not so much worried about but I will try the NZ().
Yes, I do Export my final table into excel so we can submit it to the main office.

Good to hear back from you.

Arman

 

[dhookom]

I am still looking for the "worked on all but [blue]some[/blue]". I have entered and deleted and re-entered lots of your data but haven't found any that didn't work.

Duane
Hook'D on Access
MS Access MVP

 

[arman28]

I will send the section of the exact data that the query didn't retrieve properly.
Also, I did another desperate attempt. I got my "matched" query that pulls all matched data, this time with a "PK" that I assigned to all my data in the Import and Export tables. So, naturally when I get my "matched" data there are some duplicated PKs. So, then I separate the Export section of the query(E_S, E_C, E_Q) from the Import section(I_S, I_C, I_Q) and I created two tables in the table tab and migrated the data there through excel. Then I copied and pasted the same tables into different tables in the table tab section and I clicked "YES" on the append data dialog box. so now I have no duplicates. I have what I want but in two different tables. If I could bring these two tables together in one query where I can show all data and all column headers(UNION query wouldn't do that), the problem is solved.
Is there a waythat I can send files here in the forum?

 

[arman28]

Here is where data doesn't get pulled properly from the Import or Export table. These are the actual data.

Import Date Style Color Qty
11/18/2009 A42101 Black 15
11/18/2009 A42101 White 15
11/18/2009 A42101 Terra Multi 30
11/18/2009 A42101 Metalic Multi 30
11/18/2009 A42101 Tutti 45

Export Date Style Color Qty
11/19/2009 A42101 White 15
12/10/2009 A42101 Terra Multi 15
12/10/2009 A42101 Tutti 15
11/27/2009 A42101 Metallic Multi 15
11/27/2009 A42101 Terra Multi 15
11/27/2009 A42101 Tutti 15
11/19/2009 A42101 Black 15
12/10/2009 A42101 Tutti 15
11/19/2009 A42101 Metallic Multi 15
11/19/2009 A42101 White 15
11/19/2009 A42101 Metallic Multi 15
11/19/2009 A42101 Black 15


I_D I_S I_C I_Q E_D E_S E_C E_Q

11/18/2009 A42101 Terra Multi 30 11/27/2009 A42101 Terra Multi 15
-------------------------------------11/19/2009 A42101 Metallic Multi 15
-------------------------------------11/19/2009 A42101 Black 15
-------------------------------------12/10/2009 A42101 Terra Multi 15
-------------------------------------11/19/2009 A42101 White 15
11/18/2009 A42101 Tutti 45 11/27/2009 A42101 Tutti 15
-------------------------------------11/27/2009 A42101 Metallic Multi 15


AND

Import Date Style Color Qty
11/18/2009 A42107 White 30
11/18/2009 A42107 Black 30
11/18/2009 A42107 Havana 30

Export Date Style Color Qty
11/27/2009 A42107 White 15
11/27/2009 A42107 Havana 15
11/27/2009 A42107 White 15
11/27/2009 A42107 Black 15
11/27/2009 A42107 Havana 15
11/27/2009 A42107 Black 15

I_D I_S I_C I_Q E_ D E_S E_C E_Q
-----------------------------11/27/2009 A42107 Black 15
-----------------------------11/27/2009 A42107 Havana 15
-----------------------------11/27/2009 A42107 White 15

This a samle of the some you were talking about. I hope it helps.

 

[dhookom]

If you have records with the same date, color, and style it will be impossible to get solution without some type of field or fields that create a unique key. The uniqueness is required in order to create a rank (number your records). Add an autonumber and you should be able to get this to work.

Duane
Hook'D on Access
MS Access MVP

 

[arman28]

You mean adding a PK to my Import and one to my Export tables and then follow the "Rank queries" direction that you had originally mentioned along with the rest of the queries?

 

[dhookom]

Exactly. Every table should have a primary key of one or more fields.

Duane
Hook'D on Access
MS Access MVP

 

[arman28]

It seems to be working with a field decleard as PK.
I have someone to check the result versus both tables(Import and Export)to make sure the result is what I want.

I have another question for you; I have created some unique results in two seperate tables and I want to bring them together in one table. When I use UNION query, I have no control over the headers, like how I want the headers to show or if I want more headers than the ones I have in the "SELECT" part of the query. Any suggestions?

 

[Golom]

I have no control over the headers, like how I want the headers to show or if I want more headers than the ones I have in the "SELECT" part of the query.

By "headers" I assume that you mean column names. In a UNION query the column names are determined by the first SELECT statement. For example

Select fld1 As X, fld2 As Y, fld3 As Z From SomeTable

UNION

Select fld4, fld5, fld6 From SomeOtherTable

the columns will have headers "X", "Y" and "Z". Note that the column names in the second and later SELECT statements are used to identify the data but do not otherwise appear in the result.

The first SELECT also defined how many and which columns and column names appear in the result. Further, each SELECT after the first one must supply the same number of fields with the same data types as the first one.

If you want additional columns to appear then

Select fld1 As X, fld2 As Y, fld3 As Z[red], ' ' As NewColumn[/red]
From SomeTable

UNION

Select fld4, fld5, fld6[red], fld7[/red] 
From SomeOtherTable

Will create "NewColumn" in the result. Note however that, a UNION query is not updatable so you will not be able to change any of these values.

 

[arman28]

Does anyone know how to see a table in a form by creating grid? I am using Access 2007.
I would like to have a dropbox, choose whichever I want and have it to show in gridview along with whatever else associated with that data..

Thanks.

 

[MajP]

You are going to have to restate the question and be much clearer. This could mean lots of things. I interpret this to be the same as what is described in the following thread:
Thread702-1596922

You want a main form with a subform in datasheet view. You want a combo that allows you to pick a query and a multiselect listbox to allow you to choose which fields to display.

If that is not the correct interpretation, then maybe you want to do something using a flexgrid. However, as I said you have to be much clearer or we are just guessing.

 

[arman28]

Yes I want to see a flexgrid on my form showing selected data as I pick one from the dropbox. If I use the dropbox to choose either male or female, after I select either one I like to see all data associated with what I chose, such as their names, age, location and phone numbers, shown in flexgrid. I hope this is clear and if not please let me know.

 

[dhookom]

Do you really need a flexgrid or will a datasheet view work for you?

Did you read through the thread and try anything?

Do you have a specific question about anything from the thread?

Duane
Hook'D on Access
MS Access MVP