I have 4 inventories , How I will make relationship for all in Query ?

[Mehvan]

Hello Everyone

I have 4 inventories , I made special Table for them
Can someone help me about them ? When I choose parts for one of them automatically my data go to that inventory

for example : my parts at Factory are ( Big rolls , small rolls , pipes and Damage )
When I choose big rolls automatically those materials I entered go to big rolls inventory



How I will do it in Query ?
Please , If someone can help me , I will be very pleased .

[CJ_London]

you should only have one inventory table. Included in that inventory table would be a field 'inventorytype' which can be populated with 'Big rolls' , 'small rolls' etc

[Mehvan]

yes , I made it
but How I will make query for that table , I mean when I choose 'inventotytype' it's automatically appear in that inventory
I want to be established for each of sections Type Special Report .

[CJ_London]

I think you are talking about cascading combo's

if so, on your form you will have two combos - something like this

cboInventoryTypes
rowsource "SELECT DISTINCT InventoryType FROM tblInventories ORDER BY InventoryType"
bound column 1
column count 1


cboParts
rowsource "SELECT PartPK, PartDescription FROM tblInventories"
bound column 1
column count 2

in the cboParts Enter event put

cboParts.rowsource="SELECT PartPK, PartDescription FROM tblInventories WHERE InventoryType=[cboInventoryTypes] ORDER BY PartDescription"

in the cboParts Exit event put

cboParts.rowsource= "SELECT PartPK, PartDescription FROM tblInventories"

[Mehvan]

thanks a lot for your help
I made all in Forms and I bring from Tbl
but I have a problem with Query , I don't know How I will create Query
because I think I should make all Reports from Query , I mean If I don't have a Queries I can't create Reports


This is my Project Query , But I don't know How I will create it

For more information I created by 'Part name' .
Do you can help me , please ?
How I will create Query ?

[CJ_London]

part name implies text whilst partID implies a number. If you are using lookup fields in your table design, remove the lookup, it only confuses things.

Otherwise include the inventoryID in your query and when you do your report, use the group and sort on it (this is different from the grouping in queries)

[Mehvan]

Thanks a lot for your help
because I'm not good in Microsoft Access , I came to this big website to learn Microsoft Access and I was glad to joined in this site

Excuse me , I will upload my DB project , If you can correct my mistakes at this project , I will be very pleased

Factory.zip

[CJ_London]

I've taken a quick look and not sure I can help. There seem to be a lot of unnecessary tables, many forms and no reports. What am I supposed to be looking at?

[Mehvan]

My projects about Iron Factory
I have these tables
1 - customers name
2- products name
3- Goods name
4- sales invoice
5- buy invoice
6- return materials from customers ,, because maybe I send to him something wrong
7- return materials to the products
8- damage ,,, because We sale damage materials cheaper than well materials
and for all invoices , I made special table even I create subform for these invoices

I thought I have many mistakes at that tables and forms , so I didn't created any Reports and I deleted that Query too .

[CJ_London]

still doesn't tell me what your problem actually is

[Mehvan]

did you look at my DB ?
you saw any problem in my DB , If has a problem in that DB and you tell me , I will be very glad

now I have 3 questions :
1- I want When I write customers name in sales invoice automatically appear his address and phone
2- I want to create queries for inventory Reports , I mean one Report for 'Big roll' , and one Report for ' Small rolls ' and other .
3- How I will create Report for every invoice , I mean when someone came to buy something and I sold him about 10 types of pipes , I want to create report for that invoice .

[CJ_London]

since I don't know what your db is supposed to do, I cannot tell you if there is anything wrong with it.

3 questions

1. customers should be a subform on your main form to display the customer details. the customer name combo should be based on your customer table
2. Just have one report and when you open it from your form (using docmd.openreport) apply a criteria for which roll size you want
3. as with 2, one report for invoices and when opening apply a criteria to select the invoice number.

Since you have not explained how your business works I have no idea if you have the right data structure. but looks like you need a link to products from 'the customer side'

You would do well to

a) remove spaces from your names
b) do not use lookups in table design
c) use autonumbers to uniquely identify each record. At the moment many are numeric but need to be populated

I don't see why many tables appear to be duplicates, one referring to customers and the other to products, I would have thought they should be the same table.

similarly sale invoice and customer return should be the same table, I can't see why they would be separate

[Mehvan]

I think it will be difficult for me to create little Tables
because I don't have many information about it , and I should to have many times with questions
and also I should be patient with friend's help , even I be better
because gradually I will be better , sometimes reading books , searching on YouTube , asking friends
I have believe by myself , I will be better but gradually .

I'm sorry , If I ask you many questions
And also I'm sorry , I made you tired with my many questions , and also my English isn't very good to can understand me well .

I think I read Microsoft Access from Tables again , because I have mistakes in Tables too , and Tables is so important to create successful program by Microsoft Access .
I will be glad When you have a free time to help me .
your answer is so useful for me and support me to be continue on this project .
thanks a lot for your help .
I will try to do my best , even I be good programmers and help other people who want to learn Microsoft Access .

[CJ_London]

Happy to help where I can. Appreciate language can be an issue - would google translate help?

https://translate.google.co.uk/

If you tell me your language, I can also try translating

from this thread - https://www.accessforums.net/showthread.php?t=62124
did Orange's videos help? If not, what did you not understand?

Ssanfu's post #11 in this thread offers some good advice - https://www.accessforums.net/showthr...262#post333262

More advice from Orange here - https://www.accessforums.net/showthread.php?t=62220

Not to be critical, but comparing the db you uploaded before and this more recent one you do not appear to have taken on board the advice given other than to apply relationships. Perhaps you do not understand the advice?

The way to understand how it all works is different for everyone. I understand you are an accountant so I'll try to draw an analogy from there. So step back and take a deep breath and think like an accountant for a minute.

The most important this is to get your tables right, after that everything else is relatively easy

With bookkeeping you have several ledgers - sales, purchase, stock, nominal etc. With the exception of the nominal ledger they all have the same basic structure: an 'owner' (customer, supplier or stock item), 'transactions' (sales, purchases, stock movements) and often with transactions you have detail transactions (invoice lines, purchase lines, part movements). And the nominal ledger is the glue than binds these different ledgers together and allows them to interact with each other (to preserve the concept of double entry).

Note these structures are all basically the same. What is the difference between a customer record and a supplier record other than what they are? difference between a sales invoice and a purchase invoice? or an invoice line and a stock movement?

I accept that sometimes you need some information about a customer that is not required for a supplier and visa versa and I'm not suggesting you should combine these in one table, but if you look at your accounting system - do you see a different type of record for an invoice from that of a credit note? No - so these should be combined into one table.

So going back to double entry. This is where the analogy ends, because with double entry you have two records - debit one account , credit another. Whereas with a database you just need the one record, and depending on from what perspective you are looking at it can be different things. So take a sale of a product - from the stock ledger perspective it is seen as movement out (stock reduction) but would be seen by a customer as a sale. All you need to do is include in that record the information required for both perspectives.

I'm not sure whether this next bit relates to your business but you would track movements of stock through the factory in much the same way. If raw stock is moved out of the warehouse and into production, in accounting terms you would credit raw stock and debit work in progress - but again, from a database only one transaction record is required.

So get a big bit of paper and some sticky notes. On the paper draw some boxes to represent tables - basically 'owner', 'transactions', 'detail' (looks like you need one set for customers and one for stock at the moment) Write on the notes the data you need to know and place them in one of the boxes. The rule is you cannot repeat the data and you can only put it in one box.

You can draw lines between the boxes so that if data needs to appear in more than one box you can follow the line to get to that data (these will be your relationships)

You will find you need additional tables - your stock type for example and you will also find is that the stock detail record and the sales detail record are pretty much the same so can be combined.

Hopefully you will find a lot more as well as you think about how data is collected (i.e. manual type, import from another system) and what you need for reporting (invoices etc)

Hope this helps but I've now run out of steam!

[Mehvan]

Thank you so much for your help
Your explain about my mistakes in Microsoft Access was so useful for me
I will try to correct all my mistakes in Tables and other Parts
I will remove Space in Tables and I don't use Lookup too .