Calling all Gurus...How to make a query a table 1

[AstroTek]

I want to append a table with two queries. How? I have put the descriptions in the destinastion table.I am getting a validation rule error.

Thanks be the keepers of the knowledge !

 

[HitechUser]

Help

Need more info....

 

[TheAceMan1]

How are ya AstroTek . . . . .

What you ask is [blue]not[/blue] at all [blue]clear![/blue] A guess on our part at best . . . .

Be [blue]more specific[/blue] and [purple]take more time in thought![/purple]

See Ya! . . . . . .

 

[willir]

When you update / insert data into a table, there a few things to watch out for...
- Make sure you use the same data type. For example, inserting a text string into a date, numeric or boolean (yes/no) field will be a little hard. Like wise, when you are using a numeric field, make sure they are the same numeric type. For example, integer, long integer, single and double percision. Another classic example, although may not generate an error is to insert a text string 255 characters long into a text field limited to 50 characters. Look up data types in Access help for more info.
- When a table is created, usually some rules are tied to the table. These rules are to help preserve data integrity, and are controlled by the developer. For example. If you create an Invoice, you want to ensure that the CustomerID is included in with the Invoice information. Otherwise, you will have an invoice that is not associated with a customer -- makes it hard to collect your money. Here, the developer will / may create the invoice table and state that the CustomerID is a required field.
- Do not insert data into a field defined as AutoNumber.
- Do not try to overwrite an existing table with a new table. You have to delete the old table before creating the new one, or rename the new table to ensure the name is unique.


So, open the source and target tables in design mode.
- Do not change any data types unless you know what you are doing, or you know the change will not hurt a production database. (Did I mention you may want to backup your database??)
- Look at their data types (and be a little more specific than numeric or text).
- Look at the rules for each field by looking at the bottom half of the design view under properties.

Now when you create your make table query using the Query Builder. Pay attention not to break data type or dield restriction rules, and make sure the target table is unique.

Still having problems -- post back, but please provide more info. My ESP is not working very well these days. ;-)

Richard

 

[AstroTek]

Microsoft Access ca't append all the records in the append query441records(s) due to validation rule violation. I am appending 565 records to a table. 124 records are appended to the table. INSERT INTO Appendtlb ( [Bottler Nbr], bill_bp_ent_id, [BP Role ID], [Bottler Name], [Member Name], [Bottler City], [Bottler State], [Bottler Zipcode], [Size], [Product Description], [Product Quanity], [Member Address], [Member City], [Member State], [Member Zipcode], [Member Phone] )
SELECT Billingnumqry.[Bottler Nbr], Billingnumqry.bill_bp_ent_id, Billingnumqry.[BP Role ID], Billingnumqry.[Bottler Name], Billingnumqry.[Member Name], Billingnumqry.[Bottler City], Billingnumqry.[Bottler State], Billingnumqry.[Bottler Zipcode], Billingnumqry.Size, Billingnumqry.[Product Description], Billingnumqry.[Product Quanity], Billingnumqry.[Member Address], Billingnumqry.[Member City], Billingnumqry.[Member State], Billingnumqry.[Member Zipcode], Billingnumqry.[Member Phone]
FROM Billingnumqry;

 

[cghoga]

make sure field types in both tables match up

 

[PHV]

And that all returned fields match the corresponding table level validation rules, such as null allowed or zero length allowed ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[willir]

You may have mismatched data too. A field that looks numeric but has text strings in it. For example, a field called StreetNumber would seem to be numeric. But then you hit 2A, and 1234-45 which are text strings. This type of field should be defeined as a text field.

The 124 records passed the requirements to create the new records with your append query. The remainder did not. You have 16 fields to check in the two tables.
[tt]
[Bottler Nbr], - is this numeric, check
bill_bp_ent_id, - looks like a numeric ID, ling interger
[BP Role ID], - numeric?
[Bottler Name], - obvious text, is it required?
[Member Name], - text, required?
[Bottler City], - text, required?
[Bottler State], - text, required?
[Bottler Zipcode], - text
[Size], - numeric or text?
[Product Description], - text
[Product Quanity], - numeric, right, required?
[Member Address], - text
[Member City], - text
[Member State], - text
[Member Zipcode], - TEXT!! Not numeric
[Member Phone] - TEXT!! Not numeric

Richard