Add Query and Edit Query in one

[kevmeister123]

Hello.

I need help with a query. I found out about query types (Ie Append query/select query etc).

What I need is one query that will create a new record using append, but then edit the selected record at the same time.

Ie.

Name Lollipops
Kevin 5
John 4
Peter 2

Alan comes along and takes 2 of Kevin's Lollipops. So I need a new line for Alan containin 2 Lollipops, but now need to subrtract 2 lollipops from Kevin.

So.

Name Lollipops
Kevin 3
John 4
Peter 2
Alan 2

Help please, how easy is it to do?

Thank you for any advice.

Kev

 

[AlexCuse]

Is this homework?

Even if it is, there is no way to do this with one single query.

A wise man once said
"The only thing normal about database guys is their tables".

 

[ddiamond]

You cannot do this with a single query, but you can do it with 2 queries tied together with either a macro or VBA code. Let us know if you need more details.

 

[lespaul]

However, storing calculated fields like this breaks normalization rules. Do you have a valid reason for storing a calculated field?

Read the fundamentals document below for more on normalization.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[kevmeister123]

I'll certainly give it a read (I was trying to avoid going into to much "depth" but I guess i'll have to if i want it working correctly)

Regarding the calculated fields, I didn't think they are calculated are they?

The new record is added, and an old record adjusted based on the added field, but so far there are no calculations...I'll get reading :O)

Thanks again for your help.

 

[lespaul]

You are subtracting the number of lollipops from Kevin that Alan got, that's a calculation.

You'll really make your database life easier in the future if you spend the time now to understand how it is suppose to be set up and do it correctly.

What kind of information are you really trying to store? What kind of information do you want to be able to report from the data you store? How are your tables currently set up (if that's something you can share)?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[dhookom]

There may be a need for something like this if you have a transaction table with a similar structure that has changes that need to be applied to another table. Assuming a Transaction table
[tt][blue]
Name Lollipops
Alan 2
Kevin -2
[/blue][/tt]
If your Name field is a primary key and you original table name is "ie" (I don't know why OPs never give actual table and field names), you can create a single query with SQL like:

UPDATE ie RIGHT JOIN [Transaction] ON ie.Name = Transaction.Name 
SET ie.Name = [Transaction]![Name], ie.Lollipops = Nz([ie].[Lollipops],0)+[Transaction].[Lollipops];

This will result in:
[tt][blue]
Name Lollipops
Alan 2
John 4
Kevin 3
Peter 2
[/blue][/tt]


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kevmeister123]

Lespaul,

Thanks for the documents, I read the fundamentals thing, and to be honest I didn't really understand any of it, my brain for some reason just won't pick any of it up about 1st normal forms, and normalisation etc. I can understand what i'm reading, i just don't have a clue on how to apply any of it....Der-me.
It's not secretive, i've uploaded the actual database, it's a sheep management database, it needs to record, movement of sheep etc.

http://www.lakedistrict.pwp.blueyonder.co.uk/SheepManagement.mdb

 

[kevmeister123]

thanks Dhookom, after reading the fundamentals thing I can see that making more tables is probably the "correct" way to go about it, but I just wanted to keep it simple really. I'll look at this transaction table, not sure how i'll get it to work on my current model tho, thanks again.

 

[lespaul]

Ok, here's a perfect example of how you should normalize. In your tblSheep, you have information about the SALE of the sheep. tblSheep should ONLY have information about a specific instance of A SHEEP.

In tblAddress you have information about the FARM. If a particular farm can have multiple addresses, then having an address table makes sense.

So, first you need to identify your ENTITIES (Sheep, Farm, Sales) - these will be your TABLES. Then you identify the ATTRIBUTES of those ENTITIES - these will be your fields:
A Sheep will have:
SEX
BREED
FLOCKTAG

A Farm will have
ContactName
Address (if there aren't multiple addresses for a single farm)

A Sale will have
BUYER
SELLER
TOTALDUE

Now, can a sale have multiple sheep in a single order? Then you would need a SALEDETAIL table. The Sale would contain information about the Sale and the Detail would contain information about the specific sheep that were sold.

You should take a look at the Northwind sample database and see how the Orders and OrdersDetail is set up. Basically you sheep are the "inventory" items. The Farms are the customers (and vendors).






Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[randy700]

What happened to the tables Remou helped you create? Much of this was all ready discussed/resolved in thread702 1301460.

Randy

 

[kevmeister123]

Lespaul, I think I see what you're saying. Breaking it down into different areas.

Randy700, That was help on a different query. But Remou and someone else did help me before on tables - the resulting table is the "tblSheep" table. ie one combined table of everything.

I'm REALLY confused now. I originally had a Sell table, a purchase table, an address table etc. Then was told that its too confusing that way and to make just on big table. now i find out they "should" be in seperate tables as i originally had them. And I thought it was confusing enough already lol.

Ok back to the drawing board then. So...seperate my tables into seperate "entities" is definitely the way forward?

I'll give it a shot :O)

Thanks again for the help people.

Kev

 

[lespaul]

yes, you should definitely have them split up. I would start by taking every piece of data that you have:

Sex
Breed
Buyer
Seller
Price

and determine WHAT that piece of data refers to:

Sex - SHEEP
Breed - SHEEP
Buyer - SALE
Seller - SALE
FarmName - FARM

the WHAT's that you identify will be your tables. Each piece of data belongs in the table as a field.

I read your other thread (Thread702-1296571) regarding the table structure and Remou has some valid points. But, without knowing EVERYTHING about your system, it's impossible for us to tell you exactly what to do. Can a sheep be moved more than once? Do you need to track all it's prior movements or just know where it's currently located? Additionally, the tables that I reviewed in the database link weren't set up as Remou suggested.

Les