Appending new rows only...

G

googlegroups

I have two tables. One that has a list of suppliers and another that
lists a list of products. In tbl_suppliers there are 5 rows and in
tbl_products there are 10 rows. I now have created a query to show me
all the possible permutations of each supplier against each product (in
other words, on my query there is no joins), this query gives 50
results. I then have this query append an existing blank table (lets
call this tbl_results) that also has tick boxes to determine whether I
want to sell that product to that supplier.

My problem is that I now am adding a new supplier, but when I run the
query, I get 60 rows as I would expect but when this is appended to
tbl_results I now get 50+60=110 rows. How can I get MS access to just
append the additional 10 rows?

As there are no indexes in my tbl_results table, I cannot run a
duplicate query.
Any ideas?
 
G

Gijs Beukenoot

From [email protected] :
I have two tables. One that has a list of suppliers and another that
lists a list of products. In tbl_suppliers there are 5 rows and in
tbl_products there are 10 rows. I now have created a query to show me
all the possible permutations of each supplier against each product (in
other words, on my query there is no joins), this query gives 50
results. I then have this query append an existing blank table (lets
call this tbl_results) that also has tick boxes to determine whether I
want to sell that product to that supplier.

My problem is that I now am adding a new supplier, but when I run the
query, I get 60 rows as I would expect but when this is appended to
tbl_results I now get 50+60=110 rows. How can I get MS access to just
append the additional 10 rows?

As there are no indexes in my tbl_results table, I cannot run a
duplicate query.
Any ideas?
Click to expand...

You could combine all fields in tbl_results into a primary key. That
way, the previous 50 aren't added again (since they already exist).
Or, empty tbl_results before appending
 
J

jacko3

Unfortunately, as the company name and products are listed multiple
times in the results table, I cannot create either attributes as
primary keys as each rows data isn't unique.
I want to be able to tick boxes next to that supplier and product and
not loose that data so deleting the tbl isn't an option.
Thanks anyway.
 
G

Gijs Beukenoot

From jacko3 :
Unfortunately, as the company name and products are listed multiple
times in the results table, I cannot create either attributes as
primary keys as each rows data isn't unique.
I want to be able to tick boxes next to that supplier and product and
not loose that data so deleting the tbl isn't an option.
Thanks anyway.
Click to expand...

I fail to see where you would get duplicate entries for company
combined with product.

I mean, if you have, for example, company a, b and c and products x, y
and z.
You'll end up with
a, x, tickfield
a, y, tickfield
a, z, tickfield
b, x, tickfield
b, y, tickfield
b, z, tickfield
c, x, tickfield
c, y, tickfield
c, z, tickfield

Now if you add another company, that would proabely be d, ending up
with
d, x, tickfield
d, y, tickfield
d, z, tickfield

If you say the company and product is listed multiple times, that could
mean you end up with:
b, z, tickfield with a yes-value
b, z, tickfield with a no value

Or am I missing the obvious here?

Oh, and FWIW, I would suggest that this 'tickfield'-table only holds
ID's pointing to the company and products table, otherwise this table
can grow very rapidly...
 
J

John Spencer (MVP)

To create a multiple field unique index
--Open up the WorkingTable in design mode
--Select View: Index from the menu
--Enter Name for Index in first blank cell under Index Name
--Select one field in the index under Field Name
--Set Unique to Yes
--Move down one line and select the next FieldName
--Continue moving down and selecting fieldnames until all needed are included.
--Close the index window and close and save the table
 
J

jacko3

Thank you both but I am unable to create indexes as I get errors.
My data does (as suggested) look like...

a, x, tickfield
a, y, tickfield
a, z, tickfield
b, x, tickfield
b, y, tickfield
b, z, tickfield
c, x, tickfield
c, y, tickfield
c, z, tickfield

Now if you add another company, that would proabely be d, ending up
with
d, x, tickfield
d, y, tickfield
d, z, tickfield

If I apply indexes to the first two attributes as suggested by the two
of you, I get the following error message... Any ideas ???
Regards
Jacko3

"The changes you made to the table were not succesful because they
would create duplicate values in the index, primary key or
relationship.Change the data in the field or fields that contain
duplicate data, remove the index, or redifine the index to premit
duplicate entries, then try again."
 
G

Gijs Beukenoot

From jacko3 :
Thank you both but I am unable to create indexes as I get errors.
My data does (as suggested) look like...

a, x, tickfield
a, y, tickfield
a, z, tickfield
b, x, tickfield
b, y, tickfield
b, z, tickfield
c, x, tickfield
c, y, tickfield
c, z, tickfield

Now if you add another company, that would proabely be d, ending up
with
d, x, tickfield
d, y, tickfield
d, z, tickfield

If I apply indexes to the first two attributes as suggested by the two
of you, I get the following error message... Any ideas ???
Regards
Jacko3

"The changes you made to the table were not succesful because they
would create duplicate values in the index, primary key or
relationship.Change the data in the field or fields that contain
duplicate data, remove the index, or redifine the index to premit
duplicate entries, then try again."
Click to expand...

That is because only the combined values are unique. Only applying an
index on them will not work, that is correct; you'll have to combine
the two fields into one key.
Open the table in design-view, select both fields (name and product),
click on the key-button, making both fields the primary key and you're
done. You can see they are both part of the primary key because they
have the key-symbol in front of them.
 
J

jacko3

I understand about primarykeys but they need unique data and as my
attributes do not contain unique data I still get that error message.

I think I will leave this as I am not getting anywhere. I've had
another idea that does work.

Thanks for your help guys. I ts interesting, I have found other people
historically asking the same thing but none of them managed to get it
working.

Thanks
Jacko3
 
Top