Creating blank records

[Damien McBain]

Howdy, I wonder if anyone can help:

I have a database with a customers table (custno and name), a product
hierarchy table (hiercode) with 13 different hierarchy codes, and a sales
table with multiple prod hierarchy codes per customer number and the
associated sales data.

For many of the customers, there will not be a record in the sales table for
all 13 hierarchies if the customer has no sales history for that prod hier.

How can I add records to the sales table for each customer / hierarchy
combination that doesn't currently exist? I can do it excel with some messy
VBA (which doesn't help of I have more than 65535 records) but I'm
interested to know how I can do it with access.

cheers

Damien

 

[Tom Wickerath]

Hi Damien,

How can I add records to the sales table for each customer / hierarchy
combination that doesn't currently exist?

I assume you want to do this for testing purposes only....is your sales table a linking or join
table, between the customers table and the product tables? If so, you should be able to run an
append query that is based on a Cartesian product query result. A Cartesian product query
results when there are no joins between the tables. You will end up with a recordset that is the
product of the number of records selected in the customers table multiplied by the number of
records selected in the products table. If custno and hiercode are a combined field primary key
in the sales table, you will likely receive append query errors, if some of these combinations
are already present in the table, but you can just ignore these errors.

Create a new query. Add the customers and product tables. Select the custno and heircode
fields. Convert the query from a Select query into an Append query. Specify your Sales table as
the table to append the data to. You should see a new row in the QBE grid titled "Append To:".
Select the custno and heircode fields. Run the query.

If you view the SQL statement for this query, (View > SQL View from query design) you should have
something like the following:

INSERT INTO Sales ( custno, hiercode )
SELECT Customers.custno, Products.hiercode
FROM Customers, Products;


Tom
____________________________________________


Howdy, I wonder if anyone can help:

I have a database with a customers table (custno and name), a product
hierarchy table (hiercode) with 13 different hierarchy codes, and a sales
table with multiple prod hierarchy codes per customer number and the
associated sales data.

For many of the customers, there will not be a record in the sales table for
all 13 hierarchies if the customer has no sales history for that prod hier.

How can I add records to the sales table for each customer / hierarchy
combination that doesn't currently exist? I can do it excel with some messy
VBA (which doesn't help of I have more than 65535 records) but I'm
interested to know how I can do it with access.

cheers

Damien

 

[Damien McBain]

Hi Damien,


I assume you want to do this for testing purposes only....is your
sales table a linking or join table, between the customers table and
the product tables? If so, you should be able to run an append query
that is based on a Cartesian product query result. A Cartesian
product query results when there are no joins between the tables.
You will end up with a recordset that is the product of the number of
records selected in the customers table multiplied by the number of
records selected in the products table. If custno and hiercode are a
combined field primary key in the sales table, you will likely
receive append query errors, if some of these combinations are
already present in the table, but you can just ignore these errors.

Create a new query. Add the customers and product tables. Select
the custno and heircode fields. Convert the query from a Select
query into an Append query. Specify your Sales table as the table to
append the data to. You should see a new row in the QBE grid titled
"Append To:". Select the custno and heircode fields. Run the query.

If you view the SQL statement for this query, (View > SQL View from
query design) you should have something like the following:

INSERT INTO Sales ( custno, hiercode )
SELECT Customers.custno, Products.hiercode
FROM Customers, Products;


Tom
____________________________________________


Howdy, I wonder if anyone can help:

I have a database with a customers table (custno and name), a product
hierarchy table (hiercode) with 13 different hierarchy codes, and a
sales
table with multiple prod hierarchy codes per customer number and the
associated sales data.

For many of the customers, there will not be a record in the sales
table for
all 13 hierarchies if the customer has no sales history for that prod
hier.

How can I add records to the sales table for each customer / hierarchy
combination that doesn't currently exist? I can do it excel with some
messy
VBA (which doesn't help of I have more than 65535 records) but I'm
interested to know how I can do it with access.

cheers

Damien

Thanks for the response Tom. What I need to do is add these records only
where they don't exist already but preserve those that do. I didn't explain
the problem well all in my op.
The Customers table contains the fields custno (customer number) and a few
others, custno is the the primary key.
The Hierarchy table contains the fields prodhier and a description, prodhier
is the primary key. There are only 13 records is this table and the data is
more or less static.
The sales table contains all the sales data for all customers by product
hierarchy, so for each customer there's up to 13 product hierarchy records
with sales data for each.
I have the 3 joined with a 1 to many rel'p between customer number in the
customers table and the same field in the sale table. Same with the
hierarchies. I have referential integrity enforced in all joins with cascade
update and delete on (so if I delete a customer it gets rid of all the sale
data).
Here is how the joins are:
(hopefully you're using a fixed width font for this...)

[Customers] [Sales] [Hierarchies]
CustNo 1----> CustNo
Name ProdHhier <----1 Prodhier
Class ActTonnes Description
City ActSales
Terr ActMarg
BudTonnes
BudSales
BudMarg
PotSale

The sales table already contains data for the Act* (actual) fields
(extracted from SAP). The users will populate the Bud* (budget) fields. I
need to have all 13 hierarchies for each customer in the sales table because
the users can't create records in the form but currently only those records
where there's Act* stuff exist. So I need to somehow test for the existence
of a customer/hier combination then if it doesn't exist, create a new record
in the sales table for that combination with zero values in the Act*, Bud*
and PotSale fields.

That was the verbose description of the problem! I hope someone can assist.
I don't use Access constantly but it's ideal for this application.

cheers

Damien

 

[Tom Wickerath]

Hi Damien,

What I need to do is add these records only where they
don't exist already but preserve those that do. I didn't explain
the problem well all in my op.

Actually, I understood this from your op (original post). This is why I wrote the following:

"If custno and hiercode are a combined field primary key in the sales table, you will likely
receive append query errors, if some of these combinations are already present in the table, but
you can just ignore these errors."

The Hierarchy table contains the fields....

Okay, I didn't understand that the name of one of your tables is "Hierarchy". I thought you had
three tables named: Customers, Products and Sales.

So I need to somehow test for the existence of a customer/hier
combination then if it doesn't exist, create a new record in the
sales table for that combination with zero values in the Act*, Bud*
and PotSale fields.

There should be no need to test for the existence of previous records. When you attempt to run
the update query, you should get a message that n records failed to update, where n = the number
of pre-existing records in your table. This failure will occur because a primary key must be
unique--therefore you won't be allowed to add the same record twice. You can set a default value
of zero for the Act*, Bud* and PotSale fields before running the update query. However, I see
that ActTonnes is a description, so this field is likely text, in which case you wouldn't set a
default value for that field.

My instructions are basically the same as the first time, except with amended field and table
names and setting the default values of some fields to zero before running the update query:

Create a new query. Add the Customers and Hierarchies tables. Select the CustNo and Prodhier
fields. Convert the query from a Select query into an Append query, by selecting Query > Append
Query... from the menu in query design view. Specify your Sales table as the table to append the
data to. You should see a new row in the QBE grid titled "Append To:".
Select the CustNo and ProdHhier (<---Is this spelling intentionally different?) fields. Run the
query.

If you view the SQL statement for this query, (View > SQL View from query design) you should have
something like the following:

INSERT INTO Sales ( CustNo, ProdHhier )
SELECT Customers.CustNo, Hierarchies.Prodhier
FROM Customers, Hierarchies;


Tom

_______________________________________________


Thanks for the response Tom. What I need to do is add these records only
where they don't exist already but preserve those that do. I didn't explain
the problem well all in my op.
The Customers table contains the fields custno (customer number) and a few
others, custno is the the primary key.
The Hierarchy table contains the fields prodhier and a description, prodhier
is the primary key. There are only 13 records is this table and the data is
more or less static.
The sales table contains all the sales data for all customers by product
hierarchy, so for each customer there's up to 13 product hierarchy records
with sales data for each.
I have the 3 joined with a 1 to many rel'p between customer number in the
customers table and the same field in the sale table. Same with the
hierarchies. I have referential integrity enforced in all joins with cascade
update and delete on (so if I delete a customer it gets rid of all the sale
data).
Here is how the joins are:
(hopefully you're using a fixed width font for this...)

[Customers] [Sales] [Hierarchies]
CustNo 1----> CustNo
Name ProdHhier <----1 Prodhier
Class ActTonnes Description
City ActSales
Terr ActMarg
BudTonnes
BudSales
BudMarg
PotSale

The sales table already contains data for the Act* (actual) fields
(extracted from SAP). The users will populate the Bud* (budget) fields. I
need to have all 13 hierarchies for each customer in the sales table because
the users can't create records in the form but currently only those records
where there's Act* stuff exist. So I need to somehow test for the existence
of a customer/hier combination then if it doesn't exist, create a new record
in the sales table for that combination with zero values in the Act*, Bud*
and PotSale fields.

That was the verbose description of the problem! I hope someone can assist.
I don't use Access constantly but it's ideal for this application.

cheers

Damien

 

[Damien McBain]

Hi Damien,


Actually, I understood this from your op (original post). This is
why I wrote the following:

"If custno and hiercode are a combined field primary key in the sales
table, you will likely receive append query errors, if some of these
combinations are already present in the table, but you can just
ignore these errors."




Okay, I didn't understand that the name of one of your tables is
"Hierarchy". I thought you had three tables named: Customers,
Products and Sales.




There should be no need to test for the existence of previous
records. When you attempt to run the update query, you should get a
message that n records failed to update, where n = the number of
pre-existing records in your table. This failure will occur because a
primary key must be unique--therefore you won't be allowed to add the
same record twice. You can set a default value of zero for the Act*,
Bud* and PotSale fields before running the update query. However, I
see that ActTonnes is a description, so this field is likely text, in
which case you wouldn't set a default value for that field.

My instructions are basically the same as the first time, except with
amended field and table names and setting the default values of some
fields to zero before running the update query:

Create a new query. Add the Customers and Hierarchies tables.
Select the CustNo and Prodhier fields. Convert the query from a
Select query into an Append query, by selecting Query > Append
Query... from the menu in query design view. Specify your Sales
table as the table to append the data to. You should see a new row
in the QBE grid titled "Append To:".
Select the CustNo and ProdHhier (<---Is this spelling intentionally
different?) fields. Run the query.

If you view the SQL statement for this query, (View > SQL View from
query design) you should have something like the following:

INSERT INTO Sales ( CustNo, ProdHhier )
SELECT Customers.CustNo, Hierarchies.Prodhier
FROM Customers, Hierarchies;


Tom

Tom, that worked except that it created duplicate customer / hierarchy2
combinations in the sales table (in which there is no primary key). There
was no error (ie records not created) for this reason. My SQL looks exactly
like yours.
I'm going to play with the criteria to see if I can restrict it that way.
I appreciate your time on this - sorry if I'm wasting it.

Damien

_______________________________________________


Thanks for the response Tom. What I need to do is add these records
only
where they don't exist already but preserve those that do. I didn't
explain
the problem well all in my op.
The Customers table contains the fields custno (customer number) and
a few
others, custno is the the primary key.
The Hierarchy table contains the fields prodhier and a description,
prodhier
is the primary key. There are only 13 records is this table and the
data is
more or less static.
The sales table contains all the sales data for all customers by
product
hierarchy, so for each customer there's up to 13 product hierarchy
records
with sales data for each.
I have the 3 joined with a 1 to many rel'p between customer number in
the
customers table and the same field in the sale table. Same with the
hierarchies. I have referential integrity enforced in all joins with
cascade
update and delete on (so if I delete a customer it gets rid of all
the sale
data).
Here is how the joins are:
(hopefully you're using a fixed width font for this...)

[Customers] [Sales] [Hierarchies]
CustNo 1----> CustNo
Name ProdHhier <----1 Prodhier
Class ActTonnes Description
City ActSales
Terr ActMarg
BudTonnes
BudSales
BudMarg
PotSale

The sales table already contains data for the Act* (actual) fields
(extracted from SAP). The users will populate the Bud* (budget)
fields. I
need to have all 13 hierarchies for each customer in the sales table
because
the users can't create records in the form but currently only those
records
where there's Act* stuff exist. So I need to somehow test for the
existence
of a customer/hier combination then if it doesn't exist, create a new
record
in the sales table for that combination with zero values in the Act*,
Bud*
and PotSale fields.

That was the verbose description of the problem! I hope someone can
assist.
I don't use Access constantly but it's ideal for this application.

cheers

Damien

 

[Tom Wickerath]

Damien,

From your previous description, I assumed that the two foreign keys in your Sales table, CustNo
and ProdHhier, were used as a combined field primary key. It is quite common to use two foreign
keys in a linking table as a primary key. For example, if you open the Northwind sample
database, you will see that the Order Details table uses a combined field primary key.

You have two choices:
1.) Set these two fields as a combined field primary key in the Sales table or
2.) Set a multi-field index on these two fields, disallowing duplicates.

To do the first option, use the "record selector" (grey box at far left of each row) in table
design view to select both fields. Then click on Edit > Primary Key (or click on the PK toolbar
button).

To do the second option, click on View > Indexes in table design view (or click on the indexes
toolbar button). Enter an index name and select CustNo on the same "row". In the lower portion
of the Indexes window, set Unique = Yes. Move down one row and select ProdHhier WITHOUT entering
a new index name.

You will need to remove any duplicates prior to attempting to do either of these choices. So,
perhaps try starting over with a copy of your original Sales table, before you ran the append
query. This is the one with the data that was imported from SAP. Add a combined field primary
key, or a multi-field unique index, and then re-run the append query. Ignore any append query
errors.

Tom

_______________________________________


Tom, that worked except that it created duplicate customer / hierarchy2
combinations in the sales table (in which there is no primary key). There
was no error (ie records not created) for this reason. My SQL looks exactly
like yours.
I'm going to play with the criteria to see if I can restrict it that way.
I appreciate your time on this - sorry if I'm wasting it.

Damien