Creating a Single field from Multiple entries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This is probably the easiest thing to do but I havent been able to nut it out
so far :

What I have is a flat text file exported from another database which
contains basically Customers and purchases. Each purchase is treated
differently naturally, but the Customer is repeated per purchase ie

Name Adress Purchase
Jim Bloggs 123 Madeup Place Butter
Jim Bloggs 123 Madeup Place Eggs
Jim Bloggs 123 Madeup Place Milk

What I want to do, is from this very simple database is create another
table, or export of some decription that will do something similar to this:

Name Address Purchases
Jim Bloggs 123 Madeup Place Butter, Eggs, Milk

I tried using the analyze wiard to try and create two tables, then create a
query from that which would display something similar but I had no luck with
it at all.

Any Ideas?? Thanks in advance
 
Thank you for such a quick response :)

I'm not 100% fussed whether it was in a single field or over multiple ones,
but I need to create a table or something that only lists the customer once
and the purchases afterwards. I did try and creating a two-table system with
the analyze wizard but I could not work a query to make it happen.

Breifly again I have a table which lists everything, and customers many
times for each individual purchases

I am wanting to create a "create table" query or similar that is made up of
each customer once and each purchase listed afterwards. Is this hard to do??

Original Table
Customer Purchase
Joe Bloggs Milk
Joe Bloggs Eggs
Joe Bloggs Cheese


New Table
Customer Purchased items
Joe Bloggs Milk Eggs Cheese (either as one or
many fields)
 
I'm not 100% fussed whether it was in a single field or over multiple
ones, but I need to create a table or something that only lists the
customer once and the purchases afterwards. I did try and creating a
two-table system with the analyze wizard but I could not work a query
to make it happen.
Click to expand...

I think that what Duane was getting at was two tables like this:

Customers
=========
Joe Bloggs 123 Madeup Place
Sam Doe 456 Somewhere Else


Products
========
Joe Milk
Joe Eggs
Joe Butter
Sam Guitar
Sam Piano
Sam Fork 'andles

and so on. I doubt that a wizard will do it for you, but a little bit of
VBA parsing with Line Input# should not be too hard. Another approach
would be using Excel, which could sort on the customer specific stuff and
then remove the duplicates.

Hope that helps


Tim F
 
I may have gone about this the long way but -

Ive now split the Original Data table into two seperate (related/linked
tables) : "Customers" and "Purchases" making sure that the "purchase order
number" was present in both tables. I have used this as the primary key.

I have managed to create a query that creates a new table (Customers) from
the existing one, that removes the duplicate owners. Each owner and purchase
was connected via a purchase number - so I have established a direct
relationship between the new table and the existing purchses table. With the
new table I have inserted the "purchases" as a subdata sheet and sure enough
it lists all purchases for each individual customer when you expand the
sudata sheet

My question now is.... from this; can I create another table, or extract, or
something that will list all this data?? I tried doing a mail merge directly
from the table - but it did not recognise the subdata sheet fields (which i
need!!)

(main datasheet)
Name Address Purchase No
Joe Bloggs 123 Madeup place 1000
|
(Subdata sheet)
Item Purchase No
Cheese 1000
Eggs 1000
Milk 1000
 
Ive now split the Original Data table into two seperate
(related/linked tables) : "Customers" and "Purchases" making sure
that the "purchase order number" was present in both tables. I have
used this as the primary key.
Click to expand...

What happens when you have more than one purchase order for the same
customer? The first table could be Customers or Orders but it can't be
both. I would guess you probably need two tables.

Since you have several Purchases on each Order, the PK of this table
should be a combination of OrderNumber plus something else. The something
else could be either an OrderLineNumber or the Product itself (as long as
you can't have the same product twice on the same order, etc).

My question now is.... from this; can I create another table, or
extract, or something that will list all these data??
Click to expand...

Yes, but you need to be _much_ more specific in what you want. Duane
posted a link where you can create a query that looks like

Sam Milk, Eggs, Butter
Jo Camera, Lights, Action

or you can make a simple join that looks like

Sam Milk
Sam Eggs
Sam Butter
Jo Camera


which is where we started!
I tried doing a
mail merge directly from the table - but it did not recognise the
subdata sheet fields (which i need!!)
Click to expand...

I suppose it's possible to create mailmerges from complex data, but to me
it just seems like eating spaghetti with chopsticks -- it's easier with a
fork! The Access report writer is specifically designed for making
reports with complex header/ grouping/ detail structures, and while there
is little you can do with Word that you can't do with Access reports,
there is plenty that is easy in Access and a pig in Word.

Oh -- and a useful tip is to ditch all this subdatasheet stuff. Use forms
(and subforms, if you like them) to manipulate data, and queries and
reports to display information for endusers. Table sheets are really only
useful for debugging and testing, and subdatasheets are nothing better
than The Work Of The Devil.

Hope that helps


Tim F
 
I just took an SQL class and we used something called a "Self Join" to
solve a similar problem.

The concept is new to me, so I can't give you too much detail. Basically,
you join a table to itself by giving it two separate Aliases in the From
and Join statement:

The example they gave was a similar list to yours, with products repeating
for multiple borrowers. The end result was the product listed once and a
separate field for each borrower. If this sounds useful to you, I can try
to give more detail.

You may find more detail by looking for "Self-Joins" under and SQL.
Hope this helps
 
:

What happens when you have more than one purchase order for the same
customer? The first table could be Customers or Orders but it can't be
both. I would guess you probably need two tables.
Click to expand...

The data table only deals with products bought in one month and thankfully
due to the nature of the purchase (vehicles) there is usually just the one
purchase order for each customer. Even so, using the purchase order as the
primary key does circumvent this issue as each purchase order is unique
Yes, but you need to be _much_ more specific in what you want. Duane
posted a link where you can create a query that looks like

Sam Milk, Eggs, Butter
Jo Camera, Lights, Action

or you can make a simple join that looks like

Sam Milk
Sam Eggs
Sam Butter
Jo Camera
Click to expand...

A simple join is what I have already - what I am trying to do is split the
purchases from the customers from the original data table then re-attach them
on a "one customer - many purchases" type table by itself; thus removing the
multiple customers from the original table
 
Brian Mullin via AccessMonster.com said:
I just took an SQL class and we used something called a "Self Join" to
solve a similar problem.

The concept is new to me, so I can't give you too much detail. Basically,
you join a table to itself by giving it two separate Aliases in the From
and Join statement:

The example they gave was a similar list to yours, with products repeating
for multiple borrowers. The end result was the product listed once and a
separate field for each borrower. If this sounds useful to you, I can try
to give more detail.

You may find more detail by looking for "Self-Joins" under and SQL.
Hope this helps
Click to expand...

SQL isnt something that is familiar to me; but I am interested in the
result. Are there any htmls that you can point me to for reference??
 
I just took an SQL class and we used something called a "Self Join" to
solve a similar problem.

The concept is new to me, so I can't give you too much detail.
Basically, you join a table to itself by giving it two separate
Aliases in the From and Join statement:

The example they gave was a similar list to yours, with products
repeating for multiple borrowers. The end result was the product
listed once and a separate field for each borrower. If this sounds
useful to you, I can try to give more detail.
Click to expand...


I think you might want to ask for your money back. Self Joins are useful,
but not in this situation. They are generally used in heirarchical or
network designs, such employee-manager or family trees.

The OP has a straightforward one-to-many design, but an ugly import file;
and the problem is parsing it conveniently. A self-joion here, on the
lines of the old network databases, would probably be catastrophic.

B Wishes


Tim F
 
The data table only deals with products bought in one month and
thankfully due to the nature of the purchase (vehicles) there is
usually just the one purchase order for each customer. Even so, using
the purchase order as the primary key does circumvent this issue as
each purchase order is unique
Click to expand...

Yebbut the customer isn't "usually" is not the same as "guaranteed". In
any case, your original post did not mention purchase orders at all: it
had Name and Address, which suggests People to most of us.
A simple join is what I have already - what I am trying to do is split
Click to expand...

By definition, a join requires (at least) two tables, and you have not
really explained what you have stored in each. Duane and I both suggested
a typical solution, but I can't tell from here what you are doing.
itself; thus removing the multiple customers from the original table
Click to expand...

Multiple customers or multiple purchase orders?


B Wishes


Tim F
 
TechieRob said:
:



The data table only deals with products bought in one month and
thankfully due to the nature of the purchase (vehicles) there is
usually just the one purchase order for each customer. Even so, using
the purchase order as the primary key does circumvent this issue as
each purchase order is unique


A simple join is what I have already - what I am trying to do is
split the purchases from the customers from the original data table
then re-attach them on a "one customer - many purchases" type table
by itself; thus removing the multiple customers from the original
table
Click to expand...

For use inside your database, you do not want to join these up again in
a new table -- that will just create a non-normalized table that would
be a royal pain to work with. On the other hand, you may have a need to
extract the data to some external form that lists each customer's
purchases horizontally. You can write a query that calls a function to
do that. In fact, the function has already been written to do that --
see the fConcatChild function posted on The Access Web at:

http://www.mvps.org/access/modules/mdl0004.htm
Return a concatenated list of sub-record values
 
Thanks for all the advice

It seems after all this that I just can't get the modules working. I can
create multiple tables (one for customers, one for purchases) and connect
them via forms or subdatasheets; but when I write the query either the syntax
is wrong or the "reserved word is misspelt" even after i copy the format
exactly from the websites.

Is there a way to do a linked table query to design this new table?
 
It seems after all this that I just can't get the modules working. I
can create multiple tables (one for customers, one for purchases) and
connect them via forms or subdatasheets; but when I write the query
either the syntax is wrong or the "reserved word is misspelt" even
after i copy the format exactly from the websites.
Click to expand...

for us to help, we really need to be able to see (a) the structure of the
tables, and (b) the SQL that you have tried to query them with.

I'm not quite sure what modules you are referring to: if you really have
the tables set up then there isn't any need for any further vba as
everything can be done in the Access user interface.

B Wishes


Tim F
 
TechieRob said:
Thanks for all the advice

It seems after all this that I just can't get the modules working. I
can create multiple tables (one for customers, one for purchases) and
connect them via forms or subdatasheets; but when I write the query
either the syntax is wrong or the "reserved word is misspelt" even
after i copy the format exactly from the websites.

Is there a way to do a linked table query to design this new table?
Click to expand...

I don't know what you mean by that question. To figure out what's wrong
with what you've done so far, I'd have to see the SQL of the query, and
know exactly what error message you get when you run that SQL.
 
Thanks again guys for all of your advice

It seems that the 'reporting' feature in access allows me to group records
by certain fields and after much troubleshooting I have managed to get
something that pleases management (thank gosh)

The original data table was created from an export of a *very* old SQL-type
system; so I have had to change the extract code to fit the new report; but
that in comparison has been relatively simple.

The report tool allows you to hide duplicate fields (ie if the next record
is exact to the previous) so my final question is that from a data table, can
you sort records first by one field; and second by another?? I know you can
simply sort by acending alphanumeric or decending but that only applies to
one column

ie sort by customer first then by purchases??
 
TechieRob said:
Thanks again guys for all of your advice

It seems that the 'reporting' feature in access allows me to group
records by certain fields and after much troubleshooting I have
managed to get something that pleases management (thank gosh)

The original data table was created from an export of a *very* old
SQL-type system; so I have had to change the extract code to fit the
new report; but that in comparison has been relatively simple.

The report tool allows you to hide duplicate fields (ie if the next
record is exact to the previous) so my final question is that from a
data table, can you sort records first by one field; and second by
another?? I know you can simply sort by acending alphanumeric or
decending but that only applies to one column

ie sort by customer first then by purchases??
Click to expand...

For a report, you can open the Sorting and Grouping dialog -- in design
view, click the Sorting and Grouping toolbar button, or menu items
View -> Sorting and Grouping -- and set the fields to be sorted on.
 
Back
Top