/wsr/ - Worksafe Requests » Thread #824895

[18 / 6 / ?]

311KiB, 1000x1000, endme.jpg

Access SQL help

Anonymous Wed 20 May 2020 11:38:56 No.824895 View View Reply Original Report

Quoted By: >>824906

Hello, I have a fairly simple project to make. I already have an access database with a few tables set up and I just need to make a few queries (5 selects, 2 group-by and 2 queries with sub-queries). I already made the selects but I'm not really good at SQL or coming up with queries. Would anyone mind helping me out over discord? I'll buy you a cheap game on Steam or something.

Anonymous

Anonymous Wed 20 May 2020 11:41:19 No.824896 Report

Quoted By:

Forgot discord Homeless-Pleasehelp-Godbless#3391

Anonymous

Anonymous Wed 20 May 2020 12:36:05 No.824906 Report

Quoted By: >>824909

>>824895
If you post them here, it's more likely people will help you.

Anonymous

Anonymous Wed 20 May 2020 13:09:39 No.824909 Report

Quoted By: >>824911

>>824906
This. I don't really give a shit about steam or games either.
Just obscure the database name, table names, columns and whatever, OP.

Anonymous

Anonymous Wed 20 May 2020 13:13:18 No.824911 Report

Quoted By:

>>824909
It's not a problem of obscuring or not, I'm just not sure how to ask for help in text. Also the project is in Romanian cause the teachers is fucking stupid and doesn't speak english. I'll do my best to explain.

Anonymous

View Same Google ImgOps iqdb SauceNAO order.png, 11KiB, 901x107

Anonymous Wed 20 May 2020 13:19:46 No.824913 Report

Quoted By: >>824914 >>824941

This is a table for ordering multiples of a product. Is there a way to generate the price based on the ID of the product that is being bought and the quantity?

Anonymous

View Same Google ImgOps iqdb SauceNAO payments.png, 10KiB, 805x104

Anonymous Wed 20 May 2020 13:21:53 No.824914 Report

Quoted By: >>824949

>>824913
This is a table to process payments, how can I get the client ID, transaction date and price to carry over from the orders table. Also was having payments and orders both be a foreign key in each other a mistake? If not can I get them to display without manual input?

Anonymous

View Same Google ImgOps iqdb SauceNAO transaction.png, 7KiB, 698x85

Anonymous Wed 20 May 2020 13:23:41 No.824918 Report

Quoted By: >>824952

This table is just a straight up summary of transactions and is made up entirely of foreign keys from other tables. Is there a sql function I can write to get these to auto-complete based on manual input of a few foreign keys?

Anonymous

View Same Google ImgOps iqdb SauceNAO products.png, 16KiB, 805x148

Anonymous Wed 20 May 2020 13:28:15 No.824920 Report

Quoted By: >>824954 >>824963

Other than that, I have a tab for clients, employees, suppliers and products (mostly all consist of ID, name, surname, phone number, address, with the exception of products which also has a stock and stock capacity columns) kept in stock). I already wrote 5 select functions to sort random entries in ascending order or according to some criteria, but I'm not sure what to do for the group by and sub-query requirements. Every time I try to make some sort of comparison or arrange based on criteria across multiple tables I'm assuming I get an error cause running the query results in a blank table. Did I fuck up? Should I start over?

Anonymous

Anonymous Wed 20 May 2020 14:04:08 No.824941 Report

Quoted By: >>824959

>>824913
select PRET * CANTITATE
from TABELCOMANDA
where ID_COMANDA = 330001

It gets a bit more complex if you want to sum up an order consisting of several IDs.

Anonymous

Anonymous Wed 20 May 2020 14:31:20 No.824949 Report

Quoted By: >>824959

>>824914
select tp.ID_PLATA, tp.ID_COMANDA, tc.ID_CLIENT, tc.DATA_COMANDA, tc.PRET
from TABELPLATI tp, TABELCOMANDA tc
where tp.ID_PLATA = tc.ID_PLATA and tp.ID_COMANDA = tc.ID_COMANDA

Two tables having a foreign key of each other isn't necessarily a mistake but can potentially lead to some awkward situations. My first guess is that an order doesn't absolutely need a reference key to payments; if you want to check if a payment has arrived, you can do so by matching the payment table containing the reference to the order table.
To update the orders with the payment information in your current example you could do something like this: https://stackoverflow.com/a/2334741

Anonymous

Anonymous Wed 20 May 2020 14:41:20 No.824952 Report

Quoted By: >>824959

>>824918
insert into TRANZACTION (ID_TRANZACTION, ID_CLIENT, ID_COMANDA)
select 770002, tc.ID_CLIENT, tc.ID_COMANDA
from TABELCLIENT tc
where tc.ID_COMANDA = 330001

I'm omitting a few of the columns here to keep it short but that's the basic idea.
https://stackoverflow.com/a/26080 for a more general explanation.

Anonymous

Anonymous Wed 20 May 2020 14:45:18 No.824954 Report

Quoted By:

>>824920
Not sure what exactly you're trying to group by but whatever. If the query returns an empty table that doesn't indicate an error, it only means your criteria were to strict or plain wrong.

Anonymous

View Same Google ImgOps iqdb SauceNAO SQLet.jpg, 14KiB, 235x369

Anonymous Wed 20 May 2020 15:03:49 No.824959 Report

Quoted By: >>824968 >>825001

>>824941
>>824949
>>824952
Thanks for the help but I put these in a query and none of them work. Was I supposed to generate my tables with SQL somehow and put your lines in their respective code windows?

Anonymous

Anonymous Wed 20 May 2020 15:11:59 No.824963 Report

Quoted By:

>>824920
As for the error I had I tried grouping the products tab with the suppliers and having a query show only products from a certain supplier.

SELECT producttable.ID_Product, producttable.Product_Name, suppliertable.ID_Supplier
FROM suppliertable INNER JOIN producttable ON producttable.ID_Supplier = producttable.ID_Supplier
ORDER BY ONLY suppliertable.Supplier_Name;

I realize my sql is ass.

Anonymous

Anonymous Wed 20 May 2020 15:28:40 No.824968 Report

Quoted By: >>824995

>>824959
My assumption was you having an dedicated SQL database for doing the cool stuff in a query window and only using Access as an interface for easy access to your tables and views. Judging from the code you posted it probably amounts to the same thing anyway but I don't really know much about queries done with Access because Access is shit.
Is there any specific error you're getting? Maybe I got some column or table names wrong.
This is a lot easier to do with a working environment and some trial and error, I'm typing these queries in the reply window.

Anonymous

Anonymous Wed 20 May 2020 16:28:17 No.824995 Report

Quoted By:

>>824968
Access is a SQL database, just not a very good one.

Anonymous

Anonymous Wed 20 May 2020 17:00:18 No.825001 Report

Quoted By:

>>824959
The code he posted looks alright for Access. Check the spelling of all names maybe. Or post more details about in what way does it not work.

Capcode	All Only User Posts Only Verified Posts Only Moderator Posts Only Manager Posts Only Admin Posts Only Developer Posts Only Founder Posts
Show Posts	All Only With Images Only Without Images Only Spoiler Images Only Non-Spoiler Images
Deleted Posts	All Only Deleted Posts Only Non-Deleted Posts
Ghost Posts	All Only Ghost Posts Only Non-Ghost Posts
Post Type	All Only Sticky Threads Only Opening Posts Only Reply Posts
Results	All Grouped By Threads
Order	Latest Posts First Oldest Posts First

On these archives

Your latest searches

Access SQL help