Query questions

[Pete Davis]

I have an app for a real estate company. There are 3 main forms:

ForSale
ForRent
PropertyDetails.

Both the ForSale and ForRent properties are each associated with a
PropertyDetails record, so the PropertyDetails form is a subform for both
the ForSale and ForRent form.

My client wants a button that will create a copy of all the data for the
current record, minus the keys.

So, let's assume we've got the ForSale form up. The first thing I need to do
is create a new copy of the property details record. Then I need to create a
copy of the ForSale record and attach the new PropertyDetailsID to it.

I've created an append query for both the PropertyDetails record and the
ForSale record.

The general form is:

Insert into PropertyDetails(field1, field2, field3, ...)
Select field1, field2, field3, ...
From PropertyDetails

Question #1: I'd like to create a where condition and be able to pass the
unique ID for the PropertyDetails record I wish to copy. How do I do that?

Question #2: How do I get the ID of the newly inserted record?

Question #3: In the ForSale append query, I need to pass the
PropertyDetailsID that I obtained in Question #2, into the insert into. How
would I do this if the method is different from #1?

Thanks.

Pete

 

[Pete Davis]

There is no duplication of data. Maybe my explanation of the design was
poor, but the data is normalized.

So, since my solution is a mere "technicality", can you answer the
questions?

Pete

 

[Pete Davis]

I'm sorry, I see what you mean by duplication. I misunderstood. The reason
for copying the data is for the following example:

You have a condo. Each unit sells separately, so each is a separate listing.
However, all condos in the same building share a variety of things. What
they share and what they don't share will differ from condo to condo. For
example, some may have the same amenities as each other while others may
have upgrades. The copying of the records is simply a way to speed up data
entry of numerous condos in the same complex.

So, again, is there a solution to my question?

Pete

 

[Pavel Romashkin]

The solution is a technicality. The big question is, WHY? Why do they
want a duplication of information? It is a sign of poor design when data
are duplicated. Perhaps their goal can be achieved without denormalizing
the database.

Pavel

 

[John Vinson]

I have an app for a real estate company. There are 3 main forms:

ForSale
ForRent
PropertyDetails.

Both the ForSale and ForRent properties are each associated with a
PropertyDetails record, so the PropertyDetails form is a subform for both
the ForSale and ForRent form.

My client wants a button that will create a copy of all the data for the
current record, minus the keys.

So, let's assume we've got the ForSale form up. The first thing I need to do
is create a new copy of the property details record.

I'm really queasy about this. I wonder if you have the relationships
backwards! It's still the same piece of property; storing all of its
information redundantly in a second table seems unwise.

Might it not make more sense to have *one* property record? It could
be linked both to the Sales and Rentals tables, could it not?

 

[Pete Davis]

No, you don't understand at all.

First of all, I wish people would just answer the question I've been waiting
hours for an answer to instead of trying to scrutinize my design decisions
when they weren't involved in the design discussions. I don't mean this as
an attack on you, and I hope it doesn't come out that way, but this happens
all the time on usenet and it drives me nuts.

Okay, here's how it works:

You have properties for sale and you have properties for rent.

Each property for sale has a unique property details record associated with
it. Each property for rent ALSO has a unique property details record
associated with it.

The property details contains information such as who the owner is, who the
agent is, where the property is located, property descriptons, and so on and
so forth.

A ForSale property has information specific to it that a Rental property
does not, such as a sale price, improvements, lot size, and so forth.

Rental properties, on the other hand, have rental rates (weekly, monthly,
maybe half year and maybe year rates). We also have statistics that are
specific to rentals so that we know how much we've made off of a specific
rental in the past after expenses.

So, that's the design.

The reason for copying the records is COMPLETELY DIFFERENT. I already
explained why we're doing that in a previous post.

So, CAN SOMEONE ANSWER MY QUESTIONS?

Pete

 

[Pavel Romashkin]

Ok.

Answer #1:
Dim UniqueID as Long
UniqueID = DMax("PD_ID", "PropertyDetails") + 1
Dim Q as DAO.Querydef
Set Q = CurrentDB.CreateQueryDef(vbNullString)
Q.SQL = "Insert into PropertyDetails(PD_ID, field1, field2, ...) SELECT
(" & _
UniqueID & " AS PD_ID, field1, field2, ....) FROM PropertyDetails"
Q.Execute

Answer # 2:
The ID of the newly inserted record is UniqueID.

Answer # 3:
Use Insert ... Into .... Values () query.

Although you seem to have formed a solid opinion that you have a
flawless design, insertion of records like this is a flaw that should be
addressed using a junction table.
If others on the Usenet lost their patience as soon as you, I doubt
you'd get any replies. No offense meant - it is not a paid customer
support, but a discussion forum. John Vinson knows his databases and
voices his opinion for a reason. I think it is a good investment of a
100 keystrokes if it leads to an answer from him.
Good luck,
Pavel

 

[Pavel Romashkin]

P.S. Forgot the filtering.
UniqueID & " AS PD_ID, field1, field2, ....) FROM PropertyDetails WHERE
PD_ID = " & Me!PD_ID
assuming that the record you want to copy is the one currently active in
the form and PD_ID is the name of the primary key ID.

Pavel

 

[Kelvin]

Pete, try this.

Make your button set the default of each text box to the current value in
the box. Then move to the new record, this will automatically fill each box
with the info you want. Then all you have to do is enter the key info.
This won't create a record until you enter the key so if the user changes
his mind, he can just cancel the process and not worry about having to
delete the record out of the table. If you only want this to happen each
time the user presses the button, after moving to the new record, set all
the defaults back to "" so that the next new record won't have the same
info, unless you want it to.

If you really want to copy the data, follow these steps for the button.

1) Append query to copy current record into table. Set the ID to something
like "999".

Insert Into tblTable ([Field1],[Field2],[Field3],etc... )
Select "999",[Field2],[Field3],etc...
From tblTable
Where ([Field1]=[Forms]![frmNameOfForm]![txtTextBox])

2) Append query to copy subform data

Insert Into tblSubTable ([Field1],[Field2],[Field3],etc... )
Select "999",[Field2],[Field3],etc...
From tblSubTable
Where ([Field1]=[Forms]![frmNameOfForm]![txtTextBox])

3) Bring up pop-up form asking for key

DoCmd.OpenForm "frmPopUpForm"

Then on the pop-up form have an OK button. Them for the code of this button

1) Change the 999 from tblTable to this new number

Update tblTable
Set tblTable.[Field1] = [Forms]![frmPopUpForm]![txtKey]
Where (tblTable.[Field1] = "999")

2) Change the 999 from tblSubTable

Update tblSubTable
Set tblSubTable.[Field1] = [Forms]![frmPopUpForm]![txtKey]
Where (tblSubTable.[Field1] = "999")

4) Find the record in the original form.

Dim rs As Object
Set rs = Forms!frmNameOfForm.RecordsetClone
rs.findfirst "[txtField1]='" & Forms!frmPopUpForm!txtKey & "'"
Forms!frmNameOfForm.Bookmark = rs.Bookmark

5) Close popup form

DoCmd.Close "frmPopUpForm"

Kelvin

No, you don't understand at all.

First of all, I wish people would just answer the question I've been waiting
hours for an answer to instead of trying to scrutinize my design decisions
when they weren't involved in the design discussions. I don't mean this as
an attack on you, and I hope it doesn't come out that way, but this happens
all the time on usenet and it drives me nuts.

Okay, here's how it works:

You have properties for sale and you have properties for rent.

Each property for sale has a unique property details record associated with
it. Each property for rent ALSO has a unique property details record
associated with it.

The property details contains information such as who the owner is, who the
agent is, where the property is located, property descriptons, and so on and
so forth.

A ForSale property has information specific to it that a Rental property
does not, such as a sale price, improvements, lot size, and so forth.

Rental properties, on the other hand, have rental rates (weekly, monthly,
maybe half year and maybe year rates). We also have statistics that are
specific to rentals so that we know how much we've made off of a specific
rental in the past after expenses.

So, that's the design.

The reason for copying the records is COMPLETELY DIFFERENT. I already
explained why we're doing that in a previous post.

So, CAN SOMEONE ANSWER MY QUESTIONS?

Pete

to

do

I'm really queasy about this. I wonder if you have the relationships
backwards! It's still the same piece of property; storing all of its
information redundantly in a second table seems unwise.

Might it not make more sense to have *one* property record? It could
be linked both to the Sales and Rentals tables, could it not?

[/QUOTE]

 

[Fred Boer]

Dear Mr. Davis:

I only wish that I were expert enough in Access to be able to answer your
question; if I were, I would be able to have the satisfaction of refusing to
do so! You are acting like someone with a toothache who insists that the
doctor show him how to cut off his head, when the doctor is suggesting a
simple extraction... You ask for assistance, but rudely dismiss good advice
from an expert in the field, who offers his expertise free of charge, and
"shout" out a demand for someone to help you. You note that this happens to
you "all the time"; perhaps you might consider whether your own attitude
plays a part in this.

I see that some experts have given you the answer you desire. Here's hoping
that you don't regret applying it!

Sincerely
Fred Boer

No, you don't understand at all.

First of all, I wish people would just answer the question I've been waiting
hours for an answer to instead of trying to scrutinize my design decisions
when they weren't involved in the design discussions. I don't mean this as
an attack on you, and I hope it doesn't come out that way, but this happens
all the time on usenet and it drives me nuts.

Okay, here's how it works:

You have properties for sale and you have properties for rent.

Each property for sale has a unique property details record associated with
it. Each property for rent ALSO has a unique property details record
associated with it.

The property details contains information such as who the owner is, who the
agent is, where the property is located, property descriptons, and so on and
so forth.

A ForSale property has information specific to it that a Rental property
does not, such as a sale price, improvements, lot size, and so forth.

Rental properties, on the other hand, have rental rates (weekly, monthly,
maybe half year and maybe year rates). We also have statistics that are
specific to rentals so that we know how much we've made off of a specific
rental in the past after expenses.

So, that's the design.

The reason for copying the records is COMPLETELY DIFFERENT. I already
explained why we're doing that in a previous post.

So, CAN SOMEONE ANSWER MY QUESTIONS?

Pete

to

do

I'm really queasy about this. I wonder if you have the relationships
backwards! It's still the same piece of property; storing all of its
information redundantly in a second table seems unwise.

Might it not make more sense to have *one* property record? It could
be linked both to the Sales and Rentals tables, could it not?

[/QUOTE]

 

[John Vinson]

No, you don't understand at all.

My apologies. I was *asking a question*, not slamming your design.

I see that Pavel and Kelvin have given you good suggestions. If they
don't work for you don't hesitate to post back. I promise to keep my
suggestions about Subclassing to myself, given the strength of your
opinions.

 

[Pete Davis]

I apologize for losing my patience. I'm at the very end of this project and
this is the final piece. I didn't design the database and I was brought in
at the end. While I agree it's not perfect, it meets their needs and I'm
simply here to add a few features they want and then I walk. A redesign of
the database is beyond the scope of my work. They wouldn't pay for what it
would cost and I'm not doing it for free.

I don't even normally deal with access, so I simply have technical questions
that I needed answered.

I understand people want to give advice and that's fine, I don't mind that,
but what bothers me is when someone posts along the lines of, "I know your
answer, but that's not what you want to do. What you want to do is xyz" and
then I don't get my technical answer. yes, xyz may be the best way to go in
a perfect world, but in my situation it's an impossibility.

I lost my patience because I need this project completed and delivered
today. Sorry.

Pete

 

[Pete Davis]

Fred, John, and everyone else,

I apologize for losing my patience. As I posted to Pavel, I picked up in the
middle of this project simpy to add some features for the customer. I have
to have the thing delivered today. Changing the design is way beyond the
scope of what my client asked for and is willing to pay for.

While I appreciate that people here have good advice to offer, and I don't
mean to dismiss it, there's simply nothing I can do with that advice and for
the first several hours, I was getting advice at the expense of getting the
answers to my questions, which is what was frustrating me. I don't mind
something like "Here's the answer to your question, but what you really
might want to consider is this:..."

While the design certainly has its flaws, it's sufficient for the customer's
needs and that satisfies my customer and it satisfies me. If he had deeper
pockets and a desire to do more, I wouldn't hesitate because it would mean
more money for me in the end. Since his pockets arent' deep, I want to solve
his problem as quickly as possible and move on to the next client.

Pete

 

[Pavel Romashkin]

Pete,

This is a fine response. We all have been there
I hope that you were able to solve the problem you were facing. If not,
post again and I think people will try to help.
If you can, ignore the deadlines and tell them - you want quality, leave
with the delay but let me do my job right.

Good luck,
Pavel

 

[Pete Davis]

Pavel,

I would prefer to do it as you say, but the deadline is beyond anyone's
control. One of the key people is leaving at the end of the week for good,
so it's got to be done before she's gone.

Actually, the insert into is failing. I tried running the query directly
just to test. It looked kind of like this:

INSERT INTO PropertyDetails ( PropertyTypeID, ListingAgent, Address, City,
....)
SELECT [PropertyDetails].[PropertyTypeID], [PropertyDetails].[ListingAgent],
[PropertyDetails].[Address], [PropertyDetails].[City], ...
FROM PropertyDetails WHERE PropertyDetailsID=1

Microsoft Access set 0 field(s) to Null due to a type conversion failure,
and it didn't add 1 record(s) to the table due to key violations, 0
record(s) due to lock violations and 0 record(s) due to validation rule
violations.

When running the query from within the code, it gives me:

The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key, or relationship.
blah blah blah.

I removed all relationships from the table to test. The primary key is
autonumber. It's the only thing that could be breaking it that I can think
of, but since it's an autonumber field, it has to be excluded from the
insert field list, correct?

I tested the exact same sql with a duplicate copy of the database in MS SQL
Server and it worked fine (same constraints as the Access version).

Any ideas?

Pete

 

[Kelvin]

You do not necessarily have to leave the autonumber fields in or out. If
you insert or update and autonumber field through a query or code, the
number can be changed. You can even force the autonumber field to repeat.
In your case, you have set this as a key field so you can't have repeats.
If the autonumber field is not used for anything except as a unique
identifier, leave it out of the insert query and let Access automatically
generate the number for you. However, if you are using this key to link to
other tables, keep it in the query and make sure you don't have repating
values. You might have to create an update query to modify this number is
you originla tables first before appending it to another table.

Kelvin

Pavel,

I would prefer to do it as you say, but the deadline is beyond anyone's
control. One of the key people is leaving at the end of the week for good,
so it's got to be done before she's gone.

Actually, the insert into is failing. I tried running the query directly
just to test. It looked kind of like this:

INSERT INTO PropertyDetails ( PropertyTypeID, ListingAgent, Address, City,
...)
SELECT [PropertyDetails].[PropertyTypeID], [PropertyDetails].[ListingAgent],
[PropertyDetails].[Address], [PropertyDetails].[City], ...
FROM PropertyDetails WHERE PropertyDetailsID=1

Microsoft Access set 0 field(s) to Null due to a type conversion failure,
and it didn't add 1 record(s) to the table due to key violations, 0
record(s) due to lock violations and 0 record(s) due to validation rule
violations.

When running the query from within the code, it gives me:

The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key, or relationship.
blah blah blah.

I removed all relationships from the table to test. The primary key is
autonumber. It's the only thing that could be breaking it that I can think
of, but since it's an autonumber field, it has to be excluded from the
insert field list, correct?

I tested the exact same sql with a duplicate copy of the database in MS SQL
Server and it worked fine (same constraints as the Access version).

Any ideas?

Pete

--
http://www.petedavis.net

Pete,

This is a fine response. We all have been there
I hope that you were able to solve the problem you were facing. If not,
post again and I think people will try to help.
If you can, ignore the deadlines and tell them - you want quality, leave
with the delay but let me do my job right.

Good luck,
Pavel

project

and

brought

in

redesign

of

what

it

xyz"

and

go

in

active

in

of

a

speed

up

able

to

 

[Pete Davis]

I found the problem. Apparently the indexes were corrupt. I had to create a
new DB and transfer everything over and it worked fine. Go figure.

--
http://www.petedavis.net

You do not necessarily have to leave the autonumber fields in or out. If
you insert or update and autonumber field through a query or code, the
number can be changed. You can even force the autonumber field to repeat.
In your case, you have set this as a key field so you can't have repeats.
If the autonumber field is not used for anything except as a unique
identifier, leave it out of the insert query and let Access automatically
generate the number for you. However, if you are using this key to link to
other tables, keep it in the query and make sure you don't have repating
values. You might have to create an update query to modify this number is
you originla tables first before appending it to another table.

Kelvin

Pavel,

I would prefer to do it as you say, but the deadline is beyond anyone's
control. One of the key people is leaving at the end of the week for good,
so it's got to be done before she's gone.

Actually, the insert into is failing. I tried running the query directly
just to test. It looked kind of like this:

INSERT INTO PropertyDetails ( PropertyTypeID, ListingAgent, Address, City,
...)
SELECT [PropertyDetails].[PropertyTypeID], [PropertyDetails].[ListingAgent],
[PropertyDetails].[Address], [PropertyDetails].[City], ...
FROM PropertyDetails WHERE PropertyDetailsID=1

Microsoft Access set 0 field(s) to Null due to a type conversion failure,
and it didn't add 1 record(s) to the table due to key violations, 0
record(s) due to lock violations and 0 record(s) due to validation rule
violations.

When running the query from within the code, it gives me:

The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key, or relationship.
blah blah blah.

I removed all relationships from the table to test. The primary key is
autonumber. It's the only thing that could be breaking it that I can think
of, but since it's an autonumber field, it has to be excluded from the
insert field list, correct?

I tested the exact same sql with a duplicate copy of the database in MS SQL
Server and it worked fine (same constraints as the Access version).

Any ideas?

Pete

--
http://www.petedavis.net

Pete,

This is a fine response. We all have been there
I hope that you were able to solve the problem you were facing. If not,
post again and I think people will try to help.
If you can, ignore the deadlines and tell them - you want quality, leave
with the delay but let me do my job right.

Good luck,
Pavel

Pete Davis wrote:

I apologize for losing my patience. I'm at the very end of this

project

and
this is the final piece. I didn't design the database and I was

brought

in
at the end. While I agree it's not perfect, it meets their needs and I'm
simply here to add a few features they want and then I walk. A

redesign

of
the database is beyond the scope of my work. They wouldn't pay for

what

it
would cost and I'm not doing it for free.

I don't even normally deal with access, so I simply have technical questions
that I needed answered.

I understand people want to give advice and that's fine, I don't

mind
that,

but what bothers me is when someone posts along the lines of, "I

know
your

answer, but that's not what you want to do. What you want to do is

xyz"

and
then I don't get my technical answer. yes, xyz may be the best way

to

go active of misunderstood.
The speed

Why

do design
when associated
with a the
data I
need able How
do I